Export SQL

  • Thread starter Thread starter Martin
  • Start date Start date
M

Martin

Hello,

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,
"Data"

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


Set XL = CreateObject("Excel.Application")
XL.Workbooks.Open DestinationFile
XL.Run "PasteSQL"
XL.Workbooks.Close
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,

Martin
 
Martin,

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

Ben
 
Back
Top