Open files in directory
 

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

Open files in directory
 

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...b;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 wrote:
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

Open files in directory
 

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
"Jake Marx" wrote in message
...
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...b;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 wrote:
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