To print a worksheet from access form button

[aman]

Hi guys

I have to build reports in excel and print it out when access form button is pressed.

The data is transferred to excel in the following way:

DoCmd.OutputTo acOutputQuery, "qrytemp", acFormatXLS, "C:\Documents and Settings\Amanpreet Kaur\arrivalsheet.xls", True

Now when print button is pressed then the excelsheet comes up with the above statement. I want to print it out on the printer and close it automatically.
Can anyone please provide me vba code for this.

Regards
AMan

 

[ezfriend]

Try this

Private Sub Command0_Click()
    
    'need to add a reference to Microsoft Excel 11.0 (or the version you have) Object Library
    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

 

[aman]

Hi ezfriend

Thanks a lot. It is now printing the worksheet and closing it . But the data is not fit to 1 page.
As its a new worksheet always so i can't even apply page setup manually in the excel spreadsheet. As the following command creates a new worksheet in the workbook and print it out.

DoCmd.OutputTo acOutputQuery, "qrytemp", acFormatXLS, xprtFile, False

so how can I fit the data into a single sheet.

Regards
AMan

 

[ByteMyzer]

See my answer here.