Working With Excel From VBScript
Another question that comes up a lot is how to connect to Excel from VB Script and then manipulate the data in the cells of a spreadsheet. Well, here is a quick example VBScript for doing just that:
' Open up Excel
dim ex
set ex=WScript.CreateObject("Excel.Application")
ex.visible=true
' Get the first sheet by name
dim wb
dim ws
' Normally you could open a workbook here thus:
' ex.Workbooks.Open(Filename As String, [UpdateLinks], [ReadOnly], [Format], [Password], [WriteResPassword], [IgnoreReadOnlyRecommended], [Origin], [Delimiter], [Editable], [Notify], [Converter], [AddToMru], [Local], [CorruptLoad]) As Workbook
' But I will just make one
set wb=ex.Workbooks.add()
set ws=wb.Sheets("Sheet1")
dim row
dim column
for row=1 to 100
for column=1 to 10
ws.cells(row,column).value="Cell(" & row & "," & column & ")"
next
next
dim val
for row=1 to 100
for column=1 to 10
with ws.cells(row,column)
.value="updated_" & .value
end with
next
next
for column=1 to 10
ws.columns(column).AutoFit()
next
[Via]