Help with VBA code

[Bara2]

Hello

I need to open an workbook (x)then get all sheets name in another workbook (Y) horizontally :. Sheet1 column A sheet 2 column B ..... Sheet n
And if I open another workbook (Z) I want to do the same but after the sheet n in column n in workbook (Y)


Thank you in advance

 

[johnnyL]

Your post is a bit confusing.

How many workbooks are you referring to? Do you want the controlling workbook to ask the user for each workbook to get sheet names from?

Is workbook(X) the original workbook that controls what is happening or is Workbook(Y) the original workbook that controls what is happening?

It sounds like you want to list the sheet names from each workbook horizontally across one row. You didn't say if there should be some type of indication that sheet names are being displayed from a different workbook after each workbook change.

etc...

So basically we need to know:
1) which workbook will contain the code to grab all of the sheet names from various workbooks.
2) Which workbook will contain the results and what row to store the results in.
3) Will you be asking the user to select a workbook to grab the sheet names from, or is there a list somewhere of workbook paths to load one at a time?

Post comments of what you want to do and the result that you want to see as a result of your actions.

 

[johnnyL]

No response thus far.

This is what I envision thus far until a response is received from the OP @Bara2:

Option Explicit
'

Sub ScrapeAllSheetNamesFromWorkbook()
'                                                                                   ' Works with .xlsx & .xlsm files
    Dim tbl                         As Object
    Dim SheetNamesList              As Object
    Dim conexion                    As Object, objCat   As Object
    Dim LastUsedColumnInRowA        As String
    Dim LastUsedColumnInRowAPlus1   As Long
    Dim SourceSheetName             As String
    Dim UserSelectedFile            As String
'
    Do While UserSelectedFile <> "False"
        UserSelectedFile = Application.GetOpenFilename("Excel workbooks (*.xls*), *.xls*")  ' ask user for file to get sheet names from
'
        If UserSelectedFile = "False" Then Exit Sub                                         ' If User exited then exit this sub
'
        Set SheetNamesList = CreateObject("System.Collections.ArrayList")                   ' Create SheetNamesList
'
        Set conexion = CreateObject("ADODB.CONNECTION")
        Set objCat = CreateObject("ADOX.Catalog")
        Set tbl = CreateObject("ADOX.Table")
'
        conexion.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & UserSelectedFile & "; Extended Properties=""Excel 12.0; HDR=YES"";"
'
        Set objCat.ActiveConnection = conexion
'
        For Each tbl In objCat.Tables                                                       ' Establish tbl loop to get sheet names
            SourceSheetName = tbl.Name                                                      '   Save SourceSheetName
            SourceSheetName = Replace(SourceSheetName, "'", "")                             '   Remove 's from SourceSheetName
            SourceSheetName = Replace(SourceSheetName, "$", "")                             '   Remove $ from end of SourceSheetName
'
            SheetNamesList.Add SourceSheetName                                              '   Append SourceSheetName to SheetNamesList
        Next                                                                                ' Loop back for next sheet name
'
        LastUsedColumnInRowA = Split(Cells(1, Cells(1, Columns.Count).End(xlToLeft).Column).Address, "$")(1)    ' Returns column letter
'
        Range(LastUsedColumnInRowA & "1") = UserSelectedFile                                ' Display workbook name to sheet
'
        LastUsedColumnInRowAPlus1 = Cells(1, Columns.Count).End(xlToLeft).Column + 1        ' Returns a column #
'
        Cells(1, LastUsedColumnInRowAPlus1).Resize(1, SheetNamesList.Count).Value = SheetNamesList.ToArray  ' Display SheetNamesList to sheet horizontally
    Loop
'
'   Code CleanUp
    conexion.Close
    Set objCat = Nothing
    Set conexion = Nothing
End Sub

That will ask user for workbooks until they cancel and post workbook paths and the sheets contained within them along the 1st row of the sheet that is running the code.

