• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 417
  • Last Modified:

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.
0
uad
Asked:
uad
  • 5
  • 3
1 Solution
 
Rgonzo1971Commented:
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
0
 
uadAuthor Commented:
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.
0
 
Rgonzo1971Commented:
Will some sheets be repeated?
And does the excel already have all sheets
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
uadAuthor Commented:
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
0
 
Rgonzo1971Commented:
Then create a tmp file and populate it with your statements and the create a pdf befor closing without saving your xl file

Sub Macro2()

Dim DestPrtWbk As Workbook
Set DestPrtWbk = Workbooks.Add
ThisWorkbook.Activate
For x = 1 To UBound(Array())
  Sheets("statement").Select
  'fill in the data
  'format various rows & columns
  
  Sheets("Statement").Copy After:=DestPrtWbk.Sheets(DestPrtWbk.Sheets.Count)
  ThisWorkbook.Activate
  If x = 1 Then ' delete unnecessary sheets
    For Idx = DestPrtWbk.Sheets.Count - 1 To 1 Step -1
        Application.DisplayAlerts = False
        DestPrtWbk.Sheets(Idx).Delete
        Application.DisplayAlerts = False
    Next
  End If


  With Range("a13:ak500")
    .Delete shift:=xlUp
    .RowHeight = 17
  End With
Next x

DestPrtWbk.ExportAsFixedFormat Type:=xlTypePDF, _
                             Filename:="\\chca6037.eur.beluni.net\a853725$\Documents\test.pdf", _
                             Quality:=xlQualityStandard, IncludeDocProperties:=False, _
                             IgnorePrintAreas:=False, _
                             OpenAfterPublish:=False

     
DestPrtWbk.Close False
End Sub

Open in new window

Regards
0
 
uadAuthor Commented:
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.
0
 
uadAuthor Commented:
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.
0
 
uadAuthor Commented:
Great and simple solution.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now