none
Generate Sheet Name using vba RRS feed

  • Question

  • Hi,

    I want to store the Sheet Names of a workbook from my Current Workbook. For example, I have a browse button which will help me to select an excel file. Once I select the excel file, I want the sheet names of that workbook(Which I have selected through Browse button) to be stored in Sheet2 of my current workbook without opening it.

    Is it possible to get the Sheet names without opening the file?

    Thank You.

    Friday, September 11, 2015 8:33 PM

Answers

  • Deb,

    Assuming your browse functionality is working fine. Add the following snippet after your BROWSE code.

    Sub Sht_name()
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Dim App As Application
    Dim Wb As Workbook
    Set App = CreateObject("Excel.Application")
    Set Wb = App.Workbooks.Open("C:\Users\UserName\Test.xlsx") 
    For i = 1 To Wb.Sheets.Count
    ThisWorkbook.Worksheets("Sheet2").Cells(i, 1).Value = Wb.Sheets(i).Name
    Next i
    Wb.Close False
    App.Quit
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    End Sub()

    @ J-Walk - Why so complex code for a simple solution buddy? 

    JPP

    • Marked as answer by Deb_chatt Saturday, September 12, 2015 8:44 AM
    Saturday, September 12, 2015 6:57 AM

All replies

  • No, you'd have to open the other workbook, loop through its sheets and store their names, then close that workbook without saving it.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by ryguy72 Saturday, September 12, 2015 12:16 AM
    • Unproposed as answer by Deb_chatt Saturday, September 12, 2015 8:46 AM
    Friday, September 11, 2015 8:36 PM
  • Hi Hans,

    Thanks for your reply. Could you please provide an example so that I can understand the procedure.

    Friday, September 11, 2015 9:15 PM

  • The code posted here works for me. You should be able to adapt it for your needs.

    • Proposed as answer by ryguy72 Saturday, September 12, 2015 12:17 AM
    • Unproposed as answer by Deb_chatt Saturday, September 12, 2015 8:46 AM
    Friday, September 11, 2015 11:04 PM
  • Deb,

    Assuming your browse functionality is working fine. Add the following snippet after your BROWSE code.

    Sub Sht_name()
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Dim App As Application
    Dim Wb As Workbook
    Set App = CreateObject("Excel.Application")
    Set Wb = App.Workbooks.Open("C:\Users\UserName\Test.xlsx") 
    For i = 1 To Wb.Sheets.Count
    ThisWorkbook.Worksheets("Sheet2").Cells(i, 1).Value = Wb.Sheets(i).Name
    Next i
    Wb.Close False
    App.Quit
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    End Sub()

    @ J-Walk - Why so complex code for a simple solution buddy? 

    JPP

    • Marked as answer by Deb_chatt Saturday, September 12, 2015 8:44 AM
    Saturday, September 12, 2015 6:57 AM
  • The code mentioned by J-Walk has the advantage that the workbook doesn't have to be opened and closed.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, September 12, 2015 8:36 AM
  • Thanks everyone for your help. The code provided by John is easier than the code provided by J. So, I'm marking John's reply as answer.
    Saturday, September 12, 2015 8:44 AM