searching for a worksheet name while going through a subdirectory
 

I have a master spreadsheet in which I search through a subdirectory to
update it with monthly data.
This works fine until I try to do an if statement that checks for the name
of the worksheet in the workbook it is currently reading.. Here is the code
below. I thank you in advance for your help. When I run this I get **WRONG
for all of them.

Application.ScreenUpdating = False
Set FS = Application.FileSearch
strPath = "x:\By Store Number"
szSummary = ActiveWorkbook.Name
strMonth = "Feb"
strMonth1 = "Canada February"
Sheets(1).Name = strMonth1
strMonth2 = "USA February"
Sheets(2).Name = strMonth2
strOther = "Country not Defined"
Sheets(3).Name = strOther


n = 1

With FS
.NewSearch
.LookIn = strPath
.SearchSubFolders = True
'.FileType = msoFileTypeExcelWorkbooks
.Filename = strMonth
iCount = .Execute
strMessage = Format(iCount, "0 ""Files Found""")

For Each vaFileName In .FoundFiles
'MsgBox strMessage
'strMessage = strMessage & vbCr & vaFileName
Set wb = Workbooks.Open(vaFileName)

' get info of this workbook to update the summary
' sales figures

Workbooks(szSummary).Activate
'If worksheet has an information sheet then it's valid else
flag it
For Each ws In wb.Worksheets
If ws.Name = "Information" Then
ThisWorkbook.Sheets(1).Cells(n, 1) =
wb.Sheets("Information").Cells(5, 2)
ThisWorkbook.Sheets(1).Cells(n, 2) =
wb.Sheets("Information").Cells(4, 2)
Else
ThisWorkbook.Sheets(1).Cells(n, 1) = "**WRONG"
ThisWorkbook.Sheets(1).Cells(n, 2) =
wb.Sheets(2).Cells(3, 2)
End If
Next ws
ThisWorkbook.Sheets(1).Cells(n, 3) = wb.Sheets("Royalty
Report").Cells(13, 2)
ThisWorkbook.Sheets(1).Cells(n, 4) = wb.Sheets("Royalty
Report").Cells(13, 3)

searching for a worksheet name while going through a subdirectory
 

For Each vaFileName In .FoundFiles
Set wb = Workbooks.Open(vaFileName)
Workbooks(szSummary).Activate
ThisWorkbook.Sheets(1).Cells(n, 1) = "**WRONG"
ThisWorkbook.Sheets(1).Cells(n, 2) = wb.Sheets(2).Cells(3, 2)
For Each ws In wb.Worksheets
If ws.Name = "Information" Then
ThisWorkbook.Sheets(1).Cells(n, 1) = wb.Sheets("Information").Cells(5, 2)
ThisWorkbook.Sheets(1).Cells(n, 2) = wb.Sheets("Information").Cells(4, 2)
Exit For
End If
Next ws
ThisWorkbook.Sheets(1).Cells(n, 3) = wb.Sheets("Royalty Report ").Cells(13, 2)
ThisWorkbook.Sheets(1).Cells(n, 4) = wb.Sheets("Royalty Report ").Cells(13, 3)
Next
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"ibbm"
wrote in message...
I have a master spreadsheet in which I search through a subdirectory to
update it with monthly data.
This works fine until I try to do an if statement that checks for the name
of the worksheet in the workbook it is currently reading.. Here is the code
below. I thank you in advance for your help. When I run this I get **WRONG
for all of them.

Application.ScreenUpdating = False
Set FS = Application.FileSearch
strPath = "x:\By Store Number"
szSummary = ActiveWorkbook.Name
strMonth = "Feb"
strMonth1 = "Canada February"
Sheets(1).Name = strMonth1
strMonth2 = "USA February"
Sheets(2).Name = strMonth2
strOther = "Country not Defined"
Sheets(3).Name = strOther
n = 1

With FS
.NewSearch
.LookIn = strPath
.SearchSubFolders = True
'.FileType = msoFileTypeExcelWorkbooks
.Filename = strMonth
iCount = .Execute
strMessage = Format(iCount, "0 ""Files Found""")

For Each vaFileName In .FoundFiles
'MsgBox strMessage
'strMessage = strMessage & vbCr & vaFileName
Set wb = Workbooks.Open(vaFileName)
' get info of this workbook to update the summary sales figures
Workbooks(szSummary).Activate
'If worksheet has an information sheet then it's valid else flag it
For Each ws In wb.Worksheets
If ws.Name = "Information" Then
ThisWorkbook.Sheets(1).Cells(n, 1) = wb.Sheets("Information").Cells(5, 2)
ThisWorkbook.Sheets(1).Cells(n, 2) = wb.Sheets("Information").Cells(4, 2)
Else
ThisWorkbook.Sheets(1).Cells(n, 1) = "**WRONG"
ThisWorkbook.Sheets(1).Cells(n, 2) = wb.Sheets(2).Cells(3, 2)
End If
Next ws
ThisWorkbook.Sheets(1).Cells(n, 3) = wb.Sheets("Royalty Report").Cells(13, 2)
ThisWorkbook.Sheets(1).Cells(n, 4) = wb.Sheets("Royalty Report").Cells(13, 3)

searching for a worksheet name while going through a subdirect
 

This did the trick. Awesome thanks so much!

"Jim Cone" wrote:

For Each vaFileName In .FoundFiles
Set wb = Workbooks.Open(vaFileName)
Workbooks(szSummary).Activate
ThisWorkbook.Sheets(1).Cells(n, 1) = "**WRONG"
ThisWorkbook.Sheets(1).Cells(n, 2) = wb.Sheets(2).Cells(3, 2)
For Each ws In wb.Worksheets
If ws.Name = "Information" Then
ThisWorkbook.Sheets(1).Cells(n, 1) = wb.Sheets("Information").Cells(5, 2)
ThisWorkbook.Sheets(1).Cells(n, 2) = wb.Sheets("Information").Cells(4, 2)
Exit For
End If
Next ws
ThisWorkbook.Sheets(1).Cells(n, 3) = wb.Sheets("Royalty Report ").Cells(13, 2)
ThisWorkbook.Sheets(1).Cells(n, 4) = wb.Sheets("Royalty Report ").Cells(13, 3)
Next
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"ibbm"
wrote in message...
I have a master spreadsheet in which I search through a subdirectory to
update it with monthly data.
This works fine until I try to do an if statement that checks for the name
of the worksheet in the workbook it is currently reading.. Here is the code
below. I thank you in advance for your help. When I run this I get **WRONG
for all of them.

Application.ScreenUpdating = False
Set FS = Application.FileSearch
strPath = "x:\By Store Number"
szSummary = ActiveWorkbook.Name
strMonth = "Feb"
strMonth1 = "Canada February"
Sheets(1).Name = strMonth1
strMonth2 = "USA February"
Sheets(2).Name = strMonth2
strOther = "Country not Defined"
Sheets(3).Name = strOther
n = 1

With FS
.NewSearch
.LookIn = strPath
.SearchSubFolders = True
'.FileType = msoFileTypeExcelWorkbooks
.Filename = strMonth
iCount = .Execute
strMessage = Format(iCount, "0 ""Files Found""")

For Each vaFileName In .FoundFiles
'MsgBox strMessage
'strMessage = strMessage & vbCr & vaFileName
Set wb = Workbooks.Open(vaFileName)
' get info of this workbook to update the summary sales figures
Workbooks(szSummary).Activate
'If worksheet has an information sheet then it's valid else flag it
For Each ws In wb.Worksheets
If ws.Name = "Information" Then
ThisWorkbook.Sheets(1).Cells(n, 1) = wb.Sheets("Information").Cells(5, 2)
ThisWorkbook.Sheets(1).Cells(n, 2) = wb.Sheets("Information").Cells(4, 2)
Else
ThisWorkbook.Sheets(1).Cells(n, 1) = "**WRONG"
ThisWorkbook.Sheets(1).Cells(n, 2) = wb.Sheets(2).Cells(3, 2)
End If
Next ws
ThisWorkbook.Sheets(1).Cells(n, 3) = wb.Sheets("Royalty Report").Cells(13, 2)
ThisWorkbook.Sheets(1).Cells(n, 4) = wb.Sheets("Royalty Report").Cells(13, 3)