List all worksheets (and Document Names)

[Guest]

I have about 80 workbooks that are supposed to have the same worksheet names
in them, but I know that they don't. I want to gather a list of all of the
worksheets in each workbook in one document. The workbooks are not all
stored in the same folder. I do have the URL for each workbook easily
available to me in an excel worksheet.

Any suggestions?

Thanks in advance,
Barb Reinhardt

 

[Gary Keramidas]

here's something you can start with. i have hard coded a path variable and
have the filenames listed starting in b1 on sheet2.
this opened all the files list on sheet2 and put the filename in a2 under
book and the sheets starting in b2 under sheet heading

see if you can adapt it


Option Explicit
Dim fPath As String
Dim fname As String
Dim SH As Worksheet
Dim rng As Range
Dim i As Long
Dim WB1 As Workbook
Dim lastrow As Long
Dim CurBook As String
Dim e As String
Dim lFname As Long
Sub ListAll()
CurBook = Application.ThisWorkbook.Name
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "a").End(xlUp).Row
i = 1
fPath = "N:\My Documents\Excel\RECCU\FSA\"

lFname = Worksheets("Sheet2").Cells(Rows.Count, "b").End(xlUp).Row

For i = 1 To lFname
fname = ThisWorkbook.Worksheets("Sheet2").Cells(i, "b").Value
Workbooks.Open Filename:=fPath & fname, ReadOnly:=True, UpdateLinks:=3

For Each SH In Worksheets
Workbooks(CurBook).Worksheets("sheet1").Range("a" & lastrow + 1) = _
ActiveWorkbook.Name
Workbooks(CurBook).Worksheets("sheet1").Range("B" & lastrow + 1).Value = _
UCase(SH.Name)

lastrow = lastrow + 1
Debug.Print SH.Name
Next SH
i = i + 1
Workbooks(fname).Close SaveChanges:=False
Next

End Sub

 

[Barb Reinhardt]

I'll try it. Thanks!
Barb Reinhardt

here's something you can start with. i have hard coded a path variable and
have the filenames listed starting in b1 on sheet2.
this opened all the files list on sheet2 and put the filename in a2 under
book and the sheets starting in b2 under sheet heading

see if you can adapt it


Option Explicit
Dim fPath As String
Dim fname As String
Dim SH As Worksheet
Dim rng As Range
Dim i As Long
Dim WB1 As Workbook
Dim lastrow As Long
Dim CurBook As String
Dim e As String
Dim lFname As Long
Sub ListAll()
CurBook = Application.ThisWorkbook.Name
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "a").End(xlUp).Row
i = 1
fPath = "N:\My Documents\Excel\RECCU\FSA\"

lFname = Worksheets("Sheet2").Cells(Rows.Count, "b").End(xlUp).Row

For i = 1 To lFname
fname = ThisWorkbook.Worksheets("Sheet2").Cells(i, "b").Value
Workbooks.Open Filename:=fPath & fname, ReadOnly:=True, UpdateLinks:=3

For Each SH In Worksheets
Workbooks(CurBook).Worksheets("sheet1").Range("a" & lastrow + 1) = _
ActiveWorkbook.Name
Workbooks(CurBook).Worksheets("sheet1").Range("B" & lastrow + 1).Value = _
UCase(SH.Name)

lastrow = lastrow + 1
Debug.Print SH.Name
Next SH
i = i + 1
Workbooks(fname).Close SaveChanges:=False
Next

End Sub

 

[Guest]

I'd like to have the file path listed in one column and the docs to use in
another column. How would I do that?

Also, I'm finding that I get an update links message when the documents are
opened. What needs to be done so that they aren't displayed?

This is definitely getting the information I need.

Thanks so much!

 

[Gary Keramidas]

barb:

don't know if you changed anything, but this will list the workbookname in
A, the path in B and the sheet names in C


Option Explicit
Dim fPath As String
Dim fname As String
Dim SH As Worksheet
Dim rng As Range
Dim i As Long
Dim WB1 As Workbook
Dim lastrow As Long
Dim CurBook As String
Dim e As String
Dim lFname As Long
Sub ListAll()
CurBook = Application.ThisWorkbook.Name
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "a").End(xlUp).Row
i = 1
fPath = "N:\My Documents\Excel\RECCU\FSA\"

lFname = Worksheets("Sheet2").Cells(Rows.Count, "b").End(xlUp).Row

For i = 1 To lFname
fname = ThisWorkbook.Worksheets("Sheet2").Cells(i, "b").Value
Workbooks.Open Filename:=fPath & fname, ReadOnly:=True, UpdateLinks:=3

For Each SH In Worksheets
Workbooks(CurBook).Worksheets("sheet1").Range("a" & lastrow + 1) = _
ActiveWorkbook.Name
Workbooks(CurBook).Worksheets("sheet1").Range("b" & lastrow + 1).Value =
fPath
Workbooks(CurBook).Worksheets("sheet1").Range("c" & lastrow + 1).Value = _
UCase(SH.Name)

lastrow = lastrow + 1
Debug.Print SH.Name
Next SH
i = i + 1
Workbooks(fname).Close SaveChanges:=False
Next
Columns("A:C").AutoFit
End Sub