?
Solved

Access not opening Excel macro-enabled workbook

Posted on 2014-09-19
5
Medium Priority
?
1,334 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 2000 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 51

Expert Comment

by:Gustav Brock
ID: 40333555
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

752 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