 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / February 2007Output To Excel Question
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. 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 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 > Hi there, > [quoted text clipped - 28 lines] > Hope that was of any help... > Good luck |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.