RobertN
New Member
- Joined
- Jan 10, 2020
- Messages
- 27
- Office Version
- 365
- Platform
- Windows
- Web
I am running this scrip (find and replace in multiple workbooks) in Excel 2019 (Office 365) and get the "Microsoft Forms: Could not load some objects because they are not available on this machine" error, but only the first time I run it. after subsequent runs against the same files, it doesn't pop up again. In Debug mode, the message is popping up after " Set wb = Workbooks.Open(wFile)" executes. Everything seems to run fine after dismissing the message. How would I resolve or ignore this message? I need this to be able to run on any machine running Office 365. Thanks so much for your help!
VBA Code:
Sub Unprotect_worksheets()
Dim wb As Workbook, ws As Worksheet
Dim wPath As String, wQuan As Long, n As Long
Dim fso As Object, folder As Object, subfolder As Object, wFile As Object
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.StatusBar = False
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
If .Show <> -1 Then Exit Sub
wPath = .SelectedItems(1)
End With
Set fso = CreateObject("scripting.filesystemobject")
Set folder = fso.getfolder(wPath)
wQuan = folder.Files.Count
n = 1
For Each wFile In folder.Files
Application.StatusBar = "Processing folder : " & folder & ". File : " & n & " of : " & wQuan
If Right(wFile, 4) Like "*xls*" Then
[COLOR=rgb(184, 49, 47)][B][U]Set wb = Workbooks.Open(wFile)[/U][/B][/COLOR]
For Each ws In wb.Sheets
ws.Unprotect "DocAdmin1"
Next
wb.Close True
End If
n = n + 1
Next
For Each subfolder In folder.subfolders
wQuan = subfolder.Files.Count
n = 1
For Each wFile In subfolder.Files
Application.StatusBar = "Processing folder : " & subfolder & ". File : " & n & " of : " & wQuan
If Right(wFile, 4) Like "*xls*" Then
Set wb = Workbooks.Open(wFile)
For Each ws In wb.Sheets
ws.Unprotect "DocAdmin1"
Next
wb.Close True
End If
n = n + 1
Next
Next
Application.ScreenUpdating = True
Application.StatusBar = False
Set fso = Nothing: Set folder = Nothing: Set wb = Nothing
MsgBox "All sheets in all Task Spreadsheets are now unprotected"
End Sub
Last edited by a moderator: