How to modify a spreadsheet without Excel using VBScript

Question

I need to find a way to add a row to a spreadsheet using VBScript, on a pc that does not have Microsoft office installed.

I have tried using a line like this [Set objExcel = CreateObject("Excel.Application")], but since Excel does not exist on the pc I cannot create this object.

Is there a way to modify a spreadsheet without Excel?

Answer 1

Without Excel installed I cannot see how you will be able to change an Excel document.

However, If your are using Excel 2007 spreadsheets (xslx) then you should able to use the OpenXML functionality of the .NET Framework to update the contents without Excel physically being installed.

Take a look here for more information on Office OpenXML.

Answer 2

Not without extreme difficulty. Microsoft have released their file format specifications, Excel here, but these are not to be taken lightly, and I think you will have a difficult time using VBScript.

Answer 3

You can try to use the Microsoft Jet Driver:

See here for a vbscript sample. See here for more links and ways to insert rows.

Answer 4

You might want to see this question. It's C# based, but should give you an insight into the techniques for accessing spreatsheets.

Answer 5

To use the code below, create an Excel workbook named "Test.xls" in the same folder as the vbscript file.

In Test.xls, enter the following data in cells A1 thru B4:

First    Last
Joe       Smith
Mary      Jones
Sam    Nelson

Paste the vbscript code below into a .vbs file:

Const adOpenStatic = 3
Const adLockOptimistic = 3

filename = "Test.xls"
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filename & ";Extended Properties=Excel 8.0"

query = "Select * from [Sheet1$A1:B65535]"
Set rs = CreateObject("ADODB.Recordset")
rs.Open query, cn, adOpenStatic, adLockOptimistic

rs.AddNew
rs("First") = "George"
rs("Last") = "Washington"
rs.Update

rs.MoveFirst
Do Until rs.EOF
  WScript.Echo rs.Fields("First") & " " & rs.Fields("Last")
  rs.MoveNext
Loop

At a command prompt, type:

CSCRIPT Yourfile.vbs

It will add a name to the spreadsheet and then write out all the names.

Joe Smith
Mary Jones
Sam Nelson
George Washington

Answer 6

I believe the simple answer to your question is no because you need the Excel COM object which is only installed when Excel is installed. This used to be one of the real drawbacks of writing an Office app--the need for the entire application (Excel, Word or whatever) in order for an end-user to use it.

Answer 7

This is the final version of the script I used, thank you all for the help.

Dim arrValue
arrValue = Array("Test","20","","I","2.25","3.9761","20","60","12","1","","1","1","1")
AddXLSRow "C:\Test.xls", "A1:N109", arrValue

Sub AddXLSRow(strSource, strRange, arrValues)
'This routine uses the data from an array to fill fields in the specified spreadsheet.
'Input strSource (String) = The Full path and filename of the spreadsheet to be used.
'Input arrValues (Array) = An array of values to be added to the spreadsheet.
Dim strConnection, conn, rs, strSQL, index

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strSource & ";Extended Properties=""Excel 8.0;HDR=Yes;"";"

Set conn = CreateObject("ADODB.Connection")
conn.Open strConnection
Set rs = CreateObject("ADODB.recordset")
strSQL = "SELECT * FROM " & strRange
rs.open strSQL, conn, 3,3
rs.AddNew
index = 0
For Each field In rs.Fields
         If field.Type = 202 Then
                   field.value = arrValues(index)
         ElseIffield.Type = 5 And arrValues(index) <> "" Then
                   field.value = CDbl(arrValues(index))
         End If
         If NOT index >= UBound(arrValues) Then
                   index = index + 1
         End If
Next
rs.Update
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
End Sub

Answer 8

Sorry to be late to the party. The fact that no one's mentioned VSTO probably means that I'm misunderstanding the question. And at any rate I've heard mixed reviews from folks using it.