Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening Files from a folder in sorted order.
Hi All,
Please find the following bit of code in my program: '------------------------------------------------------------------------- varWorkbookName = Application.GetOpenFilename("Microsoft Excel files (*.xls),*.xls", , "Please select the file(s) to process", , True) If TypeName(varWorkbookName) = "Boolean" Then Exit Sub For lngNoOfPayrolls = 1 To UBound(varWorkbookName) strCurrentWorkbook = Dir(varWorkbookName(lngNoOfPayrolls)) Workbooks.Open varWorkbookName(lngNoOfPayrolls) '------------------------------------------------------------------------- The files in the folder are like: Dec99 BOT Nov99 BOT Dec99 HKG Nov99 HKG .. .. so on for all the months and years from Jan99 till Mar05. The files are in random order in this folder. I basically want to open them in sorted order (Atleast sorted according to the first 5 characters of the filename) , i.e., Apr99 BOT Apr99 HKG .. .. so on I hope I have been clearer in explaining the situation. I would appreciate if someone come back to me asap. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening Files from a folder in sorted order.
How about reading all the filenames into an array, sort the array, and then
loop through the array opening them all. -- HTH RP (remove nothere from the email address if mailing direct) "Excel Beginner" wrote in message om... Hi All, Please find the following bit of code in my program: '------------------------------------------------------------------------- varWorkbookName = Application.GetOpenFilename("Microsoft Excel files (*.xls),*.xls", , "Please select the file(s) to process", , True) If TypeName(varWorkbookName) = "Boolean" Then Exit Sub For lngNoOfPayrolls = 1 To UBound(varWorkbookName) strCurrentWorkbook = Dir(varWorkbookName(lngNoOfPayrolls)) Workbooks.Open varWorkbookName(lngNoOfPayrolls) '------------------------------------------------------------------------- The files in the folder are like: Dec99 BOT Nov99 BOT Dec99 HKG Nov99 HKG . . so on for all the months and years from Jan99 till Mar05. The files are in random order in this folder. I basically want to open them in sorted order (Atleast sorted according to the first 5 characters of the filename) , i.e., Apr99 BOT Apr99 HKG . . so on I hope I have been clearer in explaining the situation. I would appreciate if someone come back to me asap. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening Files from a folder in sorted order.
From the command prompt:
dir c:\yourfolder /ON /b c:\myfile.lst will write the names of all files in alphabetical order (case insensitiveie. a and A before b and B etc). You can then read c:\myfile.lst line by line and open each one in turn "Excel Beginner" wrote: Hi All, Please find the following bit of code in my program: '------------------------------------------------------------------------- varWorkbookName = Application.GetOpenFilename("Microsoft Excel files (*.xls),*.xls", , "Please select the file(s) to process", , True) If TypeName(varWorkbookName) = "Boolean" Then Exit Sub For lngNoOfPayrolls = 1 To UBound(varWorkbookName) strCurrentWorkbook = Dir(varWorkbookName(lngNoOfPayrolls)) Workbooks.Open varWorkbookName(lngNoOfPayrolls) '------------------------------------------------------------------------- The files in the folder are like: Dec99 BOT Nov99 BOT Dec99 HKG Nov99 HKG .. .. so on for all the months and years from Jan99 till Mar05. The files are in random order in this folder. I basically want to open them in sorted order (Atleast sorted according to the first 5 characters of the filename) , i.e., Apr99 BOT Apr99 HKG .. .. so on I hope I have been clearer in explaining the situation. I would appreciate if someone come back to me asap. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
order of opening files with sumproduct returning #REF! | Excel Worksheet Functions | |||
Default folder for opening/saving files | Excel Discussion (Misc queries) | |||
Opening files from a variable (todays' date) folder name | Excel Programming | |||
opening and closing files in order with vba | Excel Programming | |||
Opening all txt files in a folder and saving as excel | Excel Programming |