asked on

VBA multi-report to one pdf

In Excel 2010 I have vba code that loads relecant data into an array that creates a couple hundred reports for individuals with their specific information. (no problem with this).

Initially the request was to "print" these reports - for which the following was successfully used:
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False

Now the request has changed to print all the reports into 1 pdf with pagebreaks for each report. I am totally stumped how to proceed to create a pdf with all the reports. As stated above, these reports are created on the fly and don't exist to attach.

Any input as to how to proceed on changing the output (print) function to pdf is needed.

Rgonzo1971

Hi,

you could try by creating a temp file

adapt tmpFile and Destination Filename

Sub macro()

ActiveWindow.SelectedSheets.Copy
tmpFile = "c:\tmpFile.xls"
With ActiveWorkbook
    .SaveAs tmpFile
    .ExportAsFixedFormat Type:=xlTypePDF, _
                             Filename:="c:\test.pdf", _
                             Quality:=xlQualityStandard, IncludeDocProperties:=False, _
                             IgnorePrintAreas:=True, _
                             OpenAfterPublish:=False
    .Close
      
End With
Kill tmpFile
End Sub

Open in new window

Regards

uad

ASKER

The problem with the suggestioned code above is that it creates each individual report as a separate pdf. I need to get all the reports into "one" pdf file.

Rgonzo1971

Will some sheets be repeated?
And does the excel already have all sheets

uad

ASKER

The macro populates the each individual one a a time and prints each report after it is created. The "template" is the same for all reports and is reused for each report.

In other words it is something like this:
for x = 1 to ubound(array())
sheets ("statement").select
fill in the data
format various rows & columns
activewindow.selectedsheets.printout copies:=1, _
collate:=true, IgnorePrintAreas:=false

range ("a13:ak500").select
Selection.delete shift:=xlUp
selection.rowheight = 17
next x

The macro works and the reports are generated.

As stated initially in need to convert the
activewindow.selectedsheets.printout copies:=1, _
collate:=true, IgnorePrintAreas:=false

to print all the reports into a single pdf file.

The sample code suggested created a pdf for each report overwritting the prior pdf not appending the new report to the existing pdf

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

uad

ASKER

Currently there are 2 workbooks that the macro uses:
1) Master.xlsm which contains the statement template along with sheets for additional legends and footnotes
2) Macro.xlm which contains the code example used

When I run the code suggested, it is using the Master.xlsm (where the statement template is) as the DestPrtWbk - which isn't good.

Do I need to set DestPrtWbk to a 3rd workbook and activate it at
Sheets("Statement").Copy After:=DestPrtWbk.Sheets(DestPrtWbk.Sheets.Count) ? If so, how would ThisWorkbook.activate be affected. Neither Master.xlsm or Macro.xlsm should be modified in any way.

uad

ASKER

Please ignore my prior issue. I figured out what I needed to do which was to activate the Master.xlsm and select the "statement" sheet prior to executing the delete.

uad

ASKER

Great and simple solution.