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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
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 |