OLE or Automation Printing of Excel

[Gary]

I need a way to setting up excel to "print to fit" fom MS Access using
Automation or OLE. The VBA code below allows to print the excel.xls file,
now I need the ability to print to fit on a letter size paper (original is
11x17).

This is my code for print the Excel from Access:

Function printxls(filename As String)
Static xlApp As Variant
Static xlBook As Variant
Static xlSheet As Variant
Static path As String

Set xlApp = CreateObject("Excel.Application")
path = "c:\chart\"

Set xlBook = xlApp.Workbooks.Open(path & filename & ".xls")
Set xlSheet = xlBook.Worksheets(1)
xlSheet.PrintOut
xlBook.Close (False)
xlApp.Quit

Set xlApp = Nothing
Set xlBook = Nothing
End Function


What would I need to do to cause it to print my excel.xls (11x17) to a
letter??

Thanks!

Gary

 

[Jim Cone]

Gary,
I've modified your code a little, to make sure that
Excel exits gracefully, by setting all the object references to
nothing. The print setting additions, I hope, are self-explanatory.

Jim Cone
San Francisco, USA

'-----------------------------
Function printxls(filename As String)
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim objPSU As Excel.PageSetup
Dim strPath As String

Set xlApp = New Excel.Application
strPath = "c:\chart\"
Set xlBook = xlApp.Workbooks.Open(strPath & filename & ".xls")
Set xlSheet = xlBook.Worksheets(1)
Set objPSU = xlSheet.PageSetup

'use only one of the next two lines...
objPSU.Orientation = xlLandscape
'objPSU.Orientation = xlPortrait
objPSU.PaperSize = xlPaperLetter
objPSU.Zoom = False
objPSU.FitToPagesWide = 1
objPSU.FitToPagesTall = False
xlSheet.PrintOut

Set objPSU = Nothing
Set xlSheet = Nothing
xlBook.Close SaveChanges:=False
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing
End Function
'--------------------------------


"Gary"
<[email protected]>
wrote in message
I need a way to setting up excel to "print to fit" fom MS Access using
Automation or OLE. The VBA code below allows to print the excel.xls file,
now I need the ability to print to fit on a letter size paper (original is
11x17).

This is my code for print the Excel from Access:

Function printxls(filename As String)
Static xlApp As Variant
Static xlBook As Variant
Static xlSheet As Variant
Static path As String
Set xlApp = CreateObject("Excel.Application")
path = "c:\chart\"
Set xlBook = xlApp.Workbooks.Open(path & filename & ".xls")
Set xlSheet = xlBook.Worksheets(1)
xlSheet.PrintOut
xlBook.Close (False)
xlApp.Quit
Set xlApp = Nothing
Set xlBook = Nothing
End Function

What would I need to do to cause it to print my excel.xls (11x17) to a
letter??
Thanks!
Gary

 

[Tom Ogilvy]

With xlSheet
With .PageSetup
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1
End With
.PrintOut
End With