Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching numbers in Worksheet? | Excel Worksheet Functions | |||
Return most recent file in subdirectory with out using FileSearch | Excel Programming | |||
Saving to subdirectory using date() | Excel Programming | |||
Find Correct Subdirectory | Excel Programming | |||
Searching for worksheet | Excel Programming |