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

You shouldn't depend on the name of the workbook. If you copy a sheet, it
is the activesheet.
Sub Save_Sheet(s01,s02)
set xlApp = Excel.Application
Set xlbook = xlApp.Workbooks.Open("E:\Temp\Year_2004.xls")
if s01 then
xlbook.Worksheets("01").copy
Activeworkbook.SaveAs "C:\Temp\January.xls"
Activeworkbook.Close SaveChanges:=False
End if
xlBook.Activate
if s02 then
xlbook.Worksheets("02").copy
Activeworkbook.SaveAs "C:\Temp\February.xls"
Activeworkbook.Close SaveChanges:=False
End if
End Sub


This could be made much simpler using the checkboxes and looping.
Sub SaveWorkbooks()
Dim sh as Worksheet, i as Long
for i = 1 to 12
set sh = xlBook.Worksheets(format(i,"00"))
if Thisworkbook.worksheets(1).OleObjects("CheckBox" & i).Object.Value then
sh.Copy
ActiveWorkbook.SaveAs "C:\Temp\" & Format(DateSerial(2004,i,1),"mmm") &
".xls"
ActiveWorkbook.Close SaveChanges:=False
End if
Next
End Sub

--
Regards,
Tom Ogilvy


"SHA" wrote in message
...
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