Avatar of cbones
cbonesFlag for United States of America

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
VB ScriptMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
cbones
Avatar of Tom Cieslik
Tom Cieslik
Flag of United States of America image

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

\\SERVER\SHARE\FOLDER\

Than try run macro again
Avatar of Norie
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

Avatar of cbones
cbones
Flag of United States of America image

ASKER

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

Try this.
objExcel.ActiveWorkbook.Close False

Open in new window

Avatar of Bill Prew
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
Avatar of cbones
cbones
Flag of United States of America image

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
Avatar of Norie
Norie

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of cbones
cbones
Flag of United States of America image

ASKER

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


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

Can you post the exact code you tried?
Avatar of cbones
cbones
Flag of United States of America image

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

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo