- Jan 10, 2020
- Office Version
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!
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: