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

Question

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.

Run-time error '438': Object doesn't support this property or method — MilesToGoBeforeISleep, Mar 22 '16 at 19:55

score 1 · Accepted Answer · answered Mar 22 '16 at 19:56

1

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

answered Mar 22 '16 at 19:56

Scott Holtzman

27,099
5
37
72

3

It works just fine for me without the activate statement - I was just about to post a very similar answer to yours :) Maybe because I use fully qualified references? ThisWorkbook.Sheets(Array("Page1", "Page2")).Select – Miqi180 Mar 22 '16 at 20:11
1

Brilliant. Thanks Scott. I concur with Miqi that the Activate statement is not needed. – MilesToGoBeforeISleep Mar 22 '16 at 20:55
[Select and Activate are not even required if you only want to print a range of sheets in the Workbook.](http://stackoverflow.com/a/36107539/4088852) – Comintern Mar 22 '16 at 22:57
1

@Comintern - bear in mind that the link you provided uses the `ExportAsFixedFormat` method on an **an entire workbook**, as opposed to only selected sheets (granted I do see the work around to only use certain sheets within the workbook.). – Scott Holtzman Mar 23 '16 at 12:52
1

@ScottHoltzman - That's my point - you don't have to use `Select` if you use a higher level object. – Comintern Mar 23 '16 at 12:59
@Comintern - it's a valid point, but I also think its worth knowing how to do both ways - as there may be complications in a workbook structure that make it easier to the method on selected sheets only. – Scott Holtzman Mar 23 '16 at 13:05

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

1 Answers1