VBS: reading and writing excel sheet

Posted on November 14, 2008

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

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

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 &")")