Saving MS-Excel sheets with VBScript

shamigc · July 9th, 2004, 07:23 AM

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 subroutine, 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 choused 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 subroutine 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. How can I create a new book and know which name has it? Knowing this I can improve the code, making reference to it. Also, how can I use variables with 'xlApp.Workbooks("Book1")', so instead of "Book1" I could use something like %BookName% variable?

Thanks,
Salvador HernÃ¡ndez

gcianfanelli · July 9th, 2004, 08:11 AM

Try this

Code:

Public Sub saveSheets()
  Dim xlApp As Object
  Dim xlBook As Object
  Dim xlSheet As Object
  Dim strOutputFileName

  Set xlApp = CreateObject("Excel.Application")
  Set xlBook = xlApp.Workbooks.Open("E:\Temp\Year_2004.xls")
  For Each xlSheet In xlBook.Worksheets
    strOutputFileName = "C:\TEMP\" & MonthName(CLng(xlSheet.Name)) & ".xls"
    xlSheet.SaveAs strOutputFileName
  Next
  xlApp.Quit
End Sub

Of course the only risk here is if your worksheet name isn't successfully cast to a Long type integer