3

I'm trying to export a file as a PDF as part of a larger macro. However, I'd like the user to have the option of saving the file to a directory of his or her choosing, and I think this would be easiest with a browsing dialog box. However, I can't figure out how to pull one up. Currently, my code reads as follows. 

ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "C:\Users\<filepath>\11.08E PT5 Executive Summary - v3.2.pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, From:=1, To:=3, OpenAfterPublish:=True

I'd like to replace with the result of the dialog box.

Gaffi
  • 4,307
  • 8
  • 43
  • 73
soytsauce
  • 381
  • 2
  • 4
  • 16

1 Answers1

5

Application.GetSaveAsFilename.

dim v as variant
v = Application.GetSaveAsFilename("11.08E PT5 Executive Summary - v3.2.pdf", "PDF Files (*.pdf), *.pdf")

if vartype(v) = vbString then
  ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=v, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, From:=1, To:=3, OpenAfterPublish:=True
end if
GSerg
  • 76,472
  • 17
  • 159
  • 346
  • Late to this thread but wondering, what is the reason or logic behind the `From:=1, To:=3` portion. I am learning and don't quite follow why this is there or what it does. – Rivers31334 Mar 03 '16 at 18:32
  • Also, in doing it this way, how does the program know what the default file path will be when the save box comes up? – Rivers31334 Mar 03 '16 at 18:33
  • @Rivers31334 The `From:=1, To:=3` is in fact irrelevant to the question. I copied it from the OP's code because that's what they had. It is supposed to only save pages from 1 to 3, but this also [requires `Range:=wdExportFromTo`](https://msdn.microsoft.com/en-us/library/bb256835%28v=office.12%29.aspx) which the OP did not have, so it might have no effect after all. The default file path is the [current directory](http://stackoverflow.com/a/5944101/11683). – GSerg Mar 03 '16 at 18:45