Use Form and VBA to Send Query Results to Excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ryguy7272 - 28 May 2008 20:23 GMT

I thought a simple sub, such as the following, which I found on this DG,
would allow me to copy/paste the results of a Query to an Excel SS:

Private Sub Command1_Click()
Dim stDocName As String
stDocName = "qryCurrentQ"
'This code does not work
DoCmd.OutputTo acOutputReport, stDocName, acFormatXLS, "c:\Current.xls"
End Sub

All this does is delete the Worksheet (Current.xls) that resides on the C:
drive and...that's it. How can I send the results of a Query to an Excel SS?

Regards,
Ryan---

Signature

RyGuy

Reply to this Message

Klatuu - 28 May 2008 21:16 GMT

Use the TransferSpreadsheet method instead.
See VBA Help for details.

Signature

Dave Hargis, Microsoft Access MVP

> I thought a simple sub, such as the following, which I found on this DG,
> would allow me to copy/paste the results of a Query to an Excel SS:
[quoted text clipped - 11 lines]
> Regards,
> Ryan---

Reply to this Message

ryguy7272 - 28 May 2008 22:01 GMT

You are totally right! I forgot about this little trick. I created the
Macro, saved it to a Module, and called that Function with a Command1_Click
button. Thanks for the reminder about this technique. The old code is gone
and the new code is running smoothly...

Regards,
Ryan---

Signature

RyGuy

> Use the TransferSpreadsheet method instead.
> See VBA Help for details.
[quoted text clipped - 14 lines]
> > Regards,
> > Ryan---

Reply to this Message