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

Question

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?

Andre · Accepted Answer · 2020-10-30 14:40:20Z

2

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

answered Oct 30, 2020 at 14:40

Andre

27.7k7 gold badges43 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related