As a bonus, it does that without opening the workbooks that the user selects. That will make it faster.

 

[Bara2]

Hello
Thank u for your support .
Yes the workbook (X) the original workbook who contrôle what happening
Y,z .... Workbooks which I want to open from a path then copy the sheets name horisontaly .
Workbook (X) will contain all the results
Row 4
Yes I need to ask the user to chose the workbooks to grab sheets name

Thank u for your patience

 

[johnnyL]

You marked my post as a solution but it sounds like you need the row that the results are displayed into adjusted to row 4.

I made the edit to have row 4 receive the data. I also corrected an error in the code that would overwrite the last sheet name when adding additional sheets from additional files.

Option Explicit
'

Sub ScrapeAllSheetNamesFromWorkbookV2()
'                                                                                   ' Works with .xlsx & .xlsm files
    Dim AdditionalPasteCounter          As Long
    Dim DestinationRow                  As Long
    Dim LastUsedColumnInDestinationRow  As Long
    Dim tbl                             As Object
    Dim SheetNamesList                  As Object
    Dim conexion                        As Object, objCat   As Object
    Dim SourceSheetName                 As String
    Dim UserSelectedFile                As String
'
    DestinationRow = 4                                                                      ' <--- Set this to the row that you want results displayed to
'
    AdditionalPasteCounter = 0
'
    Do While UserSelectedFile <> "False"
        UserSelectedFile = Application.GetOpenFilename("Excel workbooks (*.xls*), *.xls*")  ' ask user for file to get sheet names from
'
        If UserSelectedFile = "False" Then Exit Sub                                         ' If User exited then exit this sub
'
        Set SheetNamesList = CreateObject("System.Collections.ArrayList")                   ' Create SheetNamesList
'
        Set conexion = CreateObject("ADODB.CONNECTION")
        Set objCat = CreateObject("ADOX.Catalog")
        Set tbl = CreateObject("ADOX.Table")
'
        conexion.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & UserSelectedFile & "; Extended Properties=""Excel 12.0; HDR=YES"";"
'
        Set objCat.ActiveConnection = conexion
'
        For Each tbl In objCat.Tables                                                       ' Establish tbl loop to get sheet names
            SourceSheetName = tbl.Name                                                      '   Save SourceSheetName
            SourceSheetName = Replace(SourceSheetName, "'", "")                             '   Remove 's from SourceSheetName
            SourceSheetName = Replace(SourceSheetName, "$", "")                             '   Remove $ from end of SourceSheetName
'
            SheetNamesList.Add SourceSheetName                                              '   Append SourceSheetName to SheetNamesList
        Next                                                                                ' Loop back for next sheet name
'
        LastUsedColumnInDestinationRow = Cells(DestinationRow, Columns.Count).End(xlToLeft).Column          ' Returns a column #
'
        Cells(DestinationRow, LastUsedColumnInDestinationRow + AdditionalPasteCounter) = UserSelectedFile   ' Display workbook name to sheet
'
        AdditionalPasteCounter = 1
'
        LastUsedColumnInDestinationRow = Cells(DestinationRow, Columns.Count).End(xlToLeft).Column          ' Recalculate last column # used
'
'       Display SheetNamesList to sheet horizontally
        Cells(DestinationRow, LastUsedColumnInDestinationRow + AdditionalPasteCounter).Resize(1, SheetNamesList.Count).Value = SheetNamesList.ToArray
    Loop
'
'   Code CleanUp
    conexion.Close
    Set objCat = Nothing
    Set conexion = Nothing
End Sub

Anything else that needs to be changed?

 

[Bara2]

I'll test and come with a feedback
Thank u so much

 

[Sbtmed]

hello i have been tested the code all its ok but why the path of the opned workbook appears in the first cell . also if its possible i need to make a vlookup from the opend workbook which searche the name of sheet then if it found search the line as an intersection between lines and name of sheets .

thank u in advance @jhonnyL