VBScript to open and save an Excel file

I am trying to open an excel file(xls) and trying to save it. the file already exists so the script doesnt work as it gets stuck at the prompt "File already exists. Overwrite Yes,No,Cancel". The script I have developed is attached.
Any and all help/pointers are appreciated.

Thanks

dim ObjExcel


Set objExcel = CreateObject("Excel.Application")


Set objWorkbook = objExcel.Workbooks.Open( "C:\test.xlsx")
objexcel.Visible=TRUE

WScript.Sleep 3000

objExcel.Save ("C:\test.xlsx")
objExcel.WorkBooks.Close( "C:\test.xlsx")

objExcel.Application.Quit
WScript.Echo "Finished."
WScript.Quit

Open in new window

bornsavageAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
Try this:
dim ObjExcel


Set objExcel = CreateObject("Excel.Application")
objExcel.DisplayAlerts = False

Set objWorkbook = objExcel.Workbooks.Open( "C:\test.xlsx")
objexcel.Visible=TRUE

WScript.Sleep 3000

objExcel.Save ("C:\test.xlsx")
objExcel.WorkBooks.Close( "C:\test.xlsx")
objExcel.DisplayAlerts = True
objExcel.Application.Quit
WScript.Echo "Finished."
WScript.Quit

Open in new window

bornsavageAuthor Commented:
Get an error -
cannot access c:\test.xlsx

Line 12
Patrick MatthewsCommented:
Change:objExcel.WorkBooks.Close( "C:\test.xlsx")to:objExcel.WorkBooks("C:\test.xlsx").Close
bornsavageAuthor Commented:
Same Error-

Windows Script Host

Error: Cannot access 'test.xlsx'
Line : 12
Char: 1
MisterTwelveCommented:
objExcel.DisplayAlerts = False
ON Scripting guys said:

Why did we do this? Well, first we’re creating a brand-new worksheet with this script, and then we’re going to save it as C:\Scripts\Test.xls (and then as C:\Scripts\Test.htm). That’s fine, unless a file named Test.xls or Test.htm already exists. In that case Excel won’t automatically overwrite the existing file; instead, it will pop up a dialog box asking if you’re sure that you want to overwrite that file. The dialog box will sit there on screen until you click Yes, No, or Cancel; needless to say, that means your script will also sit there - doing nothing - until you click Yes, No, or Cancel.

Unless, that is, you set DisplayAlerts to False. When DisplayAlerts is set to False Excel will not display any message boxes; instead, it automatically uses the default option of any message boxes it would otherwise show. For example, the default option for overwriting an existing file is Yes; therefore, instead of displaying a message box and waiting for you to answer, the script simple goes with the default value (yes) and overwrites the file. Make sense?
 
http://blogs.technet.com/b/heyscriptingguy/archive/2006/03/07/how-can-i-save-an-excel-spreadsheet-and-then-save-a-copy-as-html.aspx
 

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.