CODEWITHSUNDEEP
vbams-accessms-access-2010
Alok
Alok

Reputation: 509

Show print dialog box to print Excel sheet from MS Access VBA

I want to print Excel sheet (Single sheet) which is stored in a path of local computer using a click of button on MS Access form. For some business reasons I need to provide flexibility to show the print setup dialog box which we normally get if we were printing Excel sheet from outside, so that user can select the printer, select what part of sheet to print, copies to print etc. I currently have code as shown below:-

Private Sub cmdPrint_Click()
    'wks is already set to Excel sheet which needs to be printed
    wks.PrintOut
End Sub

I am trying to also use something like as shown below , but not sure how to connect the Printout method with xlDialogPrint

 ExcelApp.Dialogs(xlDialogPrint).Show

I am struggling with what code would do that in MS Access VBA.

Upvotes: 1

Views: 1629

Answers (1)

June7
June7

Reputation: 21379

The follow code will open Excel then open print interface to allow user to make choices for printing whatever sheet has focus.

Sub PrintSheet()
Dim xl As Excel.Application, wb As Excel.Workbook
Set xl = CreateObject("Excel.Application")
Set wb = xl.Workbooks.Open("C:\filepath\filename.xlsx")
xl.Visible = True
xl.CommandBars.ExecuteMso ("PrintPreviewAndPrint")
End Sub

Upvotes: 2

Related Questions