MS Access 2007 Macro no longer works

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.
Lev SeltzerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
Is the excel file open when you are running the macro?
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lev SeltzerAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.