getting worksheet names
 

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




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
SearchSubFolders = IncludeSubFolder
.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

getting worksheet names
 

Hi excel-tr

See
http://www.xldynamic.com/source/xld.xlFAQ0002.html


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"excel-tr" wrote in message ...
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




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
SearchSubFolders = IncludeSubFolder
.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

getting worksheet names
 

Hi,

try:

..SearchSubFolders = True

I dont know, where "IncludeSubFolder" comes from.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

getting worksheet names
 

Oops, wrong reply


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" wrote in message ...
Hi excel-tr

See
http://www.xldynamic.com/source/xld.xlFAQ0002.html


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"excel-tr" wrote in message ...
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




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
SearchSubFolders = IncludeSubFolder
.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

getting worksheet names
 

Try

..SearchSubFolders = True



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"excel-tr" wrote in message ...
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




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
SearchSubFolders = IncludeSubFolder
.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