Hi,i am new to macro coding,i have to browse thru a directory and sub folders get the names of 2 files,and get the names of sheets from each file one by one.ex:file 1 sheet and file 2 sheet 1 and perform a macro on the 2 sheets and the loop thru the next sheet in both the files.please find the code below:
Sub test()

Dim myDir As String, fn As String, ws As Worksheet

Dim myDir1 As String, fn1 As String, ws1 As Worksheet

Dim ind As Long

Dim wbk As Workbook, wbk1 As Workbook

ind = 1

myDir = "P:\test1\beta" '<- change folder path

myDir1 = "P:\test1\prod" '<- change folder path

Do

fn = GetFile(myDir, ind) 'Dir(myDir & "\*.xls")

fn1 = GetFile(myDir1, ind) 'Dir(myDir1 & "\*.xls")

'Do While fn < "" And fn1 < ""

If fn < "" And fn1 < "" Then

Set wbk = Nothing

Set wbk = Application.Workbooks.Open(myDir & "\" & fn)

Set wbk1 = Nothing

Set wbk1 = Application.Workbooks.Open(myDir1 & "\" & fn1)

With wbk 'Workbooks.Open(myDir & "\" & fn)

For Each ws In .Sheets

MsgBox "filename is " & fn & " sheet name is " & ws.Name

'Debug.Print "filename is " & fn & " sheet name is " & ws.Name

With wbk1 'Workbooks.Open(myDir1 & "\" & fn1)

For Each ws1 In .Sheets

MsgBox "filename is " & fn1 & " sheet name is " & ws1.Name

'Debug.Print "filename is " & fn1 & " sheet name is " & ws1.Name

Next '---- problem here : inner loop is going on

End With

Next

wbk1.Close False

wbk.Close False

End With

End If

'Loop

ind = ind + 1

Loop Until fn = "" And fn1 = ""

End Sub

'procedure to get the file from directory

Function GetFile(filePath As String, fileInd As Long) As String

Dim ind As Long

ChDir filePath

GetFile = Dir(filePath & "\*.xlsx")

For ind = 2 To fileInd

GetFile = Dir

Next

End Function


The issue is the 2nd file all the sheet names i was able to get,but the file 1 has the sheet1(file1 sheet1 and file2 sheet1,file2 sheet2,file2 sheet3).i need file1 sheet1,file2 sheet1 and then file1 sheet2 ,file2 sheet2 and so on.please help.