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

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

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