Loop Through Directory and Format Files

Alkemist

Board Regular
Joined
Nov 26, 2003
Messages
144
Hia Folks,

What I'm looking to do is get a macro to loop through a single directory and format each of the XL files within that directory ready for printing.

I presume for this I need a macro to open each XL file, apply format, save and close the file and then move onto the next file.

I've searched and not found anything that I can use. Unfortunately I'm not skilled enough in the art of macro writing to utilise bits of code I did find!

I have managed to write / record the code to do the formatting, I just need the the magic bit of code to do the looping!

Cheers...Alkemist...
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Thanks for that...Unfortunately I only have very basic macro knowledge so it doesn't get me much further I'm afraid. I did have a play to see if I could get it to do what I wanted it to, it just wasn't going anywhere very fast...

The code doesn't seem to actually open up any workbooks in the folder so my bit that formats just applies to itself, if that makes sense. It needs to opne up a workbook, format, save and close the workbook and move onto the next one.

Sorry for being a bit slow here, I am trying though!!!

Cheers..Alkemist
 
Upvote 0
Hi A,

The following is one approach to looping through the workbooks in a directory (you could make use of the Dir command instead - see the Help files for further info on this).
Code:
Sub OpenAndProcess()
    Dim vaFileName As Variant
    Const MyDir As String = "C:\My Documents"
    
    With Application.FileSearch
        .NewSearch
        .LookIn = MyDir
        .SearchSubFolders = False
        .FileType = msoFileTypeExcelWorkbooks
        If .Execute > 0 Then
            Application.ScreenUpdating = False
            For Each vaFileName In .FoundFiles
            'loop through each found workbook
                ProcessData vaFileName
                'pass workbook fullname to process routine
            Next
        Else
            MsgBox "There were no Excel files found."
        End If
        Application.ScreenUpdating = True
    End With

End Sub

Sub ProcessData(ByVal Fname As String)
    Dim wbk As Workbook
    
    Set wbk = Workbooks.Open(Filename:=Fname)
    '
    'do your stuff
    '
    wbk.Close savechanges:=True

End Sub
The first routine does the actual looping through the workbooks (change the directory location to suit). It passes the name of the workbook to the second routine where it is opened, processed, and closed (with changes saved).

Use the code that you have generated for the formatting of the workbooks within the second routine. (Note that you will be using the wbk workbook variable rather than ActiveWorkbook). If you struggle with this then post of the code for the formatting and somebody will help you out.

HTH
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

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