How do I call code in Excel from VBA

[JIMinSRQ]

I have a project in which I export a query to excel using the OutputTo method

DoCmd.OutputTo acOutputQuery, stDocName, acFormatXLS, Filename, True

Each time that I do this I create a new spreadsheet/workbook w/ a file name specific to the output. Once in Excel I run a module which formats the spreadsheet. The name of the module function is

Public Function UpdateMatrix(tabname As String)

The function formats the worksheet and renames the tab in which I've just inserted the data.

How can I use my Access module to call the function in Excel and pass it the name that I want to use as the tab?

Note: Using Office XP, the Excel code I want to use is in VBA Project (Personal.xls)

 

[PHV]

Have a look at OLE Automation and the Excel.Application.Run command.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

OOps, I meant the Excel.Application.Run method.

 

[JerryKlmns]

To run code in a module of an excel workbook from access, you have to automate excel ( create an instance of it ) and run the code. But since you are outputting to excel first, I would recommend that, you

a] automate an excel instance
b] open your desired workbook
c] open a recordset against your ready query
d] use the CopyFromRecordset method of the excel Range object e] then run that code you need.
f] Save, close and destroy objects.

 

[JIMinSRQ]

Okay, I tried the following, got no errors, but the function didn't execute. This is after the output object is completed and the workbook is open.

Dim objXL As Object, x
On Error Resume Next
Set objXL = GetObject("Excel.Application")
With objXL.Application
.Visible = True
Excel.Application.Run ("UpdateMatrix")
End With
Set objXL = Nothing

 

[JIMinSRQ]

Stand by, I see why I didn't get and error. Now getting a syntax error.

 

[PHV]

Dim objXL As Object, x
Set objXL = CreateObject("Excel.Application")
With objXL
.Visible = True
.Workbooks.Open Filename
.Application.Run "UpdateMatrix"
End With
Set objXL = Nothing

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JIMinSRQ]

Thanks for the input, there are several issues which result...

1. Using this code opens another, "read only instance" of the workbook.

2. The function "UpdateMatrix" is actually stored in the hidden Personal.xls file. When inserting the path to the Personal.xls in the .run method:

.Application.Run ("C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART\personal.xls!UpdateMatrix")

results in the following error

The Macro '.Application.Run
'C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART\personal.xls!UpdateMatrix' cannot be found.

I even went so far as to save the workbook, copy the function into the workbook, close it then run the following:

Dim objXL As Object, x
Set objXL = CreateObject("Excel.Application")
With objXL
.Visible = True
.Workbooks.Open Filename
.Application.Run ("UpdateMatrix")
End With
Set objXL = Nothing

It opens excel, opens the workbook but still returns the 1004 runtime and the message that the macro cannot be found.

Any idea's are welcome.

 

[JIMinSRQ]

Progress, sort of...

I had to open the workbook, record a macro, "testmacro" in this case. Inserted the code from personal.xls, saved and closed the workbook and finally, I actually call the macro.

Here's the problem. This is a new workbook every I do the output. The code I want to use to format the workbook is stored in a different workbook. So how do I get this to work.

It seems to me that I don't want to use the .run method as it only wants to execute a macro. Since the code is in my personal.xls file and will always want to be there I really want to do a "call" as if I were calling a function w/ in the same project, but it's not the same project. Last, I want the code to execute against the active workbook.

Again, any input/idea's are welcome.

P.S. I'm a bit of a novice at this, but I'm persistant. My code is sloppy but it does what I want.

Thanks all for your help.

 

[ck1999]

Just a thought since you are opening an instance of excel anyway why not copy your excel macro into access and do all the formating using access.

Just copy the macro into access and most likely just reference the objxl. before most of your range calls. Not sure entirely. But it is an idea.

ck1999

 

[JIMinSRQ]

ck1999,

Thanks, I'll give that a shot. In the meantime, I created an xls template and am appending a tab into that, running the macro and calling "save as".