Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    copy tab names from passworded workbook

    Is there a macro that will copy the sheet tab names from a password protected workbook to another non-protected workbook? Or, must the "source" workbook be unprotected?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The source does not need to be protected. This code should do what you want:
    Code:
    Option Explicit
    Sub GetSheetNames()
      Dim wkb As Workbook
      Dim j As Integer
      Dim iNumSheets As Integer
     
      Set wkb = Workbooks("Book1.xls")
      iNumSheets = wkb.Sheets.Count
      For j = 1 To iNumSheets
        ActiveSheet.Cells(j, 1) = wkb.Sheets(j).Name
      Next j
    End Sub
    The activesheet should be the sheet where you want to list the sheetnames (it will start in A1 and move down the column). Change the workbook name to the protected workbook.

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Mar 2010
    Location
    Tampa, FL, USA
    Posts
    114
    Thanks
    11
    Thanked 10 Times in 9 Posts
    To eliminate counters, you might also use the following to put the names in rows starting at the current location...
    Code:
    Option Explicit
    Sub wsnames()
        Dim s As Worksheet
        For Each s In Workbooks("Book1.xls")
            ActiveCell.Value = s.Name
            ActiveCell.Offset(1, 0).Select
        Next
    End Sub
    PJ in FL

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Thank you. Perfect.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Your code will only print the worksheet names, it will NOT print the chart sheets or other sheets...

    Steve

  6. #6
    2 Star Lounger
    Join Date
    Mar 2010
    Location
    Tampa, FL, USA
    Posts
    114
    Thanks
    11
    Thanked 10 Times in 9 Posts
    Steve,

    Thanks for the heads-up! Wow, the details of the Excel Object model! Part of the reason I try to understand and answer where I can is for the education I receive.

    I simplified and specified a little, and this new routine picks up charts. What other sheet types are there? Do you think this would also pick them up?

    Code:
    Sub wsnames()
        For Each s In Workbooks("Book1.xls").Sheets
            ActiveCell.Value = s.Name
            ActiveCell.Offset(1, 0).Select
        Next
    End Sub
    PJ in FL

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    This will fail with the recommended "option explicit"

    I recommend including the line:
    Dim s
    or
    Dim s as variant

    I also do not recommend SELECTing objects as this slows down the code, (which is why I prefer the looping)

    Steve

  8. #8
    2 Star Lounger
    Join Date
    Mar 2010
    Location
    Tampa, FL, USA
    Posts
    114
    Thanks
    11
    Thanked 10 Times in 9 Posts
    Steve,

    Your comment about .Select slowing down the code got me curious so I did a little timing study of both methods. I hope the results will be as informative for others as it was for me.

    I built looping into both routines so I could get a more accurate time measurement, and also compared each routine run with variations in .Calculation and .ScreenUpdating settings.

    The two routines and the results are posted below:
    Code:
    Option Explicit
    
    
    Sub wsnames()
    ' TIMING STUDY
    '  TIME (SEC)   ScreenUpdating   Calculation 
    '  ----------   --------------   ----------
    '   0.48094     TRUE            xlCalculationAutomatic
    '   0.00610     TRUE            xlCalculationManual
    '   0.00045     FALSE           xlCalculationManual
    '
    Dim s As Variant
    Dim t As Single
    Dim i As Integer
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        
        i = 0
        t = Timer()
        Do
            For Each s In Workbooks("Database_2011-04-17A.xlsb").Sheets ' 1 CHART, 4 WORKSHEETS
                ActiveCell.Value = s.Name
                ActiveCell.Offset(1, 0).Select
            Next
            i = i + 1
        Loop Until i = 1000
        
        Cells(2, 2).Value = (Timer() - t) / 1000
        Cells(2, 2).Select
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True
    
    End Sub
    
    Sub GetSheetNames()
    ' TIMING STUDY
    '  TIME (SEC)   ScreenUpdating   Calculation 
    '  ----------   --------------   ----------
    '   0.47750     TRUE            xlCalculationAutomatic
    '   0.00279     TRUE            xlCalculationManual
    '   0.00022     FALSE           xlCalculationManual
    '
    Dim wkb As Workbook
    Dim j As Integer
    Dim iNumSheets As Integer
    Dim t As Single
    Dim i As Integer
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
        
        i = 0
        t = Timer()
        Do
          Set wkb = Workbooks("Database_2011-04-17A.xlsb") ' 1 CHART, 4 WORKSHEETS
          iNumSheets = wkb.Sheets.Count
          For j = 1 To iNumSheets
            ActiveSheet.Cells(j, 1) = wkb.Sheets(j).Name
          Next j
          i = i + 1
        Loop Until i = 50
        
        Cells(1, 2).Value = (Timer() - t) / 50
        
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True
    End Sub
    The part that was most surprising to me was the 80-180x time improvement with calculation set to manual! I expected to see better times with ScreenUpdating off, and there was around 10x improvement with that change.

    As you suggested, there was a significant change due to the use of .Select. There is only about 2% improvement with ScreenUpdating and Calculation at the default enabled setting, but the difference increased to 50% improvement when Calculation changed to the manual setting!

    Thanks for sharing that tidbit! I learned a good lesson in techniques to improve the speed of my code!

    The reason one routine shows 1000 loops and the other shows only 50 is the routines were so slow with ScreenUpdating and Calculation at the slowest settings, I limited the number of loops so it didn't take minutes to run each test. At the other settings, 1000 loops ran so fast it was worth it to improve the accuracy of the result.
    PJ in FL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •