Results 1 to 4 of 4

Thread: Loop Through Directory and Format Files
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2003
    Posts
    144
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Loop Through Directory and Format Files

    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...

  2. #2
    Board Regular
    Join Date
    Feb 2003
    Location
    Luton, England.
    Posts
    8,127
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hopefully this basic structure will help you to go forward :-

    http://www.mrexcel.com/board2/viewto...246&highlight=

    The best method of dealing with code for handling the individual files will be to record a macro of processing a file and amend that.
    Regards
    BrianB (using XL2003 & 2010)
    www.cycleofgrowth.com
    Most problems occur from starting at the wrong place.
    Use a cup of coffee to speed up all Windows processes.
    It is easy until you know how.
    **FORMATTED/COMMENTED CODE IS MORE LIKELY TO GET A REPLY

  3. #3
    Board Regular
    Join Date
    Nov 2003
    Posts
    144
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP Richie(UK)'s Avatar
    Join Date
    May 2002
    Location
    UK
    Posts
    3,329
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    Richie

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •