Question

Locked

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

By mail.aaron.kirk ·
Hi all,

I'm trying to write a script that will open an excel spreadsheet, update a cell, save the spreadsheet and then close it.

I get the following error when I run the script:
Wrong number of arguments or invalid property assignment: 'objExcel.ActiveWorkbook.Save'
Line 8, Code: 800A01C2

I have noticed that it has opened Excel but there are 2 spreadsheets running: test.xls and book1

Here is the code I have:

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

objExcel.Application.Visible = True
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
-------------------------------------

Any assistance would be greatly appreciated thank you.

Regards,
Aaron.

This conversation is currently closed to new comments.

11 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +
Collapse -

Nevermind,

by mail.aaron.kirk In reply to VBScript to open excel sp ...

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

See example 3.

Regards,

Aaron

Collapse -

just in case you havn't got this working

by greg_weinheimer In reply to VBScript to open excel sp ...

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....

Collapse -

Try this

by dstewart215 In reply to VBScript to open excel sp ...

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

Collapse -

Please clarify

by niamgamer In reply to VBScript to open excel sp ...

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

Collapse -

possible issue

by ben.reay In reply to VBScript to open excel sp ...

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

Collapse -

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

by tester11111 In reply to VBScript to open excel sp ...

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

Collapse -

Does not work for me

by sp4tech In reply to VBScript to open excel sp ...

No, it does not work for me. The file is getting saved but my changes in the saved file does not display.
Also while closing the file, it ask for save or not.

Please guide

Collapse -

change "save" to "saveas" and it will work {nt}

by Slayer_ In reply to VBScript to open excel sp ...
Collapse -

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

by kishore05 In reply to VBScript to open excel sp ...

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

Collapse -

* 100% Working * Tested *

by ChetanKaul In reply to VBScript to open excel sp ...

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.

Back to Software Forum
11 total posts (Page 1 of 2)   01 | 02   Next

Related Discussions

Related Forums