Link to home
Create AccountLog in
Avatar of kritskiy

asked on

VBA code for exporting/saving certain sheets to PDF in Excel 2007

I need to write a code that will save/export certain sheets (always the same ones) into a pdf. Hopefully, there is also a dialog box appearing asking user where he wants to save it.

How can I do it?

Avatar of Steve Knight
Steve Knight
Flag of United Kingdom of Great Britain and Northern Ireland image

Roughly speaking something like this... you can soon make the file names up from dates / sheet names etc. too of course

fileName = Application.GetSaveAsFilename( _
 fileFilter:="PDF files, *.pdf")
If fileName = False Then Exit Sub

Sheets("MENU").ExportAsFixedFormat _
    Type:=xlTypePDF, _
    fileName:=fileName, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=False, _
    IgnorePrintAreas:=False, _
End Sub

Give True/False for the last one if you want it to open for the user or just be saved.

Just noticed, 100 points :-(

Avatar of kritskiy


How do I name several sheets to save into one PDF? I tried Sheets(Array("Sheet1", Sheet2, etc).ExportAsFixedFormat...

but got a run-time error 438 Object doesn't support this property or method.
AFAIK you can't do that, you would have to use the equivalent workbook command on the whole workbook, or copy the sheets to a new workbook and then export the lot..... I just ran one against a workbook here and it produced a 4600 page PDF...

i.e. run it against specific workbook, or ActiveWorkbook.

Are you familiar with copying sheets into a new workbook ok?

I can just use:

Sheets(Array("Sheet1", "Sheet2", etc)).Copy

How do I then export that into PDF? Also, can I write a code to close that newly created workbook without saving after PDF is created/saved?
Avatar of Steve Knight
Steve Knight
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Yes, I think the only thing I am missing now is saving the PDF in the same folder as the original excel file is and saving it under the same name as original excel file.pdf. Is that doable?
I didn't see your last comment there... is this why you only gave a B grade :-(

You can get the directory it is in with activeworkbook.path and the filename with, or activeworkbook.fullname is the lot.

So to strip off the extension and replace with pdf you do something like:

Dim fn As String, pdfname As String
fn = ActiveWorkbook.Name
pdfname = ActiveWorkbook.Path & "\" & Left(fn, InStr(Len(fn) - 5, fn, ".") - 1) & ".pdf"

This works great, thank you!!
No problem.  Always a good idea to make a comment before closing with a "B" grade please as we are graded on our answers.


ok, i was trying to see if I could change the grade to A afterwards but looks like I can't.:(
don't worry, not a problem.