Run an Excel macro after exporting an Access report

[Blake Rice]

Hi everyone,

Simple question, I would like a button in my Access application to export a report to excel, then run an excel macro that is stored in PERSONAL.XLS so that it is seemless. Here is the feble attempt that I have made thus far.

Private Sub cmdRunXL_Click()
On Error GoTo Err_cmdRunXL_Click
    Dim XL As Excel.Application
    
    DoCmd.OutputTo acOutputReport, "qryBackLogWithActuals", acFormatXLS, "TEST_1.xls", True
    
    XL.Run "PERSONAL.XLS!BacklogFix"

Exit_cmdRunXL_Click:
    Exit Sub

Err_cmdRunXL_Click:
    MsgBox Err.Description
    Resume Exit_cmdRunXL_Click
    
End Sub

Thanks for your help

[NeoPa]

Not such a feeble attempt, though telling us exactly what happened when you tried to run it would be more helpful.

I suggest you start by checking out Application Automation. You'll see you need XL to be instatiated before you use it. You may also find a full reference to PERSONAL.XLS to be necessary.

[Blake Rice]

Hi Neo Pa,

Thanks for the reply.

After reading the link, here is my second attempt at the code.

Private Const conAppNotRunning As Long = 429 

...

Private Sub cmdRunXL_Click() 
On Error GoTo Err_cmdRunXL_Click
    Dim objExcel As Excel.Application 
  
    On Error Resume Next 
        Set objExcel = GetObject(, "Excel.Application") 
    If Err = conAppNotRunning Then Set objExcel = New Excel.Application 
  
    With objExcel 
        DoCmd.OutputTo acOutputReport, "qryBackLogWithActuals", acFormatXLS, "Backlog With Actuals_1.xls"
        .Run "PERSONAL.XLS!BacklogFix"
        .Visible = True
    End With

Exit_cmdRunXL_Click:
    Exit Sub

Err_cmdRunXL_Click:
    MsgBox Err.Description
    Resume Exit_cmdRunXL_Click
End Sub

I have steped through the entire sub and everything runs without error. However, the lines 15. and 16. don't seem to do anything. The XL application doesn't remain open, it has been exported but I have to go into the folder and open it and when I do open it the macro hasn't run. I am not sure why these lines aren't having any effect.

I have also thought about what I would like this sub to do and I am thinking that I would need to "import" the XL macro into access and have it run from access instead of looking in PERSONAL.XLS to find it. That way any user could run the report and it would look the same ... just some thoughts.

Thanks again for your help.

[NeoPa]

Let's start with the crux of the matter as I see it (although I should have picked up on this after the first post too), which is that your XLS file doesn't seem to be opened before you attempt to run the BacklogFix routine. I would guess that is necessary. Otherwise, not a bad attempt at getting this to work.

Just while debugging, I'd suggest putting a Stop command within your BacklogFix routine. Not only does this tell you definitively whether or not it's run, but it also enables you to step through the code should you need to.

I was curious to see you have a different way of instantiating a new Excel application (as opposed to taking control of an already open instance). I don't know if this works, but there is example code in the linked article to use if it doesn't.

I like your method of checking Err to determine if GetObject() worked.

[NeoPa]

Blake Rice:
I have also thought about what I would like this sub to do and I am thinking that I would need to "import" the XL macro into access and have it run from access instead of looking in PERSONAL.XLS to find it. That way any user could run the report and it would look the same ... just some thoughts.

If this turns out to be a discussion we'll move it to a separate thread, but just as a single comment on a subject closely related to the main thread itself, I would say this may be entirely appropriate. If the procedure you run is specifically, and only, for files created from this database then the code would sit much more naturally within the database itself and not in any Excel file available to all worksheets.

If you plan to progress with this then pay special attention to the second post in that same linked article as it pertains to some of the difficulties involved with porting code developed in the native application.

[Blake Rice]

Thanks for the reply. I was wondering that same thing with regards to taking control of an already open XL object. My first attempt to involved the code:

DoCmd.OutputTo acOutputReport, "qryBackLogWithActuals", acFormatXLS, "TEST_1.xls", True

Where the [autostart] argument of the .OutputTo method was flagged as true. When that line of code finished running I was left with an open XL workbook with the unformatted report open. I have no idea how I can reference that open workbook from my code. Since I wasn't the one that created it I don’t have a variable for it. I would need a pointer to that open workbook but I don’t know what it would be called or how to reference it.

As for the break, when I did that every line stepped through without incident, however the code wasn't producing the expected result. When I added line 2

    With objExcel  
        .Workbooks.Add 
        DoCmd.OutputTo acOutputReport, "qryBackLogWithActuals", acFormatXLS, "Backlog With Actuals_1.xls" 
        .Run "PERSONAL.XLS!BacklogFix" 
        .Visible = True 
    End With

To the, With, block of code, again everything ran without error, but this time I was left with an open, blank workbook with one sheet. This tells me that I am not referencing the .OutputTo spread sheet properly.

I think that I will be moving the XL macro into the access environment, because it only needs to be run on reports that are being exported from this database. I will attempt that once I can get this block of code working properly though.

Again, thanks for your help.

[NeoPa]

I didn't realise OutputTo() provided that facility. That should make life a little easier.

If you run :

Set objExcel = GetObject(, "Excel.Application")

after the OutputTo() line then it should pick up application left open and return a pointer to that application object to you. Workbooks(SpreadsheetName) should give you a pointer to the workbook object (your Excel file).

[Blake Rice]

Hey NeoPa, that seemed to do the trick, I am still fiddling around with the entire script so I wont post it here just yet, but running the Set objExcel command after the OutputTo() command resulted in the Excel macro running on the open Excel application. I wasn't able to get the Workbooks(SpreadsheetName) code to work, but the Excel macro ran just the same without it.

Like I said I will come back to this post and post the code that I am using, after I have it tided up a bit.

Again, thank you very much for your help with these questions. As always, you responses are much appreciated.

Blake

[NeoPa]

Sorry Blake, I was just speaking naturally there. For the code to run from within your Access code it would need to be objExcel.Workbo oks(Spreadsheet Name) (See the explanation in the linked article).

Have fun :-)