Macro Loop?

[Rookeby7]

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

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

 

[sykes]

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:

Pathname = ActiveWorkbook.Path & "R:\Testing\Test col shift loop\"

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:

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:

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

 

[Rookeby7]

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)