CODEWITHSUNDEEP
vbaexcelpdf
MilesToGoBeforeISleep
MilesToGoBeforeISleep

Reputation: 183

Saving a File as PDF using the file name and a specific file path

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

End Sub

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

Upvotes: 0

Views: 1802

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27249

For some reason, the ExportAsFixedFormat method does not work directly with an array of sheets.

The below will work (it's one of the rare cases where Select and Activate are necessary in Excel VBA).

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

Upvotes: 1

Related Questions