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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
Reply |
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 |