excel - VBA macro to mass update multiple files in same location

Question

Very new to this so please help. Im trying to mass update files in a static folder location, many files in one folder.

What i want to do is

• run VBA macro in Excel 2010 to goto a network location folder,
• open the first file in the folder.
• Unprotect the workbook and worksheets call another marco to run changes
• then protect the worksheet close the file
• and then move onto the next file in the folder until all files have been corrected.

I have created the marco to make the changes, this is called "Edit"

File types are xlsm and the workbook and worksheet are password protected How can i automatically run the macro to goto the network location and in series open each file, unprotect, call the macro, then re protect the document close file and move onto the next file until they are all updated.

Sub Auto_open_change()

Dim WrkBook As Workbook
Dim StrFileName As String
Dim FileLocnStr As String
Dim LAARNmeWrkbk As String

PERNmeWrkbk = ThisWorkbook.Name
StrFileName = "*.xlsx"
FileLocnStr = ThisWorkbook.Path
Workbooks.Open (FileLocnStr & "" & StrFileName)
Workbooks(StrFileName).Activate

With Application.FindFile
SearchSubFolders = False
LookIn = "Network location"
Filename = "*.xlsm"
If .Execute > 0 Then
    Debug.Print "There were " & .FoundFiles.Count & " file(s) found."
    For i = 1 To .FoundFiles.Count
        WrkBook = Workbooks.Open(Filename:=.FoundFiles(i))
        WrkBook.Worksheets(1).Select
        ThisWorkbook.Worksheets(1).Cells(DestinationRange) = WrkBook.Worksheets(1).Cells(SourceRange).Value
    Next i
Else
    Debug.Print "There were no files found."

End If

Im managing to unprotect the file update and reprotect the file fine, just cant get the file from the network location.

Answer 1

I'm using Excel 07, which doesn't allow Application.FindFile, so I can't test this. However, I believe the issue may be that you need to Set the variable Wrkbook, not just assign it.

Change

WrkBook = Workbooks.Open(Filename:=.FoundFiles(i))

to

Set WrkBook = Workbooks.Open(Filename:=.FoundFiles(i))

and let me know how that turns out!