• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Automatically save multiple versions of dashboard report in same PDF

k1s

Member
Hi,

I've got a 2-page dashboard report that I want to be able to multiple versions of, in the same PDF. By 'versions' I mean with changed values based on user selection from a drop-down list.

I think I know how make a button that runs a macro recorded to save the set print area to generate a PDF, but what I'd really like to have happen is that the user can save - in a single PDF - all the versions of the report, as if she had selected them one-by one from the drop-down list, and saved them one at a time.

It occurred to me that one solution would be to have multiple versions of the report in hidden rows below the one the user interacts with, each one populated based on the next item on the list, but that didn't sound very efficient: the report covers a lot of cells (E9:W137, with a page break at row 71) and there is lots of complex formatting, and given the number of versions is variable, I don't think it will work.

Is the best approach to have Excel somehow cycle through the items on the list?

I looked at the similar pivot table question posted here, but couldn't work out how to adapt the solution.

In case it helps, or is otherwise significant, the list (and selection in cell B4) is from a variable list of 30 or so items, defined with named range 'dd_List', who's source is =tbl_1[Full Name & UPN]. That Table is in turn refreshed from a Power Query.

Any advice greatly appreciated.




I think I know how make a button that runs a macro recorded to save the set print area to generate a PDF, but, what I'd really like to have happen that the user can save in a single PDF all the versions of report, as she had selected them one-by one from the drop-down list.
 
Hi Monty, thanks for your offer of help. I've made a simple mock-up, attached.

Here's the code I have so far:
Code:
Sub SaveThisOneAsPDF()
    With Application.FileDialog(msoFileDialogFolderPicker) ' Select folder only - user won't have control over filename
        .Title = "Select where to save"
        .ButtonName = "Use this folder" ' instead of 'OK' button
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count <> 0 Then
            Sheet1.Range("i6") = .SelectedItems(1)
        Else 'If user cancels
            MsgBox "We didn't save this one (because you didn't select a folder)", vbExclamation, "Just so you know..."
            Exit Sub
        End If
    End With
    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=Range("I7"), _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
   
End Sub

Sub SaveAlltoPDF()
MsgBox ("Still working out how to do it!")
End Sub

As you'll see, nothing yet for the "Save all..."
...and for the "Save this one..." I haven't worked out how to force the page break to fit.
 

Attachments

  • Automatically save multiple versions of dashboard report in same PDF.xlsm
    666.9 KB · Views: 2
I've moved it on a bit with this modification, which loops through list and saves a PDF for each one.

Does anyone know of a way to make the macro put all the versions combined into a single PDF?

Code:
Sub SaveAlltoPDF()
  Dim itm As Range, listrng As Range, selecteditm As Range
    
    With Application.FileDialog(msoFileDialogFolderPicker) ' Select folder only - user won't have control over filename
        .Title = "Select where to save"
        .ButtonName = "Use this folder" ' instead of 'OK' button
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count <> 0 Then
            Sheet1.Range("i6") = .SelectedItems(1)
        Else 'If user cancels
            MsgBox "We didn't save this one (because you didn't select a folder)", vbExclamation, "Just so you know..."
            Exit Sub
        End If
    End With
    
    
  Set listrng = Sheet2.Range("dd_List")
  Set selecteditm = Sheet1.Range("B4")

  For Each itm In listrng
    selecteditm = itm
      ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=Range("I7"), _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
  Next

  End Sub
 
The trouble is, the print area is heavily formatted (including specific cell heights/widths, a page break, etc.), and there are 49 hidden columns of formulae to generate the values, that I don't want to have to re-create 30+ times
 
So external to VBA via Acrobat pro version …

Not really sure what you mean, but possibly your suggestion is to have Acrobat Pro do the combining?

I'm looking for an In-Excel VBA solution rather than having to install/ buy (v. expensive!) licenses for another product
 
Excel : all in a single worksheet to export …​

Thanks for your reply, but once again, a bit cryptic. Having already mentioned that creating a large and variable number of copies in the same sheet is not really feasible, is your repeated reply putting forward your opinion of your preferred approach, or suggesting that there is no alternative?
 
Nope ! As exporting a pdf file is like printing …
Just read Excel & VBA inner helps.
But you can search on Web, who knows ? …

Like scanning all printed pages to a pdf file ! :DD
 
So each report goes to separate sheet. You select array of sheets and export as fixed format.
Thanks, exporting a sheet for each version sounds more feasible than creating many print areas in the same sheet, but is it possible for the VBA to create
... a variable number of copies of the sheet? (between 30 & 40)
... without breaking the formatting?
...hidden/in the background?
...and deleting them again when it's finished?
 
...but I don't understand whether that is using some other PDF software or the Microsoft PDF exporter
As written it uses external software, like I wrote in post #7 …

Chihiro's input may work.
I tried it under 2007 version and it failed,
maybe with your version it may work …

In case of failure, just if warming a couple of neurons,
the copy of each worksheet is not a mess as copying first one
(keeping all formating and if others worksheets respect this format …)
and inserting a break print command between each other page …
 

I knew it's possible but I had a failure and saw some on forums …

It must depends also on objects on worksheets
and for sure about free RAM as Excel needs all in memory !
The more worksheets, the less memory …
 
The more worksheets, the less memory​

Indeed, and with such a richly populated and formulated sheet

So something like this??:

Code:
1.  set N = the number of rows in my list "listrng"

2.      for X = 1 to N
2a.        copy this Sheet1 to name reportprint&X
2b.        set the range("B4") to the next itm in the list
        next X

3. Define an array of sheets somehow using starting sheet number and N

4.    for P = 1 to N
4a      with sheets in the array
4b          export as pdf
4c          somehow delete the sheet again
          end with
      Next P

??
 

After reading VBA inner help as you can do,
try first another way : ActiveWorkbook.ExportAsFixedFormat
If unnecessary worksheet, hide it before exporting workbook.

In case of failure, after selecting several worksheets like Chihiro's link.

If both fail, you will need to go to copy first worksheet
(Copy method creates a brand new workbook with this worksheet)
then for each other worksheet add a break page with a copy of data …
 
Back
Top