Access

Print a Query in Excel

Ease of Use

Intermediate

Version tested with

2000, 2002 

Submitted by:

Justinlabenne

Description:

Exports a query's results to Excel to be formatted and printed 

Discussion:

This code just exports an Access query's results to a temporary Excel workbook, formats it with borders, page headers, etc... to be printed out, and then the temp workbook is destroyed. The example does not directly print out the sheet. It sends the formatted sheet to print preview, and makes Excel visible so you can see what happens. Normally, and with minor changes to the code, Excel would never even have to be visible and the sheet of info would just be printed out without a user ever knowing Excel was involved. 

Code:

instructions for use

			

Option Compare Database Option Explicit Sub ExportAndPrintFromAccess() ' Temp workbook's name Const szTempbook As String = "\Temp.xls" ' Access query where the info comes from Const szQueryName As String = "qryInfo" ' Temp book is created in the same location as this db ' so we build a valid path for it Dim szFullTempPath As String szFullTempPath = CurrentProject.Path & szTempbook On Error GoTo ErrHandle With Application .Echo False ' Output the query to our Excel book DoCmd.OutputTo acOutputQuery, szQueryName, acFormatXLS, szFullTempPath, False ' Late binding to avoid reference: Dim xlApp As Object 'Excel.Application Dim xlWB As Object 'Workbook Dim xlWS As Object 'Worksheet ' Create the instance of Excel that we will use to open the temp book Set xlApp = CreateObject("Excel.Application") Set xlWB = xlApp.Workbooks.Open(szFullTempPath) Set xlWS = xlWB.Worksheets(szQueryName) ' Format our temp sheet ' *************************************************************************** xlApp.Range("A1").Select Const xlLandscape As Long = 2 Const xlCenter As Long = -4108 With xlWS.PageSetup .CenterHeader = "&""Arial,Bold""&14PRINTED TEMP REPORT " .RightFooter = "&D" .PrintHeadings = False .PrintGridlines = False .CenterHorizontally = True .Orientation = xlLandscape .Draft = False End With With xlWS .UsedRange.HorizontalAlignment = xlCenter .UsedRange.Rows.RowHeight = 15.25 End With ' *************************************************************************** ' Select to make sure the sheet has focus xlWS.Range("A1").Select ' ########################################################################### ' REMOVE THE NEXT 2 LINES (ONLY USED FOR EXAMPLE) xlApp.Visible = True xlWS.PrintPreview ' ########################################################################### ' *************************************************************************** ' Uncomment the next line to print the formatted data ' The above section should be removed! 'xlWS.PrintOut ' *************************************************************************** ' Close the file we created xlWB.Close False ' Since it's a temp, destroy it Kill szFullTempPath ErrorExit: ' Explicitly Clear Memory Set xlWS = Nothing Set xlWB = Nothing Set xlApp = Nothing .Echo True End With Exit Sub ErrHandle: MsgBox Err.Description GoTo ErrorExit End Sub

How to use:

  1. Open an Access Database
  2. Copy the code
  3. Select MODULES > NEW
  4. Paste code into the right pane
  5. Press Alt+Q to return to Access
 

Test the code:

  1. Run the code however you wish, from a form button etc...
  2. You will need to have a query named "qryInfo" to use the code without modifications.
  3. Change the code to suit for your query name that you wish to export.
  4. This example sends the sheet to print-preview instead of just printing.
  5. Adjust code to suit.
 

Sample File:

TestOutput.zip 27.29KB 

Approved by mdmackillop


This entry has been viewed 405 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express