Open Files in a Folder in Alphabetical Order

E

EXCEL-LENCY

Board Regular
Joined
Feb 9, 2010
Messages
75
Hello,

I have the code below that opens files in a particular folder... I need it to open them in the order that they are in the file (alphabetical by filename). Is this possible?

Sub AllFiles()
Dim folderPath As String
Dim filename As String
Dim wb As Workbook

folderPath = "C:\SAP Imports\Sales Orders\"

If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\"

filename = Dir(folderPath & "*.xls")
Do While filename <> ""
Application.ScreenUpdating = False
Set wb = Workbooks.Open(folderPath & filename)

Call Mymacro


filename = Dir
Loop
Application.ScreenUpdating = True
End Sub

Thanks,
Jason
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
From the look of your other thread, I'm assuming the real need here is some way to start the process again where it leaves off if it gets interrupted partway through the processing. Is that accurate?

You could use the same DIR() method to create an actual list of file on your worksheet that you can see, then use the existing loop to not only run your macro on each file, but to then mark the file as "processed" on your reference list.

This would allow you to not worry about the specific order, but you could still see/know what really happened.

Yes?
 
Upvote 0
That's funny,

I could have sworn that "*.xls" goes through things in alphabetical order. Symbols > numbers > Letters first.

Unless what you are talking about is indeed what the previous poster suggested, a means of identifying where you leave off and how to continue on from that point forward.

Regards,
jc
 
Upvote 0
This is a technique with "reporting" so you know what's going on. There are two macros.

First run the CreateFileListing to create a sheet with all the files to process on a special sheet.

Then run the updated ProcessAllFiles which will run from the filenames placed into the FileList worksheet.

As each workbook is processed, it will note in the FileList sheet that it is done. This should be sufficient feedback.

As an added bonus, I did try to apply Excel's basic sorting to the filelist, so this may get you the alphabetic thing you wanted, too.

Code: 
Option Explicit
Const fPath As String = "C:\SAP Imports\Sales Orders\"  'don't forget the final \
Dim fName   As String

Sub ProcessAllFiles()
Dim wb      As Workbook
Dim fRNG    As Range
Dim file    As Range

Set fRNG = Sheets("FileList").Range("A:A").SpecialCells(xlCellTypeConstants)
Application.ScreenUpdating = False

For Each file In fRNG
    If file.Offset(0, 1) <> "processed" Then
        Set wb = Workbooks.Open(fPath & file)
        
            Call MyMacro
        
        wb.Close True  'save and close workbook opened, set to FALSE to close with no save
        file.Offset(0, 1) = "processed"
    End If
Next file

Application.ScreenUpdating = True
End Sub


Sub CreateFileListing()
Dim wsList  As Worksheet

    If Not Evaluate("ISREF(FileList!A1)") Then _
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "FileList"
    
    Set wsList = Sheets("FileList")
    wsList.Cells.Clear
    fName = Dir(fPath & "*.xls")
    
    Do While Len(fName) > 0
        wsList.Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = fName
        fName = Dir
    Loop
    
wsList.Columns.AutoFit
wsList.Range("A:A").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo
End Sub
 
Upvote 0
I used this macro on a test folder and it works perfectly... it doesn't seem to work in a folder that contains folders, though. The folderpath I would like it to run through has files (which are the ones that I would like it to open & close) and folders (which I would like it to ignore). Also, I need to update links in each file when it is opened. This...

Set wb = Workbooks.Open (fPath & file), UpdateLinks:=True

... doesn't seem to work.

Is there another way to handle it? Thanks in advance for the help!!

Jason
 
Upvote 0
the CreateFileListing macro only creates a list of filenames, it doesn't list folders, so I'm not sure why your use of the second macro would be affected in any way by folders inside the target folder.


As for updating links:
Code: 
        Set wb = Workbooks.Open(fPath & file, UpdateLinks:=True)
 
Upvote 0
Thanks for the update links part.

I'm getting an error at the following when I debug.

Set fRNG = Sheets("FileList").Range("A:A").SpecialCells(xlCellTypeConstants)

They are all Excel (.xls) files with the exception of 5 folders. Any idea what may be causing this?

Thanks!
 
Upvote 0
I figured it out... my error. Thank you again for your help. It is much appreciated. The great people on this site keeps me coming back.
 
Upvote 0
If you're wondering why I used the CellTypeConstants approach in that macro, one assumes that after you create the file list with the other macro, you MIGHT clear some of the cells randomly throughout the listing. You can clear any values you wish, leave the blank cells in the list, the CellTypeConstants method finds all the cells with an actual value and makes a range out of just those cells, then we cycle through those specific cells.

Cheers.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Macro works for one file, does not work for all files in the directory
2
Replies
13
Views
484
Alex Blakenburg
A
J
  • Question
Power Query - Data Refresh Error in Macro
Replies
3
Views
134
bobsan42
bobsan42
P
  • Question
VBA to import multilpe xml files
Replies
1
Views
44
PippaThePointer
P
J
  • Question
VBA Script - Can't figure out one error...
2
Replies
15
Views
239
Peter_SSs
Peter_SSs
G
  • Question
Open workbooks in folder and run Macro in each book
Replies
7
Views
363
glynn1969
G

Forum statistics

Threads
1,214,976
Messages
6,122,541
Members
449,089
Latest member
davidcom

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top