Take the 2-minute tour ×

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?

share|improve this question

10 Answers 10

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("First") = "George"
rs("Last") = "Washington"

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

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
share|improve this answer
I thought he said he doesn't have Excel? –  Onorio Catenacci Oct 31 '08 at 17:00
This doesn't depend on Excel. It uses ODBC. –  aphoria Oct 31 '08 at 18:16

Use EPPlus. epplus.codeplex.com

You can do most things that you can do with VSTO, without excel installed.

share|improve this answer

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
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
Set rs = Nothing
Set conn = Nothing
End Sub
share|improve this answer

I know...years later but today I needed to figure out how to access an Excel spreadsheet using vbScript without loading Excel on my server. I searched around the net and found your information helpful, but I still needed more so I kept searching. I finally found the solution that I needed and wanted to share it here just in case anyone else has the same issues that as I did.

I was trying to access (read/write) an Excel spreadsheet using vbScript on a Windows 2008 server and I didn't want to install Excel on my server. My solution was here (it uses PowerShell but it is easy to decypher to VBS):

Using vbScript to read from an Excel spreadsheet without Excel installed

Using vbScript to write to an Excel spreadsheet without Excel installed

I hope that this helps someone that needs the same solution in the future.



share|improve this answer

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.

share|improve this answer
VSTO still requires Office to be installed, AFAIK. –  ProfK Nov 3 '08 at 15:53

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.

share|improve this answer

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

share|improve this answer

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.

share|improve this answer
Of course. Duh! –  ProfK Oct 31 '08 at 13:30

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.

share|improve this answer

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.

share|improve this answer

Your Answer

