VBScript to open excel spreadsheet, update cell, save then close

I found a solution here: http://www.voiceguide.com/vghelp/source/html/modvbs.htm

See example 3.

Regards,

Aaron

The only problem I had when I tried to run this vbs is that it gave me a message that there were too many parms....

I noticed this was trying to save the active workbook.... so also passing it the workbook name seemed odd...

objExcel.ActiveWorkbook.Save "C:\test.xls"

I changed this line to

objExcel.ActiveWorkbook.Save

and it workde fine....

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\test.xls")
objExcel.Application.DisplayAlerts = False
objExcel.Application.Visible = True
'objExcel.Workbooks.Add
'objExcel.Cells(1, 1).Value = "Test value"
'objExcel.Cells(1, 2).Value = "Second value"
objExcel.Cells(1, 1).Value = TextBox1.Text
objExcel.Cells(1, 2).Value = TextBox2.Text

'objExcel.ActiveWorkbook.Save "C:\test.xls"
objExcel.ActiveWorkbook.SaveAs "C:\test.xls"
objExcel.ActiveWorkbook.Close
'objExcel.ActiveWorkbook.

'objExcel.Application.Quit
'WScript.Echo "Finished."
'WScript.Quit

Aaron,

It looks like what you have is almost there. However, can you please clarify if you are tryig to open an existing .xls file/spreadsheet or do you want to create a new workbook, spreadsheet and then update a certain cell?

First off, keep in mind that the application set is different from the workbook set.
Threfore, you set the variable "objWorkbook" but you don't use it. Do that first and it'll make it a bit easier to work with.

If you are trying to open an existing file and workbook, then the line:
objExcel.Workbooks.Add
...is not needed. (That will create the secont spreasheet you're seeing)
You can dictate which workbook (tab/spreadsheet) is active by using:
objWorkbook.WorkSheets(1).Activate

Also, when hard coding the file anem you can either use:
objExcel.ActiveWorkbook.SaveAs "C:\test.xls"
Or
objExcel.ActiveWorkbook.Save

The code that sould work for you would look like this:
-------------------------------------
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\test.xls")

objExcel.Application.Visible = True
objWorkbook.WorkSheets(1).Activate
objWorkbook.WorkSheets(1).Cells(1, 1).Value = "Test value"

objExcel.ActiveWorkbook.Save "C:\test.xls"
objExcel.ActiveWorkbook.Close

objExcel.Application.Quit
WScript.Echo "Finished."
WScript.Quit
-------------------------------------

I found that this worked if I used

objExcel.ActiveWorkbook.Save

or I could use

objExcel.ActiveWorkbook.Saveas

With a file path at the end

I dont think that save supports a file location

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\test.xls")

objExcel.Application.Visible = True
' - Remove this line - objExcel.Workbooks.Add
objExcel.Cells(1, 1).Value = "Test value"

objExcel.ActiveWorkbook.Save "C:\test.xls"
objExcel.ActiveWorkbook.Close

objExcel.Application.Quit
WScript.Echo "Finished."
WScript.Quit

Please change objExcel.ActiveWorkbook.Save "C:\test.xls"
to objWorkbook.Save "C:\test.xls" will work..

Change objExcel.ActiveWorkbook.Save "C:\test.xls" To objExcel.ActiveWorkbook.Save
And Delete objExcel.Workbooks.Add

It's working 100% , I am using it right now.