VBS: reading and writing excel sheet

I just found 2 little pieces of codes I made some time ago to check how easy it was to read and write excel sheet in Visual Basic.

Reading

Dim Cn
Dim File
Dim rst
 
' init ADODB object for reading excel file...
Set Cn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
 
' ...using  jet driver
Cn.open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\SheetFile.xls;Extended Properties=""Excel 8.0;HDR=NO""" 'strConnect
 
' we just get current time for benchmark purposes
startTime = Time() 
 
' reading some fields is just a matter of a simple SQL select
Set rst = Cn.Execute("Select * from [Sheet1$]") 
' here we loop over all non-empty lines and print column A, B, C and D
Do while(not rst.EOF)
    WScript.echo(rst.Fields(0) & " - " & rst.Fields(1) & " - " & rst.Fields(2) & " - " & rst.Fields(3))
    rst.moveNext
Loop
 
' some date comparison for benchmark purposes
endTime = DateDiff("s",startTime,Time())
WScript.echo("Read file in " & endTime & " sec")
 
' Close the connection
Cn.Close
Set Cn = Nothing

Writing

Dim Cn
Dim rst
 
' init ADODB object for writing to excel file...
Set Cn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
 
'...using adodb driver
With Cn
    .Provider = "MSDASQL"
    .ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _"DBQ=C:\SheetFile.xls"  & "; ReadOnly=False;"
    .Open
End With
 
' we just get current time for benchmark purposes
startTime = Time() ' gets the current time
'  fill 100 lines in the column A of sheet 1
For i = 1 to 100
	set rst = Cn.Execute("Insert into [Sheet1$A"& i &":B"& i &"] values("& i &","& i &")") 
Next
 
' some date comparison for benchmark purposes
endTime = DateDiff("s",startTime,Time())
WScript.echo("inserted " & i & " rows in " & endTime & " sec")
 
' Close the connection
Cn.Close
Set Cn = Nothing

Leave a Reply

Your email address will not be published. Required fields are marked *


*