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
> >