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.
|
Option Compare Database
Option Explicit
Sub ExportAndPrintFromAccess()
Const szTempbook As String = "\Temp.xls"
Const szQueryName As String = "qryInfo"
Dim szFullTempPath As String
szFullTempPath = CurrentProject.Path & szTempbook
On Error GoTo ErrHandle
With Application
.Echo False
DoCmd.OutputTo acOutputQuery, szQueryName, acFormatXLS, szFullTempPath, False
Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object
Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.Workbooks.Open(szFullTempPath)
Set xlWS = xlWB.Worksheets(szQueryName)
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
xlWS.Range("A1").Select
xlApp.Visible = True
xlWS.PrintPreview
xlWB.Close False
Kill szFullTempPath
ErrorExit:
Set xlWS = Nothing
Set xlWB = Nothing
Set xlApp = Nothing
.Echo True
End With
Exit Sub
ErrHandle:
MsgBox Err.Description
GoTo ErrorExit
End Sub
|