browse between worksheets ?

[Guest]

Hi,

assume you have 20 workbooks and 20 worksheet in each workbook.totally 400
worksheets.if 20 workbooks are in one folder, when below code is run, all
worksheet names ( 400 ) are written in a page.but if there is a folder in a
folder, below code cannot see the workbooks in the folder.

for example;assume you have excel files and one folder ( named sample ) in
adress C:\Documents and Settings.
below code cannot get worksheet names from the folder, how can we change the
code to browse all excel files whether they are in a folder or not ?

any expert ?



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:\Documents and Settings" '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

 

[NOPIK]

below code cannot get worksheet names from the folder, how can we change the
code to browse all excel files whether they are in a folder or not ?

With Application.FileSearch
.NewSearch
.LookIn = "C:\Documents and Settings" 'amend to suit
.FileType = msoFileTypeExcelWorkbooks

..SearchSubFolders = True ?