SaveAs in Excel Macro not working from VBS script

[tlogan]

Hello All -

Has anyone ever seen anything like this before? I haven't seen anything like it in previous posts.

I have an Excel spreadsheet with a macro that runs automatically (and invisibly) that at the end saves itself and then saves itself under a new name.

Here is the problem....
If the macro is run by opening the spreadsheet by either double-clicking it or single-clicking and then clicking to run the marco, everything runs fine, and both files are saved.

BUT...if the spreadsheet is run from a vbs script file, everything APPEARS to run fine, but the new named file is NOT saved even though the originally named file is.

Any ides?

Here is the code I use to run it from the vbs script and the code in the macro to do the save. BTW - the spreadsheets are kept on a netowrk drive and the vbs script is run from the local hardrive.

Any help would be GREATLY appreciated. Ultimately, I have 60 spreadsheets to do this way, which is why I want to get the whole process to run from a vbs script.

Code to run from the vbs script once the network\directory path\spreadsheetname.xls has been determined and assigned to "Spreadsheet"....

xlApp.Application.Workbooks.Open(Spreadsheet)

Code from the excel macro to do the saves, discard changes to TXT files and exit the application....

    ActivWorkbook.Save
' add code here to automate
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs "XXXXX.xls"
    Close
' end add code
    Windows("YYYYYY.TXT").Activate
    ActiveWindow.Close
    Windows("ZZZZZZ.TXT").Activate
    ActiveWindow.Close
' add code here to automate
    Application.Quit
' end add code

One last thing....Using SaveCopyAs doesn't seem to make a difference.

Again, thanks for any help.
TLogan,
No Fancy Moniker

 

[PHV]

Have you tried this ?
With xlApp.Application
.Workbooks.Open(Spreadsheet)
.ActiveWorkbook.RunAutoMacros 1 'xlAutoOpen
End With

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tlogan]

PHV -

Sorry, it didn't seem to make a difference. What DOES seem to fix it, though, is that I specified the entire path in the "SaveAs". It just seems wierd that I would need to do that because the FIRST line of the macro is ChDir to the proper path and it works if you click on it.

Thanks for the suggestion, though.

TLogan,
No Fancy Moniker

"Sometimes it gives me a headache just trying to think down to your level." - Marvin the Robot

 

[xlbo]

chDir does very little in code - seems to get overridden by pretty much anything. I'm guessing thast if you "click on it", the directory is already chosen so the chDir would have no effect anyway....????

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question