Access not opening Excel macro-enabled workbook

Although I think this is an Access question, I have included Excel just in case I am missing something.

I have an Access module that is supposted to open an autoexec macro-enabled Excel workbook.  The workbook when opened via Excel does autostart and product the file expected.  In Access I have also set a reference to the Excel library.

Below is the Access module that I have to process the Excel.xlsm.  I can't determine why it isn't working or what might be missing to make it work.

Function GetData()
    Dim dbs As DAO.Database
    Dim strFileName
    Dim fPath
    Dim ExcelObj As Object 'Excel.Application
    Dim objWB As Object 'Excel.Workbook
    fPath = "C:\Links\"
   
    Set dbs = CurrentDb
   
    strFileName = fPath & "ContactMacro.xlsm"
   
    On Error Resume Next
    Set ExcelObj = GetObject(, "Excel.Application")

    If ExcelObj Is Nothing Then
        Set ExcelObj = CreateObject("Excel.Application")
        ExcelObj.EnableEvents = True
    End If
   
    ExcelObj.Workbooks.Open strFileName
    Set ExcelObj = Nothing

End Function
uadAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieVBA ExpertCommented:
How isn't it working?

What happens it you remove On Error Resume Next and run the code?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
uadAuthor Commented:
The Excel macro opens a livelink and saves it as an Excel workbook.

When Access is supposed to opens the Excel macro-enabled workbook - it doesn't present any error (stepped through the code) but also does not create the file called by the Excel macro.

removed the On Error Resume Next as suggested.  Once that was done, the macro-enabled Excel code processed the file.  

Do I dare ask what the problem with "On Error Resume Next" would have for future reference.
0
NorieVBA ExpertCommented:
To be honest I don't know.

The reason I suggested removing it was to see if any errors were being thrown up.

If there had of been then it might have shed some light on what the problem was.

By the way, what does the autoexec code in the Excel workbook actually do?

Have you considered moving that code to your Access VBA?
0
uadAuthor Commented:
The autoexec code in Excel opens a livelink document and saves it as an excel workbook - so that the data can be imported into an Access table.  Initially I was hoping to include the whole process (opening the live link and transferring the data into an Access table) in Access but was presented with the error "database does not support HTML pages " (this issue was posted yesterday and the solution was to create via Excel and import to Access)
0
Gustav BrockCIOCommented:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.