Macro Loop?

Rookeby7

New Member
Joined
Jul 5, 2019
Messages
18
Hi I am trying to test this on a folder containing 5 files, it opens all files but it only completes the action on 1 file then seems to freeze excel completely.

I am new to the world of Macros and lifted the code from a website, so I am hoping this will be a good way to get an answer.

Have I got the "Loop" in the wrong place, something else wrong, or is it likely to be a problem with Excel 2019? Thanks in advance

Code:
Sub ProcessFiles()
    Dim Filename, Pathname As String
    Dim wb As Workbook


    Pathname = ActiveWorkbook.Path & "R:\Testing\Test col shift loop\"
    Filename = Dir(Pathname & "*.xlsm")
    Do While Filename <> ""
        Set wb = Workbooks.Open(Pathname & Filename)
        DoWork wb
        wb.Close SaveChanges:=True
        Filename = Dir()
    Loop
End Sub


Sub DoWork(wb As Workbook)
    With wb
        Sheets("TF ROC").Select
    Range("EL2:EN2").Select
    Selection.Replace What:="G", Replacement:="F", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    End With
End Sub
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Hi, and welcome to Mr. Excel!

I'm no expert on this, but...

It looks to me, as if you're setting the path to the files, twice:
Code:
Pathname = ActiveWorkbook.Path & "R:\Testing\Test col shift loop\"
Code:
ActiveWorkbook.Path
... will give you the path to the files' folders, so you're saying that "Pathname" is set to the path to the active workbook (which I'm assuming is already "R:\Testing\Test col shift loop") AND you're adding "R:\Testing\Test col shift loop" on to the end of that. What I don't understand, is why your code's not erroring at that point. Try just using:
Code:
Pathname = ActiveWorkbook.Path
I've also noticed that you're using the same name for one of your workbook variables, in both the "Processfiles" sub procedure, AND the procedure you call from it - "DoWork". I've not closely studied your code, but this could be confusing to Excel - to have two variables running with the same name, at the same time. Perhaps rename one of these:
Code:
Sub DoWork(wrkb As Workbook)
    With wrkb
        Sheets("TF ROC").Select
    Range("EL2:EN2").Select
    Selection.Replace What:="G", Replacement:="F", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    End With
End Sub
 
Last edited:
Upvote 0
Thanks for your speedy response
I was originally running macro from empty "Book1" and it was opening the files.

I have tried this as suggested: Pathname = ActiveWorkbook.Path
having the first file in the folder open as "active workbook" and the macro does nothing at all:(

I've then tried with first file in the folder open
Pathname = "R:\Testing\Test col shift loop"
which at least spits back an error:

Run-time error 1004
Method 'Open' of object 'Workbooks' failed
Debugger highlights this:
Set wb = Workbooks.Open(Pathname & Filename)
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top