Opening an excel workbook in a powerpoint macro (ERROR 1004)

Question

I switched from office 2010 to office 2016 and I noticed the below VBA now gives an error 1004 (Method 'Open' of object 'Workbooks' failed). I have confirmed this code works as intended in powerpoint 2010 but does not work in powerpoint 2016. I was hoping someone could help me determine the cause.

Sub testing()
Excel.Application.Workbooks.Open FileName:="PATH\FILENAME"
End Sub

I have updated the Microsoft excel library reference to version 16.0 and I have tried some of the other ways to run the same command to no avail. I'm convinced it has something to do with the library references.

Thanks in advance for any suggestions you have.

Does it behave any differently when an instance of Excel is open? — jsheeran
It gives me the same error. Even if I open the designated file under FileName is still gives me the same error. — moo5003
I just tried by starting PowerPoint 2016, creating a new PowerPoint file, opening the VBE, adding a reference to the Microsoft Excel 16.0 Object Library (will you be using recommended late binding technique after completing debugging?) and then adding your code with a path to a known Excel file and it worked. This is my MSO version : 2016, 32 bit, 365, 16.0.7167.2060 / 16.0.7127.1026 (no idea why the versions are different in the backstage and About UIs) — Jamie Garroch - MVP
Have you tried starting Excel manually and opening the file as you would any other file? Recent versions of Office "protect" us from various evils like files that came from (GASP) the internet. If you can open the file normally (ie, via File | Open and browse to the file) then you've ruled out MSNanny as the culprit. If the file won't open normally, rightclick it, choose Properties, see if there's an UNBLOCK button at bottom of General tab. If so, click it and then try again (via File | Open and then via your code) — Steve Rindsberg
Thanks for the information Jamie. Apparently my error is then due to something with the excel file that I'm trying to reference. I just removed all the macros from the referenced excel file and it opened as expected. Maybe something in the file is corrupted... I'll let you know as I play around with it more. Also I'm not informed regarding late binding, so my guess is I will not use it. — moo5003

bdpolinsky bdpolinsky · Accepted Answer · 2016-09-14T15:18:51

Try

Sub testing()

on error goto errhandler
Excel.Application.Workbooks.Open FileName:="PATH\FILENAME"
End Sub

errhandler:
if err.number = 1004
    msgbox("Error Description: " & err.description)
end if

and post what it says

Opening an excel workbook in a powerpoint macro (ERROR 1004)

1 Answers