query to automatically export to 2nd sheet tab in Excel

[tbauer]

I have a query that I'd like to be able to export to the 2nd sheet tab in an Excel spreadsheet from a Query that is filtered using a combo box on a form. Is there any way to automate this process as I have approx. 20 exports to Excel to create manually.

 

[boblarson]

Copy the code found here
http://www.btabdevelopment.com/ts/tq2xlspecwspath
(on my website) to a new standard module (not form, report, or class module) and name the module basExport.

Then you can just call the function like this:

SendTQ2XLWbSheet "QueryNameHereInQuotes", "SheetNameHereInQuotes", "FilePathAndFileNameAndExtensionHere"

So if you had a query named qryMyQuery and the sheet is "Sheet2" and the file name is C:\Temp\Something.xls you would use

SendTQ2XLWbSheet "qryMyQuery", "Sheet2", "C:\Temp\Something.xls"

 

[tbauer]

Thanks for the reply!

So I set the reference to Microsoft 3.6 Object Library since I use Access 2007, but export to an Excel 97-2003 named Variance Analysis.

Also, for every occurance of TQ in the code; do I substitute the query name "Account Category qry" without quotes & Sheet name and file path. The excel spreadsheet tab name to replace the data in is Account_Category_Query

 

[boblarson]

Thanks for the reply!

So I set the reference to Microsoft 3.6 Object Library since I use Access 2007, but export to an Excel 97-2003 named Variance Analysis.

Also, for every occurance of TQ in the code; do I substitute the query name "Account Category qry" without quotes & Sheet name and file path. The excel spreadsheet tab name to replace the data in is Account_Category_Query

The code I gave you from the web page is something that is GENERIC. And it is meant to be Generic. You do not change anything in it. You only pass information TO it in the form of parameters like I showed in the post I gave. So from the button that kicks it off, or whatever event, you would just call the function:

SendTQ2XLWbSheet "Account Category qry", "Put The Sheet Name Here", "Put the Path and File Name to the existing workbook here"

So, if you want it on Sheet 2 and there is a sheet named SHEET2 in the workbook

SendTQ2XLWbSheet "Account Category qry", "Sheet2", "Put the Path and File Name to the existing workbook here"

 

[tbauer]

Thank you, I'll give this a try!

 

[tbauer]

I'm sorry that I'm unable to put all of this together, but you mentioned

"Then you can just call the function like this:

SendTQ2XLWbSheet "QueryNameHereInQuotes", "SheetNameHereInQuotes", "FilePathAndFileNameAndExtensionHere"

So if you had a query named qryMyQuery and the sheet is "Sheet2" and the file name is C:\Temp\Something.xls you would use

SendTQ2XLWbSheet "Account Category qry", "Account_Category_qry", "C:\Documents and Settings\tonyab\My Documents\Accesshelp\Variance Analysis.xls"

So I believe by call function you mean to create a macro to run the code, but do you create the macro in Access where the query is or Excel where the spreadsheet is.

 

[boblarson]

I was actually talking about a VBA procedure but you could use a macro. It would be from Access and if you are using a macro you would select the Action of RunCode and the function name would be SendTQ2XLWbSheet and then the Arguments would be:

"Account Category qry", "Account_Category_qry", "C:\Documents and Settings\tonyab\My Documents\Accesshelp\Variance Analysis.xls"

 

[tbauer]

I attached what I attempted to do in the access macro and I receive error code 2950 and it relates to having quotes in the wrong places and commas in the wrong places.

 

[boblarson]

I attached what I attempted to do in the access macro and I receive error code 2950 and it relates to having quotes in the wrong places and commas in the wrong places.

Attached? Where? You would need to run Compact and Repair first and then Zip the file by right-clicking on the file and selecting SEND TO > COMPRESSED FOLDER. Then you should be able to upload it.

 

[tbauer]

I think it should be attached now, it was a screen print I placed into word 2007.

 

[boblarson]

Umm, you put the arguments in the Function Name spot. You ALSO need to put the function name in the function name spot WITH the Arguments just like this:

SendTQ2XLWbSheet("Account Category qry", "Account_Category_qry", "C:\Documents and Settings\tonyab\My Documents\Accesshelp\Variance Analysis.xls")

 

[tbauer]

When I run the macro I'm given the error #2004 that the "Select metho of Range class failed"

 

[boblarson]

Okay, do this - in the function I gave you, go to it and comment out the part that says On Error GoTo by putting an apostrophe next to it like:

' On Error GoTo err_handler

and then run it and see what line it stops on. But BEFORE YOU DO THAT, right click on your task bar and bring up Task Manager and check in the PROCESSES tab to see if there are any EXCEL.EXE listed and if so, right click on them and select END PROCESS.

 

[tbauer]

Dim strPath As String
Const xlCenter As Long = -4108
Const xlBottom As Long = -4107

On Error ' On Error GoTo err_handler


strPath = strFilePath



(I'm now given Compile error: Syntax error)

 

[boblarson]

Dim strPath As String
Const xlCenter As Long = -4108
Const xlBottom As Long = -4107

On Error ' On Error GoTo err_handler


strPath = strFilePath



(I'm now given Compile error: Syntax error)

It should not say
On Error ' On Error GoTo err_handler

It should say
' On Error GoTo err_handler

 

[tbauer]

Attached is word document showing a problem with the code I'm having now that I'm hoping you'd still be willing to help me with.

 

[boblarson]

Do you have the form open which provides the value to the query when you run this code?

 

[tbauer]

I opened the form and typed in the property # under the owner field and pressed on the macro and it came back to end or debug

set rst = CurrentDb.OpenRecordset(strTQName)

 

[boblarson]

well, is there any way you can post a copy of your database (with bogus data) and a copy of the spreadsheet?

 

[tbauer]

Sounds like a project for Monday. Enjoy your weekend and thanks for all of your help so far.