Solved

Access not opening Excel macro-enabled workbook

Posted on 2014-09-19
5
1,212 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 50

Expert Comment

by:Gustav Brock
ID: 40333555
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

733 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