locked
Run a Macro Multiple Fils RRS feed

  • Question

  • Hi,

    With the reading of old posts, I have tried to modified a VBA code, but nothing is happening.


    I have to run this code in all files in a Folder To Be Complete to copy the final file to in a folder Completed

    Sub Macro6TidyAndGeneral()
        Columns("E:E").Select
        Cells.Replace What:=" (Task Complete)", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    End Sub



    Sub DoMacro6ForAllFiles()
      Dim stPath As String
      Dim stFile As String
      Dim stNewPath As String
      stPath = "C:\Users\Rakesh\Desktop\To Be Complete\"
      stNewPath = "C:\Users\Rakesh\Desktop\To Be Complete\Completed"

    I have tried here putting "C:\Users\Rakesh\Desktop\To Be Complete\Completed\" as well.

      If Dir(stNewPath, vbDirectory) = "" Then MkDir stNewPath
      stFile = Dir(stPath & "\*.xls")
      Do Until stFile = ""
        Workbooks.Open stPath & "\" & stFile, ReadOnly:=True
        Macro6TidyAndGeneral
        ActiveWorkbook.SaveAs stNewPath & "\" & stFile  '
        ActiveWorkbook.Close False
        stFile = Dir()
      Loop
    End Sub

    After entering the above code nothing is happening, if I select Sub DoMacro6ForAllFiles() in VBA nothing happening, but when I select both the above mentioned codes it is not doing anything.

    I have saved Book2 as Macro book, which I placed in To Be Complete folder.

    Any guidance would be highly appreciated.

    https://social.msdn.microsoft.com/Forums/en-US/690385b1-1e51-4881-8b38-abf03206804f/run-a-macro-in-multiple-files?forum=isvvba

    Thank you very much in advance.

    Regards,

    Rakesh

    Saturday, July 8, 2017 8:25 AM

All replies

  • 1) Since stPath contains a backslash, you shouldn't include a backslash in

    stFile = Dir(stPath & "\*.xls")

    and in

    Workbooks.Open stPath & "\" & stFile, ReadOnly:=True

    2) The code will only look at Excel 97-2003 workbooks (extension .xls). If your workbooks are in Excel 2007 and later format (extension .xlsx, .xlsm or .xlsb), you should use "*.xls*".

    3) I wouldn't place the workbook with the macro in the To Be Complete folder, to avoid the macro processing this workbook.

    Try this version

    Sub DoMacro6ForAllFiles()
        Dim stPath As String
        Dim stFile As String
        Dim stNewPath As String
        stPath = "C:\Users\Rakesh\Desktop\To Be Complete\"
        stNewPath = stPath & "Completed\"
        If Dir(stNewPath, vbDirectory) = "" Then
            MkDir stNewPath
        End If
        stFile = Dir(stPath & "*.xls*")
        Do Until stFile = ""
            Workbooks.Open stPath & stFile, ReadOnly:=True
            Macro6TidyAndGeneral
            ActiveWorkbook.SaveAs stNewPath & stFile
            ActiveWorkbook.Close False
            stFile = Dir
        Loop
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, July 8, 2017 9:30 AM
  • Hi, Thank you very much for your reply.

    I have tried the new version, but changed file type .xls to .xlsx. But it has created a folder completed. and after that nothing happened. I have checked the below code running separately in Book2. Its working. 

    Sub Macro6TidyAndGeneral()
        Columns("E:E").Select
        Cells.Replace What:=" (Task Complete)", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    End Sub

    I used F8 function to check the steps, after reaching to   Do Until stFile = "" its is directly going End Sub.

      Do Until stFile = ""
            Workbooks.Open stPath & stFile, ReadOnly:=True
            Macro6TidyAndGeneral
            ActiveWorkbook.SaveAs stNewPath & stFile
            ActiveWorkbook.Close False
            stFile = Dir
        Loop

    did I missed anything.

    Book 2 is placed in the same folder, do I have to place it somewhere else.

    Regards,

    Rakesh

    Saturday, July 8, 2017 11:27 AM
  • If the code skips the loop, there are no .xlsx workbooks in the folder C:\Users\Rakesh\Desktop\To Be Complete...

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, July 8, 2017 2:09 PM
  • Hi Hans,

    Thanks, I have updated it to "*.xls*". But still same issue.

    As per file properties : Microsoft Office Excel 97-2003 Worksheet (.xls)

    Sorry to bother you so much, I am not a Advance Excel guys, I know only copy paste things.

    Regards,

    Rakesh

    Sunday, July 9, 2017 5:38 AM
  • Perhaps the folder name is incorrect? I have tested the code using a folder that exists on my hard disk, and it worked...

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, July 9, 2017 8:55 AM