Open files in directory

[V. Roe]

Excel 97
I use the below code to open files in 3 different directories. The first
two open in alphabetical order (the same way they are listed in the
directory). However when I use the code in the 3rd directory it seems to
open the files at random. The first two directories are on a shared
network, the 3rd is on my C drive. Can anyone tell me if there is something
I might be missing? I really need the files to open in abc order.
Any help is greatly appreciated. Thanks
Valerie

Private Sub OkButton1_Click()
Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.Folder
Dim objFile As Scripting.File
Dim FullPath As String
Dim Month As String
Dim DirLoc As String
Range("n1").Select
Application.ScreenUpdating = False

Month = ComboBox1.Text
DirLoc = ComboBox3.Text
FullPath = DirLoc & "\" & Month

'Gets files from the correct directory

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(FullPath)
For Each objFile In objFolder.files

'Opens file

If objFile.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open FileName:=objFolder.Path & "\" & objFile.Name,
UpdateLinks:=True

'Prints and Exports Voc Rehab Information into the Summary Workbook

Call ExportAndPrintAllSey

ActiveWorkbook.Close
End If
Next
Application.ScreenUpdating = False
VocNorthForm.Hide
End Sub

 

[Jake Marx]

Hi Valerie,

As you've found, the FileSystemObject will return files in seemingly random
order. On my machine, they come back alphabetically (Windows XP Pro), but I
don't think you can count on that:

http://support.microsoft.com/default.aspx?scid=kb;en-us;189751

Anyway, I think you'll have to sort them yourself:

http://www.vb-helper.com/howto_dir_fso.html

Hope that helps.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[V. Roe]

Thanks
Not the answer I hoped to hear, but at least I won't drive myself crazy
trying to figure what I have done wrong.
Valerie

Hi Valerie,

As you've found, the FileSystemObject will return files in seemingly random
order. On my machine, they come back alphabetically (Windows XP Pro), but I
don't think you can count on that:

http://support.microsoft.com/default.aspx?scid=kb;en-us;189751

Anyway, I think you'll have to sort them yourself:

http://www.vb-helper.com/howto_dir_fso.html

Hope that helps.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Excel 97
I use the below code to open files in 3 different directories. The
first two open in alphabetical order (the same way they are listed in
the directory). However when I use the code in the 3rd directory it
seems to open the files at random. The first two directories are on
a shared network, the 3rd is on my C drive. Can anyone tell me if
there is something I might be missing? I really need the files to
open in abc order.
Any help is greatly appreciated. Thanks
Valerie

Private Sub OkButton1_Click()
Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.Folder
Dim objFile As Scripting.File
Dim FullPath As String
Dim Month As String
Dim DirLoc As String
Range("n1").Select
Application.ScreenUpdating = False

Month = ComboBox1.Text
DirLoc = ComboBox3.Text
FullPath = DirLoc & "\" & Month

'Gets files from the correct directory

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(FullPath)
For Each objFile In objFolder.files

'Opens file

If objFile.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open FileName:=objFolder.Path & "\" &
objFile.Name, UpdateLinks:=True

'Prints and Exports Voc Rehab Information into the Summary Workbook

Call ExportAndPrintAllSey

ActiveWorkbook.Close
End If
Next
Application.ScreenUpdating = False
VocNorthForm.Hide
End Sub