OutputTo Excel error (1 Viewer)

[ClaraBarton]

I want to export the data from a listbox to Excel.
I pretty much followed the Microsoft Help to come up with the following:

    strSql = Me.lstUsage.RowSource
    Set qdfTemp = CurrentDb.CreateQueryDef("", strSql)
    filePath = "D:\Documents\Access\Mine\Finances\ExportedData.xlsx"
    
    DoCmd.OutputTo acOutputQuery, qdfTemp, acFormatXLSX, filePath, True

The error I get is wrong data type for one of the arguments (!?)
The only problem I can see would be the qdfTemp and that debugs to

SELECT TransactionID, fAccountID, fNameID, CkDate, CkNum, FullName, IIf(IsNull([CatMemo]),[Memo],[CatMemo]) AS CMemo, CAmount FROM tblNames RIGHT JOIN (tblTransactions LEFT JOIN (tblCategory RIGHT JOIN tblCheckCat ON tblCategory.CategoryID = tblCheckCat.fCategoryID) ON tblTransactions.TransactionID = tblCheckCat.fTransactionID) ON tblNames.NameID = tblTransactions.fNameID WHERE tblTransactions.TTransID Is Null AND CategoryID = 14 And Year([tblTransactions.CkDate]) = 2025 ORDER BY CkDate DESC

A perfectly fine listbox.
Am I using the querydef wrong?

 

[ClaraBarton]

Figured it out!
Give the Querydef a name as follows:

strSql = Me.lstUsage.RowSource
    Set qdfTemp = CurrentDb.CreateQueryDef("qryTemp", strSql)
    filePath = "D:\Documents\Access\Mine\Finances\ExportedData.xlsx"
    
    DoCmd.OutputTo acOutputQuery, "qryTemp", acFormatXLSX, filePath, True
    MsgBox "Data exported to " & filePath, vbInformation

WORKS!!

 

[Gasman]

Do you understand why though?

 

[ClaraBarton]

Well, yeah. but I wanted to give it no name so I wouldn't have to delete it every time. How would that work?

 

[ClaraBarton]

Well, to be perfectly honest, most things I don't understand. I just go with the flow. Sometimes I get the feeling that the code gets tired of dealing with me and just starts working.

 

[tvanstiphout]

Well, yeah. but I wanted to give it no name so I wouldn't have to delete it every time. How would that work?

You don't have to delete it every time; you can set the SQL property and use it again.

I do this all the time for passthrough queries to SQL Server. I create ptTemporary1 through 10, and round-robin pick one, set the SQL statement (usually a call to a recordset returning stored procedure) and use it as just another readonly query, perhaps for a report or a dropdown list. The queries themselves are never deleted.

 

[The_Doc_Man]

Well, to be perfectly honest, most things I don't understand. I just go with the flow. Sometimes I get the feeling that the code gets tired of dealing with me and just starts working.

My wife feels that way sometimes and calls me in to help her. All I have to do is walk into the room - not even touch anything - and whatever was not working before starts working immediately. Makes me feel like I'm in the middle of one of "Murphy's Laws"... "Things only go wrong when the technician isn't there."

Needless to say, she gets frustrated a second time and asks me if I've been threatening her computer with mayhem if it doesn't behave.

 

[Gasman]

Well, yeah. but I wanted to give it no name so I wouldn't have to delete it every time. How would that work?

Why not just have a query QryExport2XL and modify the sql each time?

 

[theDBguy]

Well, yeah. but I wanted to give it no name so I wouldn't have to delete it every time. How would that work?

One way to export data into Excel without a stored query is to use the CopyFromRecordset method.

 

[Ken Sheridan]

Well, yeah. but I wanted to give it no name so I wouldn't have to delete it every time. How would that work?

You could add the following line to the code:

CurrentDb.QueryDefs.Delete "qryTemp"

But, as Tom has said, it can be handy to have an object you can amend whenever you want a temporary query.

 

[Mark_]

Something you may want to do to keep your hair in place. For the values you use in FilePath, CategoryID, and the year you pull from your CkDate, I'd put them into a field on a form you can edit OR store them in a table.

This is to prevent you needing to go back in to code some time in the future to figure out WHY you can't get 2026's data to pull up. Your future self with send you a thank you card and some cookies when they remember you did this!

 

[ClaraBarton]

upon further research I discovered a function at BTAB Development called Send2Excel. I changed

Send2Excel(frm As Form, Optional strSheetName As String)

to

(strSql As String, Optional strSheetName As String)

and then used my listbox rowsource as Sql and opened the recordset with the Sql.
the listbox has date limiting and the whole works and I'm set to go. Don't need filepath because if I save it it will be from Excel.
Thank you all for your time!