Hi,

for example assume , I have 30 workbooks and 30 worksheets in each
workbook.totally 900 worksheets. I do not want to open each workbook to
browse, there must be a way to see all worksheets in a way ? in a list ?
--
SAHRAYICEDIT-ISTANBUL

Couldn't find the original link to the code courtesy of Ozgrid so had to
paste it in here. Copy this code to a new module and run it. Your workbook
must have a Sheet1 in it or alter the code to where you want the output to be

Sub GetAllWorksheetNames()
Dim i As Integer
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim wSheet As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next

Set wbCodeBook = ThisWorkbook

With Application.FileSearch
.NewSearch
.LookIn = "C:\my documents" 'amend to suit
.FileType = msoFileTypeExcelWorkbooks
If .Execute 0 Then
For i = 1 To .FoundFiles.Count
Set wbResults = Workbooks.Open(.FoundFiles(i))

wbCodeBook.Sheets(1).Range _
("A65536").End(xlUp)(2, 1) = UCase(wbResults.Name)
For Each wSheet In wbResults.Worksheets
wbCodeBook.Sheets(1).Range _
("A65536").End(xlUp)(2, 1) = wSheet.Name
Next wSheet
wbResults.Close SaveChanges:=False
Next i
End If
End With

On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

Will that do?

Mike

"excel-tr" wrote:

Hi,

for example assume , I have 30 workbooks and 30 worksheets in each
workbook.totally 900 worksheets. I do not want to open each workbook to
browse, there must be a way to see all worksheets in a way ? in a list ?
--
SAHRAYICEDIT-ISTANBUL

dear mike,

super code :) thanks a lot. is it possible to put each sheet name in to a
listbox automatically then I will click which I want to see .

regards.
--
SAHRAYICEDIT-ISTANBUL


"Mike":

Couldn't find the original link to the code courtesy of Ozgrid so had to
paste it in here. Copy this code to a new module and run it. Your workbook
must have a Sheet1 in it or alter the code to where you want the output to be

Sub GetAllWorksheetNames()
Dim i As Integer
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim wSheet As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next

Set wbCodeBook = ThisWorkbook

With Application.FileSearch
.NewSearch
.LookIn = "C:\my documents" 'amend to suit
.FileType = msoFileTypeExcelWorkbooks
If .Execute 0 Then
For i = 1 To .FoundFiles.Count
Set wbResults = Workbooks.Open(.FoundFiles(i))

wbCodeBook.Sheets(1).Range _
("A65536").End(xlUp)(2, 1) = UCase(wbResults.Name)
For Each wSheet In wbResults.Worksheets
wbCodeBook.Sheets(1).Range _
("A65536").End(xlUp)(2, 1) = wSheet.Name
Next wSheet
wbResults.Close SaveChanges:=False
Next i
End If
End With

On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

Will that do?

Mike

"excel-tr" wrote:

Hi,

for example assume , I have 30 workbooks and 30 worksheets in each
workbook.totally 900 worksheets. I do not want to open each workbook to
browse, there must be a way to see all worksheets in a way ? in a list ?
--
SAHRAYICEDIT-ISTANBUL

hi,
I added below code, but I cannot see worksheet names in workbooks in sub
folders ? what is the problem ?

..SearchSubFolders = IncludeSubFolder

If I want to see the directory of each worksheet next to the name of it how
can we revise the code?
for example;
book ( name of worksheet ) D:\library\...
--
SAHRAYICEDIT-ISTANBUL


"Mike":

Couldn't find the original link to the code courtesy of Ozgrid so had to
paste it in here. Copy this code to a new module and run it. Your workbook
must have a Sheet1 in it or alter the code to where you want the output to be

Sub GetAllWorksheetNames()
Dim i As Integer
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim wSheet As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next

Set wbCodeBook = ThisWorkbook

With Application.FileSearch
.NewSearch
.LookIn = "C:\my documents" 'amend to suit
.FileType = msoFileTypeExcelWorkbooks
If .Execute 0 Then
For i = 1 To .FoundFiles.Count
Set wbResults = Workbooks.Open(.FoundFiles(i))

wbCodeBook.Sheets(1).Range _
("A65536").End(xlUp)(2, 1) = UCase(wbResults.Name)
For Each wSheet In wbResults.Worksheets
wbCodeBook.Sheets(1).Range _
("A65536").End(xlUp)(2, 1) = wSheet.Name
Next wSheet
wbResults.Close SaveChanges:=False
Next i
End If
End With

On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

Will that do?

Mike

"excel-tr" wrote:

Hi,

for example assume , I have 30 workbooks and 30 worksheets in each
workbook.totally 900 worksheets. I do not want to open each workbook to
browse, there must be a way to see all worksheets in a way ? in a list ?
--
SAHRAYICEDIT-ISTANBUL