Selecting multiple sheets to print to PDF

  • Thread starter Thread starter Eric_G
  • Start date Start date
E

Eric_G

Can you see something inherently wrong with this code which is being
executed from 1 excel file on another:

Workbooks.Open Filename:="c:\File_2.xlsx", UpdateLinks:=3
xlfile_drive = "c\"
temp_file_name = "Savename.xlsx"

Workbooks("File_2.xlsx").Activate
Sheets(Array("Investment Models E", "Open Models E")).Select
Sheets("Investment Models E").Activate
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
xlfile_drive & temp_file_name _
, Quality:=xlQualityStandard, IncludeDocProperties:=True,
IgnorePrintAreas _
:=False, OpenAfterPublish:=False
 
Check your other post.

Eric_G said:
Can you see something inherently wrong with this code which is being
executed from 1 excel file on another:

Workbooks.Open Filename:="c:\File_2.xlsx", UpdateLinks:=3
xlfile_drive = "c\"
temp_file_name = "Savename.xlsx"

Workbooks("File_2.xlsx").Activate
Sheets(Array("Investment Models E", "Open Models E")).Select
Sheets("Investment Models E").Activate
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
xlfile_drive & temp_file_name _
, Quality:=xlQualityStandard, IncludeDocProperties:=True,
IgnorePrintAreas _
:=False, OpenAfterPublish:=False
Click to expand...
 
Dave has provided some excellent code which I am using, BUT the problem is as
follows.

The "ActiveSheet" command seems to activate the worksheet in which the macro
is stored (and NOT the worksheets which I wish to print in
DestinationFileName). Even though the worksheets in the 2nd file have been
activated, they are not actually the ones being saved to PDF.
 
I can't reproduce your problem with my tester

Public Sub print_files()
Dim wkbk As Workbook
Dim Destinationfile As String

Destinationfile = "C:\Users\Ron Desktop\Desktop\pdfpdf.xlsm"
Set wkbk = Workbooks.Open(Filename:=Destinationfile, UpdateLinks:=3)

With wkbk
.Activate 'it should already be active
.Sheets(Array("Investment Models E", "Open Models E")).Select
End With

ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:="C:\Users\Ron Desktop\Desktop\test.pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True

wkbk.Close False
End Sub
 
Eric_G has brought this to us :
Can you see something inherently wrong with this code which is being
executed from 1 excel file on another:

Workbooks.Open Filename:="c:\File_2.xlsx", UpdateLinks:=3
xlfile_drive = "c\"
temp_file_name = "Savename.xlsx"

Workbooks("File_2.xlsx").Activate
Sheets(Array("Investment Models E", "Open Models E")).Select
Sheets("Investment Models E").Activate
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
xlfile_drive & temp_file_name _
, Quality:=xlQualityStandard, IncludeDocProperties:=True,
IgnorePrintAreas _
:=False, OpenAfterPublish:=False
Click to expand...

You need to group the sheets and use ActiveWindow.SelectedSheets
something like this:

With ActiveWindow.SelectedSheets
'set up your printer and PageSetup
'...

'Print or PrintPreview
.PrintOut Copies:=1, Preview:=True, _
ActivePrinter:="PDF_PrinterName"
End With

To group the sheets, here's a basic template for excluding a specific
sheet from all sheets, but you can modify the IF construct to include
any number of specific sheets using OR:

Sub GroupSheets()
Dim wks As Worksheet, Shts() As String
Dim i As Integer

i = 0
For Each wks In ActiveWorkbook.Worksheets
If wks.Name <> "SheetName1" Then
'Fill the array with names
ReDim Preserve Shts(0 To i)
Shts(i) = wks.Name
i = i + 1
End If
Next

'Select the array
ActiveWorkbook.Worksheets(Shts).Select
End Sub

IF...OR example:

If wks.Name = "Name1" Or wks.Name = "Name2" _
Or wks.Name = "Name3" Then '//fill the array with names

HTH
Garry
 
Thank you all for your help, but this did not solve the problem.

The problem remains as follows. The code executes BUT the actual PDF file
being created contains data from the sheet where the macro is stored (i.e.
File_1) and NOT the data from the file and worksheets which I am attempting
to print (i.e. worksheets "Investment Models E" and "Open Models E"). Even
having changed the command "ActiveSheet" to "With ActiveWindow.Selected
Sheets", the same problem is occuring. Here is the code as it currently
stands -- I am so baffled. Any assistance would be greatly appreciated.
____________
Sub print_files()
Dim wkbk As Workbook
Dim Destinationfile As String

Destinationfile = "U:\mktg\star keystone
reporting\Keystone\2010-Apr\Keystone Performance-Apr-10.xlsx"
Set wkbk = Workbooks.Open(Filename:=Destinationfile, UpdateLinks:=3)


With wkbk
.Activate 'it should already be active
.Sheets(Array("Investment Models E", "Open Models E")).Select
.Sheets("Investment Models E").Activate
End With

With ActiveWindow.SelectedSheets
ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:="C:\temp\test.pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True

wkbk.Close False
End With
End Sub
 
Eric_G wrote on 5/10/2010 :
To do this task the sheets MUST be grouped AND you must use
ActiveWindow.SelectedSheets as per my example. I don't see a problem
with the way you're grouping (other than the sheetnames are
hard-coded), so the issue lies in how you are "printing" to PDF. The
only way I know how to do this is to use a PDF printer that's already
installed. I realize MSO apps now have the ability to do what you're
trying to do here, but experience tells me to never rely on such things
to process problem free, and so I avoid using them. (Sounds to me like
the export to PDF doesn't work any better than their export to HTML<g>
-There are VB PDF 'wrappers' available that do a better job, and
provide output options<g>)

I was trying to find a more robust version of the GroupSheets() sub I
posted earlier but was unable to do so at the time. I eventually found
it inside an a client's plugin.xla that allows running admin utilities
inside the main addin. I've pasted it here. This is a well documented,
reusable procedure you can use to group sheets in one of two ways: you
can specify which sheets in a Wkb to include, which will group only the
sheets you specify; -OR- you can specify which sheets to exclude, which
will group all sheets in the workbook except the ones you specify. The
sheetnames aren't hard-coded, which makes it convenient to change the
grouping depending on what you want to do with the group. (In this
client's case, they were generating new workbooks to attach to emails
being sent to various places that only required certain sheets)

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' GroupSheets()
' This procedure requires only the necessary amount of coding be used
' in the Caller. By default, it requires passing only the first arg.
' Use Example: GroupSheets "Sheet1,Sheet3"
' creates a group of only those sheets.
' To group all sheets in a workbook except those sheets:
' GroupSheets "Sheet1,Sheet3", False
' You can pass the Wkb arg to specify any open workbook.
' (The Wkb doesn't need to be active for this purpose)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub GroupSheets(sSheetnames As String, _
Optional bInGroup As Boolean = True, _
Optional Wkb As Workbook)
' Groups sheets in Wkb based on whether sSheetnames are to be included
or excluded.
' sSheetnames is a comma delimited string. (ie: "Sheet1,Sheet3")

Dim Shts() As String, sz As String
Dim i As Integer, wks As Worksheet, bNameIsIn As Boolean

i = 0
If Wkb Is Nothing Then Set Wkb = ActiveWorkbook

For Each wks In Wkb.Worksheets
bNameIsIn = (InStr(sSheetnames, wks.name) > 0)
sz = ""
If bInGroup Then
If bNameIsIn Then sz = wks.name '//includes Sheet in group
Else
If Not bNameIsIn Then sz = wks.name '//excludes Sheet from group
End If
'Build the array
If Not sz = "" Then
ReDim Preserve Shts(0 To i)
Shts(i) = sz
i = i + 1
End If
Next
'Select the array
ActiveWorkbook.Worksheets(Shts).Select
End Sub

Use Example:

Sub SomeProc()
'...//do stuff

GroupSheets "Sheet1,Sheet3"

With ActiveWindow.SelectedSheets
'do PageSetup
'...

'Print or PrintPreview
.PrintOut Copies:=1, Preview:=True, _
ActivePrinter:="PDF_PrinterName"
End With
End Sub

HTH
Garry
 
Back
Top