VBS script to make a copy of spreadsheet to a new spreadsheet

I need a VBS script that will copy all worksheets in a spreadsheet to a new spreadsheet.  Save As does not work and neither does copy and paste.

I have the following VBS code taken from http://www.microsoft.com/technet/scriptcenter/resources/qanda/sept06/hey0918.mspx which does everything I want except it only copies one spreadsheet and you have to know the name of the spreadsheet.

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\OldFile.xls")
Set objWorksheet = objWorkbook.Worksheets("Sheet1")
objWorksheet.Activate

objWorksheet.Copy()

Set objNewWorkbook = objExcel.Workbooks(2)
objNewWorkbook.SaveAs("c:\Scripts\NewFile.xls")

I need code that will do all worksheets and without knowing the names of the spreadsheets.

Thanks in advance.
drunkennoodleAsked:
Who is Participating?
 
drunkennoodleConnect With a Mentor Author Commented:
I fixed the corrupted spreadsheet by going to help and typing corrupt and followed these steps

Click the Microsoft Office Button , and then click Open.
In the Open dialog box, select the corrupted workbook that you want to open.
Click the arrow next to the Open button, and then click Open and Repair.
Do one of the following:
To recover as much of the workbook data as possible, click Repair.

Thanks for your help.
0
 
Patrick MatthewsCommented:
drunkennoodle said:
>>Save As does not work

Why not?
0
 
drunkennoodleAuthor Commented:
To clarify I'm looking for VB script.  I know I can do a Save As or a copy and paste but that is not the resolution I'm looking for.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Patrick MatthewsCommented:
drunkennoodle said:
>> I know I can do a Save As or a copy and paste but that is not the resolution I'm looking for.

Why not?  What could be simpler than:

Dim xlApp, xlWb

Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open("C:\Scripts\OldFile.xls")
xlWb.SaveAs "c:\Scripts\NewFile.xls"

Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing
0
 
drunkennoodleAuthor Commented:
For a specific file the File->Save is not working for Excel 2007 in compatablity mode.  File-Save As .xlsx does work but that file is not able to Save As .xls.  When I do try to save Excel will crash.  This happens on multiple computers that are fully patched.  This is not an issue with other files.  

I was thinking that building a new spreadsheet and coping the worksheets over would fix the problem.

I ran the code and get Windows Script Host error The server threw an exception with code 80010105.
0
 
byundtCommented:
drunkennoodle,
Points (500) refunded because you solved the problem yourself and posted the method used.

byundt--Microsoft Excel Page Editor
0
All Courses

From novice to tech pro — start learning today.