CODEWITHSUNDEEP
excelvbscript
Tester101
Tester101

Reputation:

How to modify an Excel spreadsheet, without Excel, using VBScript?

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: 25916

Answers (10)

aphoria
aphoria

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

rgshenoy
rgshenoy

Reputation: 449

Use EPPlus. epplus.codeplex.com

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

Upvotes: -2

Tester101
Tester101

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

UnderCoverGuy
UnderCoverGuy

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

BobC
BobC

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

Onorio Catenacci
Onorio Catenacci

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

ProfK
ProfK

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

Panos
Panos

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

ProfK
ProfK

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

DilbertDave
DilbertDave

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

Related Questions