Export range to PDF without saving

Question

I've found a bit of VBA script that works:

Sub ExportToPDF()

    With Sheets("Results").Range("B10:J100")
        .ExportAsFixedFormat _
            Type:=xlTypePDF, _
            FileName:="C:\Export.pdf", _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=True
    End With

End Sub

I'm still learning VBA, I do not actually need the range to be saved as a separate file, I just want the range selected to be published as a PDF that opens up.

I am not sure how to amend the above code

Did you try that code? What you say you want is what it does. — Tim Williams, Oct 03 '18 at 02:31
*"I do not actually need the range to be saved as a separate file, I just want the range selected to be published as a PDF that opens up"* ... Um... so what you're saying is that you CAN get the PDF to display as expected, but you don't want to keep the file 'Export.pdf'? I think your only play is to create the file then delete it when you're done with it...see here: https://stackoverflow.com/q/67835/3451115 — SlowLearner, Oct 03 '18 at 02:55
You cannot publish straight to a PDF Viewer, as the other comments have also explained the file needs to be saved then opened, if you don't want it permanently then delete it after the `End With` you can do so by putting this there: `kill("C:\Users\tjb1\Desktop\Export.pdf")` or alternatively use some of the other methods like the FileSystemObject method listed here: https://stackoverflow.com/questions/67835/deleting-a-file-in-vba — Dan Donoghue, Oct 03 '18 at 03:29
The issue is that this excel file will be used by multiple users, so the FileName path will be different for every user — premiumcopypaper, Oct 03 '18 at 10:40
You could simply install a PDF Printer like CutePDF (or any one of a number of PDF printers) and have it handle the whole process. — Frank Ball, Oct 04 '18 at 19:10

score 1 · Answer 1 · answered Oct 03 '18 at 02:54

1

After the

End With

in your code, you can add

CreateObject("Shell.Application").Open ("C:\Users\tjb1\Desktop\Export.pdf")

That code worked for me.

Good luck!

answered Oct 03 '18 at 02:54

oxwilder

756
5
14

2

But the VBA code already opens the file here: `OpenAfterPublish:=True` – Dan Donoghue Oct 03 '18 at 03:31

score 0 · Answer 2 · answered Oct 04 '18 at 17:39

0

In order to export to PDF, a file must be saved to a drive. For a spreadsheet that will be used by multiple users, the FileName in the VBA script must be a generic drive all users have access to.

answered Oct 04 '18 at 17:39

premiumcopypaper

165
1
13

Export range to PDF without saving

2 Answers2