Populate list of sheets in column across files in the same folder

[Ali M]

Hello
I want populating all of sheets name in open file"REPORT" in column A across files are in the same folder . the directory is "D:\Users\ALIM\Desktop\REPORT\"and hyperlink sheets when I click the sheet then should open file and activate the sheet name is click for the open file .
thanks

 

[DanteAmor]

Do the following:

1. Put all the code in one module.

Sub PopulateListOfSheets()
  Dim sFile As String, sPath As String, SheetNames As String
  Dim sh1 As Worksheet
  Dim i As Long
  Dim nm As Variant
   
  Set sh1 = Sheets("Report")
 
  sPath = "D:\Users\ALIM\Desktop\REPORT\"
  'sPath = "c:\trabajo\examples\"
  sFile = Dir(sPath & "*.xls*")
 
  sh1.Range("A2:A" & Rows.Count).ClearContents
  i = 1
  Do While sFile <> ""
    SheetNames = GetSheetNames(sPath & sFile)
    For Each nm In Split(SheetNames, "|")
      i = i + 1
      sh1.Hyperlinks.Add Anchor:=sh1.Range("A" & i), _
        Address:=sPath & Replace(sFile, " ", "%20"), _
        TextToDisplay:=nm
    Next
    sFile = Dir()
  Loop
End Sub

Function GetSheetNames(fn As String) As String
  Dim sh As Variant, sn As String
  With CreateObject("DAO.DBEngine.120").OpenDatabase(fn, False, False, "excel 5.0;hdr=no;")
    For Each sh In .tabledefs
      If InStr(1, sh.Name, "_xl", vbTextCompare) = 0 Then
        sn = sn & Replace(sh.Name, "$", "") & "|"
      End If
    Next
  End With
  GetSheetNames = Left(sn, Len(sn) - 1)
End Function

2. Run the PopulateListOfSheets macro to fill column "A" with the sheet names.

3. Put the following code in the events of the "REPORT" sheet

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
  ActiveWorkbook.Sheets(Target.Range.Value).Select
End Sub

Note Sheet Event:
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.


4. Press a hyperlink to open the book.

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[Tom.Jones]

Hi,

It's possible to have name of the file in column B (without hyperlinks).
Like this:
Sheet1....|.....Data.xlsx (say Data is the name of file where sheet1 is
Sheet1....|.....List.xlsx (say List is the name of file where sheet1 is
Sheet2....|.....List.xlsx (say List is the name of file where sheet2 is
...... and so on.

 

[Ali M]

Hi Dante !
that's awesome !
can you change show sheets names in column B and auto numbering in column A like 1,2,3....
thanks

 

[DanteAmor]

can you change show sheets names in column B and auto numbering in column A like 1,2,3....

It's possible to have name of the file in column B (without hyperlinks).

I added the columns as follows:

Column A numbering
Column B sheet name
Column C file name

Try:

Sub PopulateListOfSheets()
  Dim sFile As String, sPath As String, SheetNames As String
  Dim sh1 As Worksheet
  Dim i As Long
  Dim nm As Variant
    
  Set sh1 = Sheets("Report")
  
  sPath = "D:\Users\ALIM\Desktop\REPORT\"
  'sPath = "c:\trabajo\examples\"
  sFile = Dir(sPath & "*.xls*")
  
  sh1.Range("A2:C" & Rows.Count).ClearContents
  i = 1
  Do While sFile <> ""
    SheetNames = GetSheetNames(sPath & sFile)
    For Each nm In Split(SheetNames, "|")
      i = i + 1
      sh1.Range("A" & i).Value = i - 1
      sh1.Hyperlinks.Add Anchor:=sh1.Range("B" & i), _
        Address:=sPath & Replace(sFile, " ", "%20"), _
        TextToDisplay:=nm
      sh1.Range("C" & i).Value = sFile
    Next
    sFile = Dir()
  Loop
End Sub

If you don't require the name of the book, you can remove the line: sh1.Range("C" & i).Value = sFile

 

[Ali M]

Perfect !
just I would understand message shows when click the sheet name it won't open file directly . I should click enable macro button for message .
the message will tell me about security .
so the question, do you have any idea to avoid message and open file directly ?

 

[Ali M]

I've found why because the files is xlsm ,so will show message to enable macro .
everything is great .
much appreciated for your assistance.

 

[Tom.Jones]

Than you.