Solved

Access not opening Excel macro-enabled workbook

Posted on 2014-09-19
5
1,270 Views
Last Modified: 2014-09-19
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
0
Comment
Question by:uad
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 34

Accepted Solution

by:
Norie earned 500 total points
ID: 40333347
How isn't it working?

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

Author Comment

by:uad
ID: 40333451
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
 
LVL 34

Expert Comment

by:Norie
ID: 40333476
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
 

Author Comment

by:uad
ID: 40333542
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
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40333555
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question