Consulting

Results 1 to 6 of 6

Thread: apply word macro on all file in a folder

  1. #1
    VBAX Newbie
    Joined
    Jan 2015
    Posts
    1
    Location

    apply word macro on all file in a folder

    Hi could you help me please?
    I'd like to create a macro "launch_mod_macro" in order to apply the following macro "mod_macro" on all "*.dot" file in "c:\test_macro".

    The "mod_macro" changes some words of the layout, saves and closes the active file.

    I find that the "ExecuteApplyMacroToAllFiles" macro can execute the macro "MyMacro" on each file in "C:\test folder" , but it doesn't work and I don't know why.
    Please find below the macro:

    Sub ExecuteApplyMacroToAllFiles()

    'Change the path to the main folder
    Call ApplyMacroToAllFiles("C:\test folder")

    End Sub

    Sub ApplyMacroToAllFiles(ByVal MyPath As String)

    Dim FileSys As Object
    Dim objFolder As Object
    Dim objSubFolder As Object
    Dim objFile As Object
    Dim wkbOpen As Workbook

    Set FileSys = CreateObject("Scripting.FileSystemObject")
    Set objFolder = FileSys.GetFolder(MyPath)

    Application.ScreenUpdating = False

    For Each objSubFolder In objFolder.SubFolders
    For Each objFile In objSubFolder.Files
    Set wkbOpen = Workbooks.Open(filename:=objFile)
    'Change the name of your macro
    Call MyMacro
    wkbOpen.Close savechanges:=True
    Next
    Call ApplyMacroToAllFiles(objSubFolder.Path)
    Next

    Application.ScreenUpdating = True

    End Sub

    Please can someone help me?
    thank you very much

  2. #2
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    832
    Location
    A couple of thing don't look right...
    Call ApplyMacroToAllFiles("C:\test folder\")
    Note the additional "\". This also looks punky...
    For Each objSubFolder In objFolder
    This also doesn't seem right but it's hard to tell
    Call ApplyMacroToAllFiles(objSubFolder.Path)
    
    HTH. Dave

  3. #3
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    832
    Location
    This seems like it should work. Untested. Dave
    Sub ApplyMacroToAllFiles(ByVal MyPath As String)
    Dim FileSys As Object
    Dim objFolder As Object
    Dim objSubFolder As Object
    Dim objFile As Object
    Dim wkbOpen As Workbook
    Application.ScreenUpdating = False
    'loop files
    Set FileSys = CreateObject("Scripting.FileSystemObject")
    Set objFolder = FileSys.GetFolder(MyPath)
    For Each objFile In objSubFolder.Files
    If Right(objFile.Name, 4) = ".dot" Then
    Set wkbOpen = Workbooks.Open(Filename:=objFile.Name)
    'Change the name of your macro
    Call MyMacro
    wkbOpen.Close savechanges:=True
    End If
    Next objFile
    'loop subfolder files
    For Each objSubFolder In objFolder.SubFolders
    For Each objFile In objSubFolder.Files
    If Right(objFile.Name, 4) = ".dot" Then
    Set wkbOpen = Workbooks.Open(Filename:=objFile.Name)
    'Change the name of your macro
    Call MyMacro
    wkbOpen.Close savechanges:=True
    End If
    Next objFile
    Next objSubFolder
    Set objFolder = Nothing
    Set objFolder = Nothing
    Application.ScreenUpdating = True
    End Sub

  4. #4
    VBAX Wizard
    Joined
    May 2004
    Posts
    6,713
    Location
    First of that code is trying to action EXCEL files, not Word files. Of course executing in Word will do nothing.

    Secondly, using the Dir function works quite well for this . Look up Dir in VBA help. Or search here for examples.

  5. #5
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    832
    Location
    Gerry I agree that nothing will happen. The op said that the code worked and it was a file search problem. It makes no sense to me either but I don't know everything. Hope you don't spend hours wondering why your DIR involved code won't acknowledge a file's existence even though it does exist and the FSO can proof it. Bin ther don that... grrr. I'm hoping that you will continue to offer your help here. I'm sure if the op ever returns he/she will appreciate your expertise. Dave

  6. #6
    VBAX Wizard
    Joined
    May 2004
    Posts
    6,713
    Location
    Well if they really need to action subfolders, then they do need FSO. But as stated, it does not look like subfolders are needed. Shrug...who knows.

Posting Permissions

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