| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
List all worksheets (and Document Names)
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
List all worksheets (and Document Names)
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 -- Gary "Barb Reinhardt" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
List all worksheets (and Document Names)
I'll try it. Thanks!
Barb Reinhardt "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... 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 -- Gary "Barb Reinhardt" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
List all worksheets (and Document Names)
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" wrote: 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 -- Gary "Barb Reinhardt" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
List all worksheets (and Document Names)
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 -- Gary "Barb Reinhardt" wrote in message ... 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" wrote: 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 -- Gary "Barb Reinhardt" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| return a list of names, from a large list of repeated names. | Excel Worksheet Functions | |||
| how do I make a list of the names of worksheets in an excel workb | Excel Discussion (Misc queries) | |||
| how do i list the names of all my worksheets | Excel Discussion (Misc queries) | |||
| how do i transfer a spreadsheet list of names onto an document | Excel Worksheet Functions | |||
| Create a list in one worksheet of the other worksheets' names | Excel Worksheet Functions | |||
Linear Mode
