vba code to fit the data to one sheet during printing

[aman]

Hi Guys

I have written the following code that will print the rsult in excel sheet from access form print button. but my problem is result is not printing in one sheet .I can't change it manually from page setup in workbook as the following code creats a new sheet qrycombined in the existing workbook and print the result without saving it. How can i fit the result in one sheet or to change the layout from portrait to landscape.

Private Sub Command0_Click()
 
       Dim objXls As Excel.Application
    Dim objWrkBk As Excel.Workbook
    Dim xprtFile As String
 
    xprtFile = "C:\temp\combinded.xls"
 
    DoCmd.OutputTo acOutputQuery, "qryCombined", acFormatXLS, xprtFile, False
 
    Set objXls = New Excel.Application
    objXls.Visible = False
 
    Set objWrkBk = objXls.Workbooks.Open(xprtFile)
        objWrkBk.PrintOut
    objWrkBk.Close SaveChanges:=False
 
    Set objWrkBk = Nothing
    objXls.Quit
 
    Set objXls = Nothing
 
End Sub

Regards
Aman

 

[MarkK]

It looks like all you want to do is print this thing. Is that the case? If so, use an Access Report. It'll give you way more control over the output without using Excel.

 

[ByteMyzer]

Try the following addition to your code:

[COLOR="darkgreen"]' Excel Constant for Landscape Mode[/COLOR]
[COLOR="Navy"]Const[/COLOR] xlLandscape = 2

[COLOR="navy"]Set[/COLOR] objWrkBk = objXls.Workbooks.Open(xprtFile)
    [COLOR="darkgreen"]' Code to change Page Setup of the Worksheet[/COLOR]
    objWrkBk.Sheets("qryCombined").Select
    [COLOR="navy"]With[/COLOR] objWrkBk.Sheets("qryCombined").PageSetup
        .Orientation = xlLandscape
        .Zoom = [COLOR="navy"]False[/COLOR]
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    [COLOR="navy"]End With[/COLOR]
    [COLOR="DarkGreen"]' Proceed to PrintOut[/COLOR]
    objWrkBk.PrintOut
objWrkBk.Close SaveChanges:=[COLOR="navy"]False[/COLOR]