Export selected sheets to pdf and save to Desktop

[BDM AT WCS]

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?

    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

 

[HaHoBe]

Hi,

on my system xDesktop holds the path to the desktop without the finishing backslash so maybe try

    Sheets(sheetArray).ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=xDesktop & "\" & Worksheets("inputs").Range("$D$3").Value & " Commercial Papers " & ".pdf"

And

Ciao,
Holger

 

[BDM AT WCS]

Hi,

on my system xDesktop holds the path to the desktop without the finishing backslash so maybe try

    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...

 

[HaHoBe]

Hi,

maybe try a slightly diffferent apporach which worked for me:

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

 

[BDM AT WCS]

This worked a treat! Thanks so much...