Solved

Access not opening Excel macro-enabled workbook

Posted on 2014-09-19
5
1,156 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
  • 2
  • 2
5 Comments
 
LVL 33

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 33

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 49

Expert Comment

by:Gustav Brock
ID: 40333555
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

773 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