Calling a macro from an xla in Excel

I am using the following code to export from an access Form the results of a query and I want excel to then call a macro residing in an xla which will run some formatting on the spreadsheet...the last bit of the code does not fire off the macro...can an expert suggest reasons why. I have set the object library in References....I am using v2000

Private Sub cmdSendqryOrders_Click()
Dim xl as Excel.Application
DoCmd.OutputTo acOutputQuery, "qryorders", acFormatXLS, "C:\Company\Data\qryorders.xls", True
xl.Run "MyMacros.xla!OrdersFormatting"
End Sub
LVL 1
PeterWhittsAsked:
Who is Participating?
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.

BembiCEOCommented:
I assume, the Excel file is open, right?
0
PeterWhittsAuthor Commented:
yes but the xla is hidden just like the personalxls
0
BembiCEOCommented:
So, in general, I would also expect, that your macro should run, from the logical side.
I asume, that yoiu can run it locally from the Excel file itself.

You may take into account, that vb is using all available processor resources as needed. So dependend on the way, the excel file is opened, you may run into a timing issue.

All XLAs are loaded after the startup of Excel. The availability of the macros maybe therefore a little bit delayed. You transfer data into the file which may take resoources. So you have to make sure, that all XLAs are completely loaded before you try to call into it.

Just try, if the behaviour changes, if you paly around with DoEvents, which allows the background application to handle events.  From my logical point, I would call DoEvents, before your try to run the macro. So

DoEvents
xl.Run "MyMacros.xla!OrdersFormatting"

You may also to try DoEvents before transfering data.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PeterWhittsAuthor Commented:
Sorry the above does not seem to work.......as a test of the Access code, how do I write "Test" into A1 of the opened workbook from my Access code?....just to see if the two are talking to each other.
0
BembiCEOCommented:
xl.range("A1").value = "Test"
This refers to the currently active worksheet.
0
PeterWhittsAuthor Commented:
Sorry for the delay....I get when running the code Run Time Error 91
Object Variable or With Block variable not set

The spreadsheet is created but the rte pops up in Access.

Have you tested this code yourself and does it work?
I have limited my code to a small test:
Private Sub cmdSendqryOrders_Click()
Dim xl As Excel.Application
DoCmd.OutputTo acOutputQuery, "query2", acFormatXLS, "C:\temp\Data\query2.xls", True
DoEvents
xl.Range("A1").Value = "Test"
End Sub
0
PeterWhittsAuthor Commented:
Would appreciate some form of closing answer to the above problem from an expert even if it is only on the last part which is why I am getting a rte when trying to post data into a single cell.
0
Wayne Taylor (webtubbs)Commented:
You are not creating an Excel.Application object. You need to use the "New" keyword in the declaration....

    Dim xl As New Excel.Application

Wayne
0
Rory ArchibaldCommented:
Your problem is that you are outputting the query to an Excel application, but then you are not referring to that application instance. You could use:
 
Private Sub cmdSendqryOrders_Click()
Dim xl as Excel.Application
DoCmd.OutputTo acOutputQuery, "qryorders", acFormatXLS, "C:\Company\Data\qryorders.xls", True
set xl = GetObject(,"Excel.Application")
xl.Run "MyMacros.xla!OrdersFormatting"
End Sub
 
if you know for sure that there will only be one instance of Excel running. My preference would be to output the file but not start Excel as part of the DoCmd.OutputTo command and then automate Excel, open the workbook, then run your code.
Regards,
Rory
0
PeterWhittsAuthor Commented:
Hi Wayne

I now get a rte 1004 Method Range of Object Application failed when the spreadsheet is open but the error box takes me back to looking at my Access form
0
PeterWhittsAuthor Commented:
Hi Rory

The chances are that there will be an open Excel applcation already so how would I code for using the instance already open as if I tried to open a second version of Excel would I not get a rte saying that the xla's and personal.xls were already in use?
0
Rory ArchibaldCommented:
Actually, no because the add-ins and macro workbook would not be opened when automating Excel (xlas are multi-use anyway)
I would do something like this - you need to replace the 'add in name here' bit with the name of the add-in as it appears in the addins dialog, not the filename:
 

Private Sub cmdSendqryOrders_Click()
   Dim xl As Excel.Application
   Dim strFileName As String
   strFileName = "C:\Company\Data\qryorders.xls"
   DoCmd.OutputTo acOutputQuery, "qryorders", acFormatXLS, strFileName, False
   On Error Resume Next
   Set xl = GetObject(, "Excel.Application")
   On Error GoTo 0
   If xl Is Nothing Then Set xl = CreateObject("Excel.Application")
   xl.Workbooks.Open strFileName
   With xl.AddIns("add in name here")
      If Not .Installed Then .Installed = True
   End With
   xl.Run "MyMacros.xla!OrdersFormatting"
End Sub

Open in new window

0
PeterWhittsAuthor Commented:
Hi Rory
When I run the last code it works fine and the macro is run but Excel is not open (I would really like to see the excel data after the macro has run). If I then run the code again I get a rte 2302 Microsoft Access cannot save the output data to the file you've selected and the debug stops on the
  DoCmd.OutputTo acOutputQuery, "qryorders", acFormatXLS, strFileName, False line
If I try and delete the file from the Data folder it says that the file is in use by Microsoft Excel.
If I double click the excel file in the Data folder it flickers like it opened but does not open.

Any suggestions please...do I need a close and nothing or a check to see if it exists and if it does a kill?
Peter
0
Rory ArchibaldCommented:
Try this version (you'll need to kill any existing Excel processes first):
Private Sub cmdSendqryOrders_Click()
   Dim xl As Excel.Application
   Dim strFileName As String
   strFileName = "C:\Company\Data\qryorders.xls"
   DoCmd.OutputTo acOutputQuery, "qryorders", acFormatXLS, strFileName, False
   On Error Resume Next
   Set xl = GetObject(, "Excel.Application")
   On Error GoTo 0
   If xl Is Nothing Then
      Set xl = CreateObject("Excel.Application")
      xl.Visible = True
   End If
   xl.Workbooks.Open strFileName
   With xl.AddIns("add in name here")
      If Not .Installed Then .Installed = True
   End With
   xl.Run "MyMacros.xla!OrdersFormatting"
End Sub

Open in new window

0
PeterWhittsAuthor Commented:
Have tested the above code but I now get  a rte 1004 MyMacros.xla could not be found.  The Tools/AddIns in Excel has the MyMacros ticked but in vba there is no sign of it?
0
PeterWhittsAuthor Commented:
In the vba, even the Personal.xls is missing....the only project in there is the created file?
0
Rory ArchibaldCommented:
Yes, that's normal - as I said, automated Excel has none of its startup files loaded.
Try this version (toggling the installed property should force it to be loaded):

Private Sub cmdSendqryOrders_Click()
   Dim xl As Excel.Application
   Dim strFileName As String
   strFileName = "C:\Company\Data\qryorders.xls"
   DoCmd.OutputTo acOutputQuery, "qryorders", acFormatXLS, strFileName, False
   On Error Resume Next
   Set xl = GetObject(, "Excel.Application")
   On Error GoTo 0
   If xl Is Nothing Then
      Set xl = CreateObject("Excel.Application")
      xl.Visible = True
   End If
   xl.Workbooks.Open strFileName
   With xl.AddIns("add in name here")
      .Installed = False
      .Installed = True
   End With
   xl.Run "MyMacros.xla!OrdersFormatting"
End Sub

Open in new window

0
PeterWhittsAuthor Commented:
That seems to work except if there is an instance of Excel open I just get the eggtimmer in Access and then it takes two clicks on the task bar "qryorders" to change the focus to Excel....almost there...thanks for the help so far really appreciated!!  :-)
0
Rory ArchibaldCommented:
OK, let's try this one! :)

Private Sub cmdSendqryOrders_Click()
   Dim xl As Excel.Application
   Dim blnAlreadyOpen As Boolean
   Dim strFileName As String
   strFileName = "C:\Company\Data\qryorders.xls"
   blnAlreadyOpen = False
   DoCmd.OutputTo acOutputQuery, "qryorders", acFormatXLS, strFileName, False
   On Error Resume Next
   ' grab Excel if open
   Set xl = GetObject(, "Excel.Application")
   On Error GoTo 0
   If xl Is Nothing Then
      ' Excel wasn't open so start it
      Set xl = CreateObject("Excel.Application")
      ' have to make it visible
      xl.Visible = True
   Else
      ' Excel was already open
      blnAlreadyOpen = True
   End If
   ' open workbook
   xl.Workbooks.Open strFileName
   With xl.AddIns("add in name here")
      If blnAlreadyOpen Then
         ' install if not already installed
         If Not .Installed Then .Installed = True
      Else
         ' if Excel wasn't already open,
         ' we need to toggle the Installed property
         ' to actually load the add-in
         .Installed = False
         .Installed = True
      End If
   End With
   ' run macro
   xl.Run "MyMacros.xla!OrdersFormatting"
End Sub

Open in new window

0
PeterWhittsAuthor Commented:
Sorry the focus is still on Access but the task bar is now only one click.
0
Rory ArchibaldCommented:
What happens if you add this line before the xl.Run line:
AppActivate "Microsoft Excel"
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
PeterWhittsAuthor Commented:
Wonderful.....thank you so much..wish I could double your points!!
Thanks Rory
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.