How to print all sheets in Excel from Access VBA

[greeni91]

I have a database that I am currently upgrading and I am having just one small problem.

I have a button on a form that, when clicked, will print out the Current Form and print an Excel Document based on my current report number.

The problem I am having is that when the Workbook is open it may have more than one sheet that I wish to printout. I have used this code below:

Private Sub cmdPrint_Click()
Dim appexcel As Object
Dim MyFile As String

Set appexcel = CreateObject("Excel.Application")
MyFile = Me.PR_Number

DoCmd.PrintOut acSelection

appexcel.Workbooks.Open "G:\MB Aerospace\Aerospace New\General Access\Problem Report\Problem Reports 2012\Concession Detail\" & MyFile & ".xlsx"
appexcel.Visible = True

appexcel.ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
appexcel.Quit
End Sub

This will work for the active sheet in the excel document but I want to print all sheets in the workbook.

Can someone please help. I have a feeling it will just be one line of code to change but I have checked around and cannot see what I am missing.

[zmbd]

DoCmd.PrintOut acSelection

Is not an EXCEL vba command.
From where are you running this?

[greeni91]

I am running this code from an Access database using VBA with the hope of controlling an Excel spreadsheet to some degree.

[zmbd]

Using your code then:
Replace line 13:
Caution, this will have no formatting etc... I advise doing a manual print-preview first, setting the pages to fit page and margins first and saving the file.
You can automate this too; however, I find it easier to have the file formatted natively.

appexcel.printout copies:=1 Collate:=True, IgnorePrintAreas:=False

Take a look at: Application Automation in our insight articles (I find it easier to navigate using the link at the bottom of this page Microsoft Access / VBA Insights Sitemap

[greeni91]

I had a little play around with my database before my break and I think I have solved my own problem. The code provided above works just as well but this is the final revision of the code I used on the database.

Private Sub cmdPrint_Click()
Dim appexcel As Object
Dim MyFile As String

Set appexcel = CreateObject("Excel.Application")
MyFile = Me.PR_Number

DoCmd.PrintOut acSelection

appexcel.Workbooks.Open "G:\MB Aerospace\Aerospace New\General Access\Problem Report\Problem Reports 2012\Concession Detail\" & MyFile & ".xlsx"
appexcel.Visible = True

appexcel.ActiveWorkbook.PrintOut
appexcel.ActiveWorkbook.Close False
End Sub

As you may see I have only changed the last 2 lines of code. I have changed line 13 from:

appexcel.ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

To this:

appexcel.ActiveWorkbook.PrintOut

This code change just looks at the active workbook rather than individual sheets for the printout command.

I also changed the line 14 of the code to this:

appexcel.ActiveWorkbook.Close False

This was to close the excel workbook after print as it is no longer needed onscreen after this function is complete.

Hope this helps anyone with the same problem

[zmbd]

Using the "activeworkbook " condition in the code will not ensure that the pages printed will be those from the Excel object that you created in the VBA code.

This can happen when the user may have another Excel workbook open on the desktop prior to opening or executing your VBA script. If the user happens to select this previously opened workbook, or even if some how another Excel session is started prior to the printout command, then this becomes the active workbook not the intended target.

For this reason I've cleared the best answer selection.