?
Solved

MS Access 2007 Macro no longer works

Posted on 2014-03-24
3
Medium Priority
?
581 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

764 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