Reputation:
I need to add a row to a spreadsheet using VBScript on a PC that does not have Microsoft Office installed.
I tried [Set objExcel = CreateObject("Excel.Application")
]
Since Excel does not exist on the PC I cannot create this object.
Is there a way to modify a spreadsheet without Excel?
Upvotes: 4
Views: 25915
Reputation: 20209
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
Upvotes: 8
Reputation: 449
Use EPPlus. epplus.codeplex.com
You can do most things that you can do with VSTO, without excel installed.
Upvotes: -2
Reputation:
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
Upvotes: 1
Reputation: 11
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.
L8r...
UCG
Upvotes: 1
Reputation: 412
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.
Upvotes: 0
Reputation: 15343
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.
Upvotes: -1
Reputation: 51124
You might want to see this question. It's C# based, but should give you an insight into the techniques for accessing spreatsheets.
Upvotes: 0
Reputation: 19152
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.
Upvotes: 3
Reputation: 51124
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.
Upvotes: 1
Reputation: 3446
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.
Upvotes: 0