Troubleshooting Excel VBA performing Word operation

[kilroyscarnival]

Hi,



I've got various older "photo logs" that coworkers made in Excel, all over our shared drive, and many of them are huge because pictures were not compressed. I've been trying to free up drive space by eliminating duplicates and reducing photos, but the ones in "photo logs" are in tables in Word documents.

I have a list of those in Excel (thanks to PowerQuery) and have eliminated any less than a year old. I'd like to run a macro that would open each file based on the path in column A of "this workbook" (current workbook) and compress all pictures, save and close. (Office 365 for Business, V 2508, on a Windows 10 PC)

I've got a few stumbling blocks, so I'll ask them separately.

First, various sources say I must first hit ALT+F11 and check the Microsoft Office 16.0 Object Library before using a macro that references Word from within Excel. But this box was already checked. And I kept getting an error on the line

dim wordApp As Word.Application

So I tried checking also the Microsoft Word 16.0 Object Library, then also tried moving it upward in priority. Should I have to do this? Or did this mess something up?

After I did re-prioritize Word in the Excel VBA, I did get the macro to run, but not to satisfaction. Details to follow.

[kilroyscarnival]

This is the VBA I was trying to get to work. It runs, but I then get as many instances of "Microsoft Excel is waiting for another application to complete an OLE action"

Sub RunWordMacroFromExcel()
    Dim xlWorksheet As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim wordApp As Word.Application
    Dim wordDoc As Word.Document
    Dim filePath As String

    ' Set your worksheet name here
    Set xlWorksheet = ThisWorkbook.Sheets("Sheet1")

    lastRow = xlWorksheet.Cells(xlWorksheet.Rows.Count, "A").End(xlUp).Row

    ' Create a new instance of the Word application
    Set wordApp = New Word.Application
    wordApp.Visible = False ' Set to True for debugging

    ' Loop through each file path in your Excel list
    For i = 1 To lastRow
        filePath = xlWorksheet.Cells(i, "A").Value
        
        ' Check if the file path is not empty
        If filePath <> "" Then
            On Error Resume Next ' In case a file is not found
            
            ' Open the Word document
            Set wordDoc = wordApp.Documents.Open(filePath)

            If Not wordDoc Is Nothing Then
                ' Run the macro stored in Word's Normal.dotm
                wordApp.Run "CompressPhotosWebSaveAndClose"
                
                ' Save and close the document
                wordDoc.Save
                wordDoc.Close
            Else
                ' Handle errors for files that could not be opened
                MsgBox "Could not open file: " & filePath, vbExclamation
            End If
            
            On Error GoTo 0 ' Resume normal error handling
        End If
    Next i

    ' Clean up and close the Word application
    wordApp.Quit
    Set wordDoc = Nothing
    Set wordApp = Nothing
    
    MsgBox "Batch process complete!", vbInformation
End Sub

There's probably a better way to do this, but I haven't worked with VBA between two Office apps before. This is what some searching with AI has suggested. (I know, I know)

[macropod]

The issue you're running into is most likely due to the documents concerned being mailmerge main document. You could insert:
wordApp.DisplayAlerts = wdAlertsNone
before:
wordApp.Visible = False
to allow processing to continue but be aware this breaks the mailmerge data connection, which would have to be re-established before the documents could again be used for a mailmerge.

[kilroyscarnival]

Originally Posted by macropod

The issue you're running into is most likely due to the documents concerned being mailmerge main document. You could insert:
wordApp.DisplayAlerts = wdAlertsNone
before:
wordApp.Visible = False
to allow processing to continue but be aware this breaks the mailmerge data connection, which would have to be re-established before the documents could again be used for a mailmerge.

Hi, Paul, and thanks. I tried this, but I didn't think that would be the case, as these old files are not made with mail merge. Many of them were before I started, and I don't think anyone knew how to mail merge here.

But I gave it a shot. And at first, I got a "waiting for an OLE object in another application" error and I thought nothing was happening. Then, the Compress Pictures dialog popped up. (One of the limitations of this in Word is that there is apparently no good VBA bypass for picking "all pictures in the document" and "web quality" vs "print quality", etc. It did eventually work. Despite the fact that I have to be attendant and click those boxes/radio buttons (some are .doc and some are .docx, depending on how old they are.)

Once again, to my VBA rescue! Thanks again, Paul.