VBScript asks me to overwrite Excel file despite DisplayAlerts = False

1
votes

I want to run a macro from an Excel file via VBScript, so that I can run that script via Task Scheduler.

That part works, but when it tries to save the file it always asks "File already exists, want to overwrite? Yes, No, Cancel".

While researching, every thread says set DisplayAlerts to False, which I already did but that does not work. If I only use .Save then it saves it to my default Documents Directory - not the sharepoint.

strPath = "\\ecs.zspi.company.de@SSL\DavWWWRoot\Folder\Test.xlsm" 

strMacro = "VV.VVImport2" '  

Set objApp = CreateObject("Excel.Application") 
objApp.Visible = True   
objApp.DisplayAlerts = False
objApp.AskToUpdateLinks = False
objApp.AlertBeforeOverwriting = False
objApp.FeatureInstall = msoFeatureInstallNone

Set wbToRun = objApp.Workbooks.Open(strPath) 
objApp.Run strMacro 
wbToRun.SaveAs "\\ecs.zspi.company.de@SSL\DavWWWRoot\Folder\Test.xlsm" 
wbToRun.Close False
objApp.Quit

It should save the file to the original location without alerting. But I always get the Alert Box "File already exists, want to overwrite? Yes, No, Cancel".

1
excelvbscript
Why do you use SaveAs when saving to the same file? Can't you use simple Save? - Geert Bellekens
If I use .Save then it saves the file to my local directory and not the sharepoint. - Anasurimbor87
why would he be doing that? That makes no sense at all. - Geert Bellekens
Could be something my company configured. Our Documents directory is a network drive. It's integrated as T:\Documents. This way we can Login on any company pc and have our data. I guess some system path is configured that way. - Anasurimbor87
You could try wbToRun.Application.DisplayAlerts = False, based on a comment in another thread. - Ron Rosenfeld

1 Answers

0
votes

You need to set the .Saved property of the workbook to True

...
wbToRun.SaveAs "\\ecs.zspi.company.de@SSL\DavWWWRoot\Folder\Test.xlsm" 
wbToRun.Saved = True
wbToRun.Close False
objApp.Quit

It seems like the SaveAs method doesn't set the property to True, I've experienced this problem before and this fixed it for me.