Solved

MS Access 2007 Macro no longer works

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

688 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