locked
running a macro in subdirectories RRS feed

  • Question

  • can anyone help me with a microsoft word macro? i am trying to create a macro that runs a second macro across multiple files and subfolders. I got half of it down, to the point where i can execute the macro to all files in a single folder, however cannot get it to search subfolders. here is what i have:

    Sub run_macro_on_multiple_files()
    '
    ' run_macro_on_multiple_files Macro
    '
    '



    Dim theFileName As String
    theFileName = Dir("FOLDERPATH\*.doc")
    Do While Len(theFileName) > 0
    ChangeFileOpenDirectory "FOLDERPATH"
    Documents.Open FileName:=theFileName

    Application.Run MacroName:="FOLDERPATH"

    ChangeFileOpenDirectory "C:\Documents and Settings\mmillar\Desktop\test"
    ActiveDocument.SaveAs FileName:=theFileName, _
    FileFormat:=wdFormatFilteredHTML
    ActiveWindow.Close
    theFileName = Dir
    Loop
    End Sub



    any help?

    Wednesday, June 29, 2011 3:12 PM

All replies

  • Uses the Microsoft Scripting Runtime File System Object.
    '---
    Sub ListAllFilesInFolders()
       'James Cone - Portland, Oregon USA
        Dim strPath As String
        Dim oFSO    As Object
        Dim oFile   As Object
        Dim oFolder As Object
        Dim N As Long
       
        strPath = "C:\Word Files\"                '<<<ADJUST
        Set oFSO = CreateObject("Scripting.FileSystemObject")
        Set oFolder = oFSO.GetFolder(strPath)
       
        For Each oFile In oFolder.Files

           'Do something here

            N = N + 1
        Next 'oFile
        Call ListSubFolderFiles(oFolder, N)
     
        Set oFSO = Nothing
        Set oFile = Nothing
        Set oFolder = Nothing
    End Sub
    '---
    Function ListSubFolderFiles(ByRef oParentFolder As Object, ByRef lngR As Long)
       'James Cone - Portland, Oregon USA
        Dim oSubFolder As Object
        Dim oFile As Object
        For Each oSubFolder In oParentFolder.SubFolders
            For Each oFile In oSubFolder.Files

               'Do something here

                lngR = lngR + 1
            Next
            ListSubFolderFiles oSubFolder, lngR
        Next 'oSubFolder
    End Function
    '---

    Jim Cone
    Portland, Oregon USA
    http://www.mediafire.com/PrimitiveSoftware
    (List Files XL add-in:  finds and lists files/folders with hyperlinks)
    Wednesday, June 29, 2011 11:04 PM
  • can anyone help me with a microsoft word macro? i am trying to create a macro that runs a second macro across multiple files and subfolders. I got half of it down, to the point where i can execute the macro to all files in a single folder, however cannot get it to search subfolders. here is what i have:

    Sub run_macro_on_multiple_files()
    '
    ' run_macro_on_multiple_files Macro
    '
    '



    Dim theFileName As String
    theFileName = Dir("FOLDERPATH\*.doc")
    Do While Len(theFileName) > 0
    ChangeFileOpenDirectory "FOLDERPATH"
    Documents.Open FileName:=theFileName

    Application.Run MacroName:="FOLDERPATH"

    ChangeFileOpenDirectory "C:\Documents and Settings\mmillar\Desktop\test"
    ActiveDocument.SaveAs FileName:=theFileName, _
    FileFormat:=wdFormatFilteredHTML
    ActiveWindow.Close
    theFileName = Dir
    Loop
    End Sub



    any help?


    An alternative solution you have want to consider is the 'black box' approach at

    Process all files in a folder

    http://www.tushar-mehta.com/publish_train/xl_vba_cases/1035%20vba-process%20all%20files.htm

    Modify the 'processOneFile' routine to do what you want to do for each file -- or write your own callback routine and use that as an argument to searchForFiles.


    Tushar Mehta (Technology and Operations Consulting)
    www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)
    Microsoft MVP Excel 2000-Present
    Thursday, June 30, 2011 4:59 PM