Output To Excel Question

[Mikey B]

I am using a continuous form to show a query. It has a form header with the
field names and a form footer with filter buttons and boxes for the user
input/select criteria to modify the list.

I have a command button with this code in it:

Private Sub Export_to_Excel_Macro_Click()
DoCmd.OutputTo acOutputForm, frmAlonza77List, acFormatXLS, , True
End Sub

This work well except that I would like it not to include the controls in
the Form Footer in the export to Excel.

Deleting the resulting 'Controls' columns off the excel sheet is easy
enough, but many people (Supervisor) using this feature are confused by the
excessive data and I am trying to make it as easy as possible for everyone.

Any help would be appreciated.

 

[Michael]

Hi there,

I usually create a dynamic SQL pass it to a function that creates a
temporary query object and export the query with the
Docmd.TransferSpreadsheet. After the query is exported it is deleted. This
works always.

The beauty is that you can format the SQL in terms of column names and
export only the data you want. Your form may be based on one or more a tables
with lots of fields, the user may not be interested.

Heres an example. Lets say your form is based on the following:
SELECT * FROM tbl_Customer WHERE CustomerID = " & lngCID
Now this could be many fields and the below just gets some only...

strSQL = "SELECT [CustomerID] AS [Customer Number], CustName AS [Customer
Name] FROM tbl_Customer WHERE CustomerID = " & lngCID

The above would export only two fields, where the previous one would export
all!

Turning the dynamic SQL into a query...
Set dbs = CurrentDB
Set qdf = dbs.CreateQueryDef("NameOfTmpQuery",strSQL)

The query is now in your database and you can use the
Docmd.TransferSpreadsheet thing

And the delete the temporary "NameOfTmpQuery" query...

Hope that was of any help...
Good luck

 

[Mikey B]

Thanks fr your reply Michael,
Your response is a little beyond my abilities at the moment. I will do
some reading and see if I can adapt your technique into my form.

Thanks