Access: Export only some fields to Excel based on checkbox using VBA-CodePudding

In Access I have a form with a series of checkboxes that correspond to columns in a table and would like to export only the selected columns to Excel with some columns always being exported.

I think the logic should be something like:

CurrentProject.Connection.Execute "SELECT Date, Country, Retailer INTO temp FROM table1 WHERE 1=0"
If optionSKU.Value = True Then
CurrentProject.Connection.Execute "ALTER TABLE temp ADD COLUMN SKU FROM table1"
'repeat for all options
End If
CurrentProject.Connection.Execute "INSERT INTO temp("All relevant data from table1")"
DoCmd.OutputTo acOutputTable, temp, acFormatXLSX, thePath, True

CodePudding user response：

I would just create the SQL, then execute it and export the temp table:

Sql = "Select [Date], Country, Retailer"

If Me!CheckBoxField1.Value Then
    Sql = Sql & ", Field1"
End If
If Me!CheckBoxField2.Value Then
    Sql = Sql & ", Field2"
End If
' etc.
If Me!CheckBoxFieldn.Value Then
    Sql = Sql & ", Fieldn"
End If

Sql = Sql & " Into Temp From Table1"

CurrentProject.Connection.Execute Sql