VBA - Export to Excel (1 Viewer)

[Summer123]

Hello,
i have bunch of checkboxes that someone can click and export to excel, however it exports to different tabs. how do i make them export to one tab but on different rows? so for example if check box1, 2 and 3 are clicked and only 1 and 3 have values then current it exports checkbox 1 value to one tab and checkbox 3 value on another tab, how do i adjust it so that both checkbox 1 and 3 values are exported in one tab but right underneath one and another (so on diffrent rows, the fields on each query is the same, its just evaluation different values on different fields)?

 

[Trevor G]

What about when the checkbox are clicked the data goes into a temp table as an append query then you transfer from the temp table and also empty the temp table for the next time. Minimal code then is needed to transfer the temp table.

 

[Summer123]

let me try that! thanks for the idea.

 

[Summer123]

so how do i run a query that is checked off? do i have to name each one? i was thinking to do docmd.openquery but do i have to write out each one? i assume you mean something like below right?

For Each ctl In MyForm.Controls
Select Case ctl.ControlType
Case acCheckBox
If ctl = True Then
Set rst = db.OpenRecordset(ctl.Name)
If Not rst.EOF Then
run append query to temptable?? but how do i do this?

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "temptable", fileIn, True, ""

End If
rst.Close
Set rst = Nothing
End If
End Select
Next ctl

 

[Trevor G]

Yes use the docmd.openquery, you would need to set the warnings to off so you don't get the prompts.

 

[Summer123]

so do i need to write docmd.openquery for all 50+ append queries that i have??

or is there another way?

 

[Summer123]

can append queries be run withctl.name though? i think that only opens select queries correct? if so do i now need to do 50 if/else statements to run append queries... can someone please help? PLEASE

 

[Summer123]

can ayone please help? Basically i want to check of the queries i want to run and export it out.. the code i have above only work on select queries and not append queries.. i have append queries that stores the results in a table and i want to then export the table... so really check off the queries i want to run ... which appends to a table and then export that table.. how can i do that with simple code instead of if/else statement ... i have about 50+append queries that i want to run at once. please help.

 

[Summer123]

nevermind i got it to work after researching further i had to use db.execute and not db.openrecordset....
thanks again for your help!
Summer