CODEWITHSUNDEEP
excelvba
Adrian Gornall
Adrian Gornall

Reputation: 327

VBA macro to mass update multiple files in same location

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

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.

Upvotes: 5

Views: 18242

Answers (1)

Farfromunique
Farfromunique

Reputation: 467

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!

Upvotes: 1

Related Questions