CODEWITHSUNDEEP
vbams-access
babno
babno

Reputation: 309

how to export a custom query to excel in access/vba

I need to select different fields based on options selected in a form by a user. As a simplified example of what I'm trying to do.

dim selection, sSql as string
If Forms![frmExport]![option1].Value = "Birthday" Then
selection = "bday"

ElseIf Forms![frmExport]![option1].Value = "Ages" Then
selection = "age"

ElseIf Forms![frmExport]![option1].Value = "Name" Then
selection = "name"

Else
selection = "*"
End If

sSql = "Select users." & selection & " from users"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, sSql, sPath & "user_info", True

But I get an error 7871 because while I can pass pre-defined queries to TransferSpreadsheet I apparently can't pass it one in the form of a string. Is there a way to make this happen with making the ~50 permutations of possible queries and having a horrible giant nesting of ifs?

Upvotes: 1

Views: 7522

Answers (1)

Andre
Andre

Reputation: 27634

You can make the export query dynamic by setting its .Sql property:

Dim qd AS DAO.QueryDef

Set qd = CurrentDb.QueryDefs("myExportQuery")
qd.Sql = sSql

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, qd.Name, sPath & "user_info", True

Upvotes: 2

Related Questions