Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Open Books with VBA based on certain string
Is there a way to look in a folder and if a file has a certain string
in it's name "Sep2006", it will open. I don't care what else it has in the file name, but if that string is in there, I want it to open. Basically, I'm going to have a sheet for say 10 people with their name and Sep2006 in a folder. I want to then open 1 sheet at a time, take a value from it, and aggregate it into a new sheet. Thanks, Brett |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Open Books with VBA based on certain string
Short answer: yes, I think. I presume you want the workbook that is going to
have the aggregated data placed into it do the opening and extracting from the other workbooks? Couple of ways to attack it, easiest is if the 'control' or new aggregate workbook is in the same folder with the others. Then with VBA code you could have it determine it's own location (and thus the location of the other workbooks) and using the DIR() function in VBA, you would examine the filenames of other workbooks in the same folder and when you found one with Sep2006 within it that is of type .xls, then open it, grab the information you need, place it into the control workbook, close it and look for another until it runs out of them. Reiterative DIR() basically goes thru each possible match until it runs out of possible matches, then returns an empty string, so don't even have to worry about processing same book twice (during one run of the code). Code might look something like this: Sub FindSpecialFiles() Dim anyFile As String anyFile = UCase(Dir(Left(ThisWorkbook.FullName, _ Len(ThisWorkbook.FullName) - _ Len(ThisWorkbook.Name)) & "*.xls")) Do While anyFile < "" 'remember strings are case sensitive If InStr(anyFile, "SEP2006") Then '....perform file open, '....data extraction '....data aggregation '....close Sep2006 file End If anyFile = UCase(Dir) ' get next possible file Loop End Sub I used UCase and made the comparison to "SEP2006" since strings are case sensitive in VB and the UCase assures that the returned filename(s) are all in uppercase. " wrote: Is there a way to look in a folder and if a file has a certain string in it's name "Sep2006", it will open. I don't care what else it has in the file name, but if that string is in there, I want it to open. Basically, I'm going to have a sheet for say 10 people with their name and Sep2006 in a folder. I want to then open 1 sheet at a time, take a value from it, and aggregate it into a new sheet. Thanks, Brett |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Open Books with VBA based on certain string
Hi,
Thanks a lot for that! It does what I need in the first loop, but it creates an infinite loop. How can I make the anyFile = UCase(Dir) to at somepoint equal ""? Thanks, Brett JLatham wrote: Short answer: yes, I think. I presume you want the workbook that is going to have the aggregated data placed into it do the opening and extracting from the other workbooks? Couple of ways to attack it, easiest is if the 'control' or new aggregate workbook is in the same folder with the others. Then with VBA code you could have it determine it's own location (and thus the location of the other workbooks) and using the DIR() function in VBA, you would examine the filenames of other workbooks in the same folder and when you found one with Sep2006 within it that is of type .xls, then open it, grab the information you need, place it into the control workbook, close it and look for another until it runs out of them. Reiterative DIR() basically goes thru each possible match until it runs out of possible matches, then returns an empty string, so don't even have to worry about processing same book twice (during one run of the code). Code might look something like this: Sub FindSpecialFiles() Dim anyFile As String anyFile = UCase(Dir(Left(ThisWorkbook.FullName, _ Len(ThisWorkbook.FullName) - _ Len(ThisWorkbook.Name)) & "*.xls")) Do While anyFile < "" 'remember strings are case sensitive If InStr(anyFile, "SEP2006") Then '....perform file open, '....data extraction '....data aggregation '....close Sep2006 file End If anyFile = UCase(Dir) ' get next possible file Loop End Sub I used UCase and made the comparison to "SEP2006" since strings are case sensitive in VB and the UCase assures that the returned filename(s) are all in uppercase. " wrote: Is there a way to look in a folder and if a file has a certain string in it's name "Sep2006", it will open. I don't care what else it has in the file name, but if that string is in there, I want it to open. Basically, I'm going to have a sheet for say 10 people with their name and Sep2006 in a folder. I want to then open 1 sheet at a time, take a value from it, and aggregate it into a new sheet. Thanks, Brett |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Open Books with VBA based on certain string
It shouldn't be going into an infinite loop.
The first time we use the DIR() to set up anyFile outside of the loop (with that long concatenation statement) anyFile will either be "" (empty string, no space between the double-quotes) if no file was found that matched, in which case the loop won't even begin. But once in the loop, repeating the DIR statement without a new parameter just repeats the search for the next file with matching name. So lets say that you have 4 files with SEP2006 in their names: file01_Sep2006.xls File03_Sep2006.xls file4_sep2006.xls f2_sep2006.xls The DIR may return them in any sequence, not necessarily their 'sorted order' or the order in which they were saved to the drive. So let's say that it returns them in 1,2,3 4 order just for example. Outside of the loop, anyFile would get set to "FILE01_SEP2006.XLS" it would not be zero length, so it would enter the loop. Then the Instr() test is going to return a non-zero, positive value which will cause the stuff in the If...Then statement block to execute. The anyFile=UCase(DIR) will simply get the next (any) filename with .xls at the end and it may not even be one of our SEP2006 files, it could be named AUG2006.xls, but that would fail the InStr() test. But eventually UCase(DIR) is going to pick up the names of the other files of interest. Finally it's going to run out of the list of .xls files in the folder to examine, and when it does that, that is when it returns an empty string and it will fall out of the loop. You can test what is being returned each time by putting a MsgBox statement just ahead of the Do While statement as MsgBox "Starting With: " & anyFile then put another down right after anyFile = UCase(DIR) as MsgBox "Found Excel File: " & anyfile so you'll be able to verify that it's returning different file name each time through the loop. " wrote: Hi, Thanks a lot for that! It does what I need in the first loop, but it creates an infinite loop. How can I make the anyFile = UCase(Dir) to at somepoint equal ""? Thanks, Brett JLatham wrote: Short answer: yes, I think. I presume you want the workbook that is going to have the aggregated data placed into it do the opening and extracting from the other workbooks? Couple of ways to attack it, easiest is if the 'control' or new aggregate workbook is in the same folder with the others. Then with VBA code you could have it determine it's own location (and thus the location of the other workbooks) and using the DIR() function in VBA, you would examine the filenames of other workbooks in the same folder and when you found one with Sep2006 within it that is of type .xls, then open it, grab the information you need, place it into the control workbook, close it and look for another until it runs out of them. Reiterative DIR() basically goes thru each possible match until it runs out of possible matches, then returns an empty string, so don't even have to worry about processing same book twice (during one run of the code). Code might look something like this: Sub FindSpecialFiles() Dim anyFile As String anyFile = UCase(Dir(Left(ThisWorkbook.FullName, _ Len(ThisWorkbook.FullName) - _ Len(ThisWorkbook.Name)) & "*.xls")) Do While anyFile < "" 'remember strings are case sensitive If InStr(anyFile, "SEP2006") Then '....perform file open, '....data extraction '....data aggregation '....close Sep2006 file End If anyFile = UCase(Dir) ' get next possible file Loop End Sub I used UCase and made the comparison to "SEP2006" since strings are case sensitive in VB and the UCase assures that the returned filename(s) are all in uppercase. " wrote: Is there a way to look in a folder and if a file has a certain string in it's name "Sep2006", it will open. I don't care what else it has in the file name, but if that string is in there, I want it to open. Basically, I'm going to have a sheet for say 10 people with their name and Sep2006 in a folder. I want to then open 1 sheet at a time, take a value from it, and aggregate it into a new sheet. Thanks, Brett |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Open Files | Excel Discussion (Misc queries) | |||
Auto convert an alphanumeric string (CIS9638S) to numbers only? | Excel Worksheet Functions | |||
How do I find a file/spreadsheet that Excel says is Already open but I can't find it? | Excel Discussion (Misc queries) | |||
How to open different excel books in different windows? | Excel Discussion (Misc queries) | |||
Sumif Linking to Another Workbook error #VALUE! | Excel Discussion (Misc queries) |