Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Run of object Application Failed (acc 97, xls 97)

    Hello,
    I am using a command button in access that aopens some excel files, updates a new file, closes the opened files, and should then run a macro that is located in the remaining open file. The code executes everything i want in excel but i get an error that 'Method Run of Object Application Failed' . This error occurs at the line that should run the macro. The funny thing is that even with the error coming the macro is still being executed and working fine. How can i stop this error from appearing in access and keep my code working at the same time.

    Heres what I have:

    Private Sub cmdRiskCopy_Click()

    Dim appExcel As Excel.Application

    ' Link to Excel using automation
    Set appExcel = CreateObject("Excel.Application")

    ' So you can watch what's happening...
    appExcel.Visible = True

    'Open a list of files...
    appExcel.Workbooks.Open "C:MetricsSept 2001Issues Past Due 0-6 Months By Division.xls"
    appExcel.Workbooks.Open "C:MetricsSept 2001Quarterly Issues (9-30).xls"

    appExcel.Calculate

    appExcel.Workbooks("Issues Past Due 0-6 Months By Division.xls").Close

    ' Run an Excel Macro
    appExcel.Run "Module1.CopyNum" 'Problem arises here

    End Sub


    How can i stop the error, or can i make the macro auto run somehow with the excel file. For instnace, make the macroexecuted whenever that file is closed. Than i could throw in a close statement for that file
    and it would runt he macro. All the macro does is copy the values form that sheet and paste special them into a new workbook and then closes the original file which has the macro in it. Any suggestions out there???

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run of object Application Failed (acc 97, xls 97)

    Hi Ed

    Are you sure that it is actually that your Access VBA is actually triggering the final Excel routine in the way that you think? Try commenting out the line that you highlighted and see whether the final Excel routine still runs.

    HTH
    Gre

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •