Solved

MS Access 2007 Macro no longer works

Posted on 2014-03-24
3
574 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
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 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now