Outputting Access Query to Excel Macro file

[lhouse2]

I have been trying to get a Query from Access to output in Excel, which worked great. But now I have a macro that I want that query to look like when it outputs to Excel. To make things simple, the Excel file name is (Query2.xls) and the Macro name is (Macro2). I tried to follow the code you indicated previously, but only ended up in worse trouble. I know absolutely nothing about VB, but would like to get this to work. The code I have in Access is as follows:

Private Sub Command32_Click()
On Error GoTo Err_Command32_Click

Dim objExcel As New Excel.Application
Dim objWorkbook As Excel.workbook

sourcefilepath = "C:\Documents and Settings\cebner\Desktop\Query2"
Set objWorkbook = objExcel.workbooks.Open(filename:=sourcepath)

objExcel.Run ("Macro2")

objWorkbook.Close
objExcel.Quit

Set objWorkbook = Nothing
Set objExcel = Nothing

End Sub

Please help...I am desperate!!!

 

[opeth49]

Hi
I'm not an expert at this but you could try to replace objExcel.Run ("Macro2") by objExcel.ExecuteLine "Macro2

 

[opeth49]

Actually, forget what I said, it's probably not right. You should refer to this thread:

http://www.tek-tips.com/viewthread.cfm?qid=956261&page=14