Saving MS-Excel sheets with VBScript

[shamigc]

Hi,
I have an MS-Excel file (Year_2004.xls) with 12 sheets, one for each month, with names "01", "02" and so on. I want to develop a subrutine, so the user selects with checkboxes which sheets to save, then those sheets should be saved with their month names, for example, if the user choosed January and February, sheet "01" should be saved as "January.xls", and sheet "02" should be saved as "February.xls". Until now I have the following code to save sheets for January and/or february (I have to replicate the code to address all months, s01 and s02 are boolean variables representing sheet "01" and sheet "02"):
-----------------------------------
Sub Save_Sheet(s01,s02)
If s01 then
Set xlApp = CreateObject("Excel.Application")
Set xlbook = xlApp.Workbooks.Open("E:\Temp\Year_2004.xls")
Set xlsheet = xlbook.Worksheets("01")
xlsheet.Select
xlsheet.Copy
Set NewBook = xlApp.Workbooks("Book1")
NewBook.Sheets("01").Select
NewBook.SaveAs "C:\Temp\January.xls"
xlApp.Quit
End If
If s02 then
Set xlApp = CreateObject("Excel.Application")
Set xlbook = xlApp.Workbooks.Open("E:\Temp\Year_2004.xls")
Set xlsheet = xlbook.Worksheets("02")
xlsheet.Select
xlsheet.Copy
Set NewBook = xlApp.Workbooks("Book1")
NewBook.Sheets("02").Select
NewBook.SaveAs "C:\Temp\February.xls"
xlApp.Quit
End If
End Sub
-----------------------------------
The problem I have with this subrutine is that it takes to much time to execute it when the user selects more than one month, because I have to open and close the MS-Excel application, because this is the only way I know to be sure the new book created is called "Book1" (the name excel uses as the default name when a new book is created), and to make reference to it.

Does anybody know how to improve my code? Does anybody has any suggestion?

Thanks,
Salvador Hernandez

[Lumumba]

Have a try at a VB-Script forum. I guess there it will need half a sec to get a response on your code snippet. (as this isn't a Macro Scheduler, but an Excel/VBS issue).

[Bob Hansen]

Within the VB you might be able to put the objects (xlApp, xlbook, xlsheet, etc.)in a single IF loop or a Select tree, and make the "opening a file, saving, etc.", a sub routine of its own. Might make the code smaller and neater, but don't think it will really run any faster. It might be faster if you can eliminate the Quit and Open of the spreadsheet. Perhaps find a way to keep it open until all of the selections have been processed?

You might also consider making a macro in your Spreadsheet. Have no idea if it will be any faster.

You could use Macro Scheduler to Execute *.xls and call the spreadsheet macro. That spreadsheet macro should have Close the spreadsheet as its final command to return to Macro Scheduler.

But Lumumba is correct re VB stuff. Some answers may be abailble here, but the VB experts are on other sites. You might want to check out http://p2p.wrox.com/forum.asp?FORUM_ID=73 for one. This is one that I use daily for Access and VBA issues.

And you can probably do all of this with MacroScheduler standard commands without using VB at all.