Export A Form's Query to Excel

[Bob Valentine]

Group:

I would like to add the capablilty to my application to export a form's
underlying query to an Excel spreadsheet while the form and query are open.
Is this possible? If so, what would the VBA statement be to accomplish this?

Thanks,
Bob V

 

[Jeanette Cunningham]

Bob,
assuming this form is not for data entry or editing.
Assuming the form's underlying query is a saved query, you can use Transfer
spreadsheet.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"QueryName", "FullPathAndFileName"

Replace QueryName and FullPathAndFileName with your names.
Vba help explains it pretty well.


Jeanette Cunningham

 

[Bob Valentine]

Jeannette:

Thank you for your help. The open form is for data entry into the query, and
the underlying query is one that is saved. Does this cause a problem?

Thanks,
BobV

 

[Klatuu]

No, it will not be a problem if the query is a stored query. It really
doesn't matter whether the form is open or not.

 

[Jeanette Cunningham]

Bob,
As the form is used for data entry,
I would include some code to save any pending edits before you export the
query.
Like:

If Me.Dirty = True Then
Me.Dirty = False
End If
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"QueryName", "FullPathAndFileName"

Jeanette Cunningham

 

[Klatuu]

Why would that be necessary, Jeanette?
It is a different instance of the query. The query will be pulling from the
table, not the form or the form's recordset.
Are you speculating or am I missing something?

 

[Jeanette Cunningham]

Klatuu,
My thinking is like this:
The question stated that the export was to be done from a form that sounded
as if it was used for data entry.
I assumed that if edits or a new record was added, there was an expectation
that the latest change would show up in the spreadsheet.
If changes are made on the form but not written to disk, they won't be saved
in the table at the time the export runs.

Jeanette Cunningham

 

[Klatuu]

Right! I was missing something. You are correct. Thanks.

 

[Bob Valentine]

Thanks to all of you who have responded to my question. I now know what to
do.

BobV