asked on

Save an excel file from within a vbscript

Thanks in advance for any help or guidance. I have a vbscript, pasted below, that opens excel, runs a macro but fails to save and close.

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(“M:\Test\Test.csv”)

objExcel.Application.Visible = True

objExcel.Application.Run "PERSONAL.XLSB!Macro1"
objExcel.ActiveWorkbook.Close

'objWorkbook.SaveAs(“M:\Test\Test.csv”)
objExcel.Application.Quit
WScript.Quit

I've commented out the objWorkbookSaveAs because it pops up an invalid charater in line 10 - char 20. Is it because it is located on a network drive? I can manually save the file.

Thank you

Tom Cieslik

If you do have access to macro and you can modify, just change path to

\\SERVER\SHARE\FOLDER\

Than try run macro again

Norie

The quotes in the commented out line don't look right, they should be " not “.

Try this.

objWorkbook.SaveAs "M:\Test\Test.csv"

Open in new window

By the way, you might want to specify the file format when saving.

For a CSV the file format constant would be xlCSV which has the value 6 so to save with that format use this.

objWorkbook.SaveAs "M:\Test\Test.csv", 6

Open in new window

cbones

ASKER

Norie, thank you. That works. Any way to not have it prompt to ask me to save the changes?

Norie

Try this.

objExcel.ActiveWorkbook.Close False

Open in new window

Bill Prew

If you want to save any changes made but not be prompted you can use the following approach, setting the DisplayAlerts property to prevent the popup question(s).

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("M:\Test\Test.csv")

objExcel.Application.Visible = True

objExcel.Application.Run "PERSONAL.XLSB!Macro1"

objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.Close True
objExcel.Application.Quit

WScript.Quit

Open in new window

~bp

cbones

ASKER

Thank you. That does work to save the file. Ideally I'd like to save the file to a different folder but using objWorkbook.SaveAs isn't working for me.

ASKER CERTIFIED SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cbones

ASKER

Thank you Norie. I tried both but keep receiving an error.

Line: 9
Char: 1
SaveAs Method of Workbook class failed
Code: 800A03EC

Norie

Can you post the exact code you tried?

cbones

ASKER

Norie, I had below in the code:

objExcel.ActiveWorkbook.Close True - I somehow left "True" in. Once removing that everything is working properly.

Thank you