Run a Macro Multiple Fils

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

Answer 1

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)

Answer 2

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

Answer 3

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)

Answer 4

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

Answer 5

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)