VBA to print specific excel worksheets on a workbook (1 Viewer)

[paulcherianc]

Can anyone help me to find the VBA to print specific excel worksheets on a workbook from access vba.

 

[Beetle]

Something like the following (air code);

    Dim appXL As Excel.Application
    Dim wks As Excel.Workbook
  
    Set appXL = CreateObject("Excel.Application")
    Set wks = appXL.Workbooks.Open("C:\Users\SomeUser\Documents\YourExcelFile.xlsx")
  
    appXL.Visible = True
    
    With wks
        .Worksheets("Sheet1").Select
        .PrintOut Copies:=1
    End With

 

[paulcherianc]

Hi,

Thanks!

Can I add multiple sheet names to the code as follows?

Dim appXL As Excel.Application
Dim wks As Excel.Workbook

Set appXL = CreateObject("Excel.Application")
Set wks = appXL.Workbooks.Open("C:\Users\SomeUser\Documents\YourExcelFile.xlsx")

appXL.Visible = True

With wks
.Worksheets("Sheet1").Select
.PrintOut Copies:=1
.Worksheets("Sheet2").Select
.PrintOut Copies:=1
.Worksheets("Sheet3").Select
.PrintOut Copies:=1
End With

 

[Gasman]

Here is how I have approached it, (albeit in Excel) as I do not need all sheets printed.
I have a cell that has the value Print if the sheet needs to be printed. Then just run this.

ub CreateReport()
Dim ws As Worksheet
Dim intRowMax As Integer

'Cycle through all sheets that need printing
For Each ws In Application.Worksheets
    If ws.Range("P1").Value = "Print" Then
        intRowMax = Range("A" & Rows.Count).End(xlUp).Row
        ws.PageSetup.PrintArea = "A1:I" & intRowMax
        If Sheets("Property").Range("P2").Value = "Portrait" Then
            ws.PageSetup.Orientation = xlPortrait
        Else
            ws.PageSetup.Orientation = xlLandscape
        End If
        ws.PageSetup.LeftFooter = "&Z" & "\" & "&F"
        ws.PageSetup.RightFooter = "&D  &T"
        ws.PageSetup.RightFooter = ""
        ws.PrintOut
    End If
Next
Sheets("Property").Select
Set ws = Nothing

End Sub

HTH

 

[Beetle]

Hi,

Thanks!

Can I add multiple sheet names to the code as follows?....

Yes, you can do that.