How to modify an Excel spreadsheet without Excel using VBScript?

Question

I need to add a row to a spreadsheet using VBScript on a PC on which Microsoft Office is not 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 change a table without Excel?

Answer 1

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 into cells A1 through B4:

First   Last
Joe     Smith
Mary    Jones
Sam     Nelson

Paste the vbscript code below into the .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 the command prompt, type:

CSCRIPT Yourfile.vbs

He will add the name to the spreadsheet, and then write out all the names.

Joe Smith
Mary Jones
Sam Nelson
George Washington

Answer 2

Microsoft Jet:

. vbscript. . .

Answer 3

. Microsoft Excel , , , VBScript.

Answer 4

script, , .

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 5

... , , Excel vbScript Excel . , , . , , , , - , .

(/) Excel vbScript Windows 2008, Excel . ( PowerShell, VBS):

vbScript Excel Excel

vbScript Excel Excel

, -, .

l8r...

UCG

Answer 6

Excel , Excel.

, Excel 2007 (xslx), OpenXML- .NET Framework Excel.

Office OpenXML.

Answer 7

, . #, -.

Answer 8

, . , VSTO, , , . , , , .

Answer 9

, - , COM Excel, Excel. Office - (Excel, Word - ), .

Answer 10

EPPlus. epplus.codeplex.com

, VSTO, excel.