Export SQL




I am trying to find a way to export the SQL from a query into Excel. I am
exporting the results and sending this via e-mail but I also want to add a
hidden sheet to contain the SQL so if I ever need to re-create the query I
can easily without the other person knowing.

I have this so far but it doesnt seem to copy the SQL:

Public Function ExportExcel()
Dim SourceFile, DestinationFile As String
Dim XL As Object

SourceFile = CurrentDBDir & "Work Template.xls"
DestinationFile = "c:\Temp\Retail Analysis Data.xls"
FileCopy SourceFile, DestinationFile

DoCmd.SetWarnings False

DoCmd.TransferSpreadsheet acExport, 8, "1 Branches", DestinationFile, True,

DoCmd.OpenQuery "1 Branches", acViewDesign
CommandBars("Menu Bar").Controls("View").Controls("SQL
DoCmd.RunCommand acCmdCopy

Set XL = CreateObject("Excel.Application")
XL.Workbooks.Open DestinationFile
XL.Run "PasteSQL"
Set XL = Nothing

The "PasteSQL" macro in Excel does work when the SQL is on the clipboard so
I can figure out why it doesnt copy.

Can anyone help?

Many thanks in advance,




You have to create a query def object and assign your query to that
object. Then do something like this: qdf.sql = mysql


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question