Solved Macro Button Code, SaveAs PDF w/ file name from cell

June 17, 2013 at 10:26:53
Specs: Windows 7
To whom this may concern,

I have a work sheet that is an office form, I would like to have a button on the form that that can do 3 tasks at one time:

1. Save Sheet as a PDF
2. Save the PDF with the file name from cell's "O30" & "O31" & "A1" with a space of dash between the three names
3. Save the PDF in a folder on the desktop.

Thanks you for you help and support in advance.
-E925


See More: Macro Button Code, SaveAs PDF w/ file name from cell

Report •

#1
June 17, 2013 at 11:41:47
✔ Best Answer
The code below should do what you ask with the following caveats:

1 - I am assuming that the cells you referenced are on Sheet1. Modify as required
2 - You will need to put the filepath to the Desktop where indicated. It must inside the opening quote and before the \Desktop.

Sub DesktopPDF()

'Set path to Desktop
    fPath = " ***Insert Path To Desktop Here*** \Desktop"

'Build File Name from Sheet1 O30, O31 & A1
    fName = Sheets(1).Range("O30") & "-" & _
            Sheets(1).Range("O31") & "-" & _
            Sheets(1).Range("A1")

'Export as PDF to Desktop
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPath & fName, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=True
        
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
June 17, 2013 at 13:49:48
DerbyDad03,

Thank you very much for taking the time to answer my question. I am still having one small problem. I entered the code below and I continue to get a "Compile error: Syntax error". when I click debug I am shown the "fPath = C:\Users\edunivan\Desktop" (in red font) any ideas why?

Private Sub CommandButton2_Click()

'Set path to Desktop
fPath = C:\Users\e925\Desktop

'Build File Name from Sheet1 O30, O31 & A1
fName = Sheets(1).Range("O30") & "-" & _
Sheets(1).Range("O31") & "-" & _
Sheets(1).Range("A1")

'Export as PDF to Desktop
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPath & fName, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True

End Sub


Report •

#3
June 17, 2013 at 14:43:49
You are getting the error because you did not follow my instructions.

2 - You will need to put the filepath to the Desktop where indicated. It must inside the opening quote and before the \Desktop.

The path name must be a text string and a text string must be enclosed in quotes.

fPath = "C:\Users\e925\Desktop"

In addition, please click on the following line and read the instructions on how to post VBA code in the forum using the pre tags.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question