Export selected sheets to pdf and save to Desktop

BDM AT WCS

New Member
Joined
Dec 17, 2018
Messages
7
I'm implementing a 'live' and self-service reporting regime. I want managers to be able to access their own area's live data and take a snapshot of it at any point in time by saving selected sheets to a pdf file on their respective Desktops. The macro I've put together runs without errors - but it doesn't save the pdf to the desktop. Can anyone see what's wrong here? The "Worksheets("inputs").Range("$D$3").Value" is picking up today's date in yymmdd format such that the multiple snapshot files they'll end up with can be easily sorted and found in Windows Explorer. I think it's here where the problem lies - it seemed to be working and saving to the Desktop when I had the filename starting with a text value of "Commercial Papers" and then appending the date to the filename - but this way around, something's gone wrong. Can you spot it?

VBA Code:
    Sub Save_As_PDF_To_Desktop()
    
        Dim xDesktop As String
        Dim sheetArray As Variant
    
        xDesktop = CreateObject("WScript.Shell").SpecialFolders("Desktop")
        sheetArray = Array("action sheet", "agenda", "my sector commercial summary", "Paper B Live", "Paper B Productive", "Paper C Income Nat", "Paper C Income Inter", "Paper C Income NMC", "Income By Sector", "gap_analysis", "my Gap", "Paper_D_Y-on-Y", "FWDF Summary")
    
    For Each ws In ActiveWorkbook.Worksheets
        ws.Columns.AutoFit
    Next ws
    
    Sheets(sheetArray).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=xDesktop & Worksheets("inputs").Range("$D$3").Value & " Commercial Papers " & ".pdf"
    
    End Sub
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Hi,

on my system xDesktop holds the path to the desktop without the finishing backslash so maybe try
VBA Code:
    Sheets(sheetArray).ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=xDesktop & "\" & Worksheets("inputs").Range("$D$3").Value & " Commercial Papers " & ".pdf"
And

Ciao,
Holger
 
Upvote 0
Hi,

on my system xDesktop holds the path to the desktop without the finishing backslash so maybe try
VBA Code:
    Sheets(sheetArray).ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=xDesktop & "\" & Worksheets("inputs").Range("$D$3").Value & " Commercial Papers " & ".pdf"
And

Ciao,
Holger
Thanks for this! Indeed your suggestion did write the file as intended to the desktop but it wouldn't pick up the date value and write it to the filename. However if I switch the code around so that the text "Commercial Papers" appears first in the filename then it runs OK. Since I needed to do that to make it work, I've killed off the date value ($D$3 in the "inputs" worksheet) and have just gone with Filename:=xDesktop & "\Commercial Papers " & Format(Date, "yymmdd") & ".pdf" which will still allow the managers to chronologically sort the files based on the names alone. So a little bit of a compromise solution but it'll serve the same purpose. Thanks again for your input...
 
Upvote 0
Hi,

maybe try a slightly diffferent apporach which worked for me:
VBA Code:
Sub PrintMultipleSheetsToPDFonDesktop()

  Dim ws              As Worksheet
  Dim blnSingle       As Boolean
  Dim strFileName     As String
  Dim sheetArray      As Variant
  
  
  strFileName = Format(Worksheets("inputs").Range("$D$3").Value, "yymmdd") & " Commercial Papers " & ".pdf"
  sheetArray = Array("action sheet", "agenda", "my sector commercial summary", "Paper B Live", "Paper B Productive", _
      "Paper C Income Nat", "Paper C Income Inter", "Paper C Income NMC", "Income By Sector", "gap_analysis", "my Gap", "Paper_D_Y-on-Y", "FWDF Summary")
  blnSingle = True
  
  For Each ws In Sheets(sheetArray)
    ws.Columns.AutoFit
    ws.Select blnSingle
    blnSingle = False
  Next ws
                                                                                
  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                                  Filename:=CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator & strFileName, _
                                  Quality:=xlQualityStandard, _
                                  IncludeDocProperties:=True, _
                                  IgnorePrintAreas:=False, _
                                  OpenAfterPublish:=False

End Sub
Ciao,
Holger
 
Upvote 0
Solution

Forum statistics

Threads
1,223,268
Messages
6,171,100
Members
452,379
Latest member
IainTru

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top