none
Saving a File as PDF using the file name and a specific file parth RRS feed

  • Question

  • Hi,

    I'm new to VBA and trying to create a macro that will automatically save my file as a PDF file using the file's base name. This topic has been covered quite a bit in various sites so I was able to get most of the code I needed but for some reason I'm getting tripped up in the very last statement. Here is what I have so far:

    Sub SaveAsPDF()

    Dim SaveDirectory As String
    Dim SaveFileName As String
    Dim BaseName As String
    Dim fso

    SaveDirectory = Environ("Userprofile") & "\Dropbox\Operations\VBA Projects\"

    Set fso = CreateObject("Scripting.FileSystemObject")

    BaseName = fso.GetBaseName(ActiveWorkbook.Name)

    SaveFileName = SaveDirectory & BaseName & ".pdf"

    Sheets(Array("Page1", "Page2")).ExportAsFixedFormat Type:=xlTypePDF, _
                                                  FileName:=SaveFileName, _
                                                  Quality:=xlQualityStandard, _
                                                  IncludeDocProperties:=True, _
                                                  IgnorePrintAreas:=False, _
                                                  OpenAfterPublish:=False

    When I debug through the whole code, that last statement gets highlighted. Not sure what I"m doing wrong. Thanks for your help.

    Tuesday, March 22, 2016 6:47 PM

Answers

  • I found the answer. Turns out the "ExportAsFixedFormat" method doesn't work directly with an array of sheets. It's one of those rare cases when the .select command must be used:

    Sheets(Array("Page1", "Page2")).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                                                  FileName:=SaveFileName, _
                                                  Quality:=xlQualityStandard, _
                                                  IncludeDocProperties:=True, _
                                                  IgnorePrintAreas:=False, _
                                                  OpenAfterPublish:=False


    Tuesday, March 22, 2016 9:19 PM

All replies

  • I found the answer. Turns out the "ExportAsFixedFormat" method doesn't work directly with an array of sheets. It's one of those rare cases when the .select command must be used:

    Sheets(Array("Page1", "Page2")).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                                                  FileName:=SaveFileName, _
                                                  Quality:=xlQualityStandard, _
                                                  IncludeDocProperties:=True, _
                                                  IgnorePrintAreas:=False, _
                                                  OpenAfterPublish:=False


    Tuesday, March 22, 2016 9:19 PM
  • There seems to be no issue in the last statement.

    Can you please verify following:

    1.  Page1 and Page2 names are there for sheets.

    2. SaveFileName really exists

    Do you have any specific error message? That might help me identifying the real issue.


    Vish Mishra

    Tuesday, March 22, 2016 9:21 PM
  • I can verify the sheet names are correct. The initial error I got was:

    Run-time error '438': Object doesn't support this property or method

    The error went away after I used a Select statement.

    Thursday, March 24, 2016 1:10 PM