[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

MS Access 2007 Macro no longer works

Posted on 2014-03-24
3
Medium Priority
?
583 Views
Last Modified: 2014-03-24
I had a problem some time ago with a macro, which I reported here:

http://www.experts-exchange.com/Database/MS_Access/Q_28261009.html

The solution worked for a while, but my system stopped working and I had to reinstall Windows 8.1 and all applications, and now the macro does not work.

As before, I have confirmed that the reference to the Microsoft Excel 15.0 Object Library is checked.

Option Compare Database

Function ExportToIMM()
    Dim filepath As String, xlapp As Object, xldoc As Object
     
    filepath = "C:\Users\Lev\Desktop\ATR.xls"
    filepath2 = "C:\Users\Lev\Desktop\ATR.txt"
    DoCmd.OutputTo acOutputTable, "RecentlySentAnswers", acFormatXLS, filepath
     
    Set xlapp = CreateObject("Excel.Application")
    Set xldoc = xlapp.Workbooks.Open(filepath)
    xlapp.DisplayAlerts = True
    xldoc.SaveAs filepath2, xlCSV
     
    xldoc.Close False
    xlapp.Quit
    deedCSV = 1
End Function

Open in new window


The error is
Run-time error '429':
ActiveX component can't create object

I do not know what to do now to get this to work.

At the same time, when I open the project (ADP file) I am told that it is in read-only mode, even though the folder e:\my documents has been listed as a "trusted zone". I saved the file with a different name (same folder), and am now working with that, but perhaps this is related.

Thank you.
0
Comment
Question by:Lev Seltzer
[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
3 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39949938
Is the excel file open when you are running the macro?
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 39949952
I'm not sure why you have a reference to Excel checked, You're using Late Binding, which does NOT require an explicit reference (except to include your constants, like "xlCSV", which can easily be fixed). Try removing the reference and then compile your application (in the VBA Editor click debug - compile). You'll find any constant errors there, and you'll have to resolve them.

Once you've done that, the app will use whatever version of Excel is installed on the machine, so you don't have to worry about that.

If that doesn't work, then you may have a faulty installation of Office. Try running the installation again, or removing Office and reinstalling.
0
 

Author Closing Comment

by:Lev Seltzer
ID: 39950084
I ran detect and repair for Office 2007 (for Access) and Office 2013 (for Excel) and without any further modification the macro works perfectly.
I do not know what was wrong but thank you for the tip, as it was all I needed to get things working again.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

649 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