Macro for Save As PDF Excel 2010

[jlajla24]

I've done some searching and found alot about this but the options are always for an auto naming the file based on info in a a particular cell or the date.

In my version of this macro, I already have the Excel workbook saved with the exact name that I want the PDF to be named. Being a Macro rookie, I can't seem to find this answer.

How do I tell the macro that the filename should be the same as the workbook it's saving from?

 

[ClaytonFord]

Hi Firstly i must thank you for spending the time to reply to people and aid the likes of myself with developing our desire to learn.

i have used your code in this post and it works great and is close to what i am after, what im now looking to do is remove the shape im inserting on my sheet that says "click here to create a PDF" reason for this is the sheet is my invoice template and i would rather not send my clients a PDF invoice with the shape.

can you please help?

 

[Osocod]

Right click at the shape. Choose "Format control" (I think, my Excel version is not English). In the Properties tab there is a checkbox that lets you exclude the shape from being printed.

 

[REAN]

Hi, the PDF is saved correctly, but can you tell me how to save the PDF with the name in cell A1?

Thanx

I guess this will work. Try:

[COLOR=Navy]Sub[/COLOR] Save_as_pdf()
[COLOR=Navy]Dim[/COLOR] FSO [COLOR=Navy]As[/COLOR] [COLOR=Navy]Object[/COLOR]
[COLOR=Navy]Dim[/COLOR] s(1) [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
[COLOR=Navy]Dim[/COLOR] sNewFilePath [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]

    [COLOR=Navy]Set[/COLOR] FSO = CreateObject("Scripting.FileSystemObject")
    s(0) = ThisWorkbook.FullName
    
    [COLOR=Navy]If[/COLOR] FSO.FileExists(s(0)) [COLOR=Navy]Then[/COLOR]
        [COLOR=SeaGreen]'//Change Excel Extension to PDF extension in FilePath[/COLOR]
        s(1) = FSO.GetExtensionName(s(0))
        [COLOR=Navy]If[/COLOR] s(1) <> "" [COLOR=Navy]Then[/COLOR]
            s(1) = "." & s(1)
            sNewFilePath = Replace(s(0), s(1), ".pdf")
            
            [COLOR=SeaGreen]'//Export to PDF with new File Path[/COLOR]
            ActiveSheet.ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=sNewFilePath, _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, OpenAfterPublish:=True
        [COLOR=Navy]End[/COLOR] [COLOR=Navy]If[/COLOR]
    [COLOR=Navy]Else[/COLOR]
        [COLOR=SeaGreen]'//Error: file path not found[/COLOR]
        MsgBox "Error: this workbook may be unsaved.  Please save and try again."
    [COLOR=Navy]End[/COLOR] [COLOR=Navy]If[/COLOR]
    
    [COLOR=Navy]Set[/COLOR] FSO = [COLOR=Navy]Nothing[/COLOR]

[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

It may seem overkill checking if your XL file exists but unsaved workbooks have no filepath so it's an error that could be very confusing at some later date.

 

[flyersny81]

I need to create a macro that will allow me to save a PDF from an XL file I am creating by running a report out of Access. This report yields a workbook with 2 sheets in it. The first tab (sheet) called "Mish" and the second called "Women's League". I need to save these reports separately as PDFs and they need to be saved in seperate locations. The first sheet (Mish) needs to be saved here: \\OS\OFS\Data\DayHab\Mish . The second sheet needs to be saved here: \\OS\OFS\Data\DayHab\Women's League . Here is the twist... Both sheets need to take their PDF name from their G3 cells. I have Excel 2010 and dont want to print from my Adobe PDF printer. I would like to save as a PDF so I can run it off of computers that dont have the Adobe PDF printer installed. I have searched through the forums and found some code to try out but nothing that I could really understand to change to fit my need here. Any help would be huge and greatly appreciated. I have come to this site many times before for some great advice and answers but this time I guess I have a tailored problem.

TIA

 

[Jhade2000]

I tried this code and it worked great, but what if I have 80 worksheets and the file name needs to say the tab name? Is there a way to do that?

I must have missed this answer as this is exactly what I need. I see where you got the name of the file and if I could get a list of variables in VB I could find the correct name to get the tab. Please either provide the name to variable or modify the code to pull the tab name instead of the workbook name.

Kind regards and Thank you

 

[swaz1]

Hi, the PDF is saved correctly, but can you tell me how to save the PDF with the name in cell A1?

Thanx

Any ideas?

 

[swaz1]

I guess this will work. Try:

[COLOR=Navy]Sub[/COLOR] Save_as_pdf()
[COLOR=Navy]Dim[/COLOR] FSO [COLOR=Navy]As[/COLOR] [COLOR=Navy]Object[/COLOR]
[COLOR=Navy]Dim[/COLOR] s(1) [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
[COLOR=Navy]Dim[/COLOR] sNewFilePath [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]

    [COLOR=Navy]Set[/COLOR] FSO = CreateObject("Scripting.FileSystemObject")
    s(0) = ThisWorkbook.FullName
    
    [COLOR=Navy]If[/COLOR] FSO.FileExists(s(0)) [COLOR=Navy]Then[/COLOR]
        [COLOR=SeaGreen]'//Change Excel Extension to PDF extension in FilePath[/COLOR]
        s(1) = FSO.GetExtensionName(s(0))
        [COLOR=Navy]If[/COLOR] s(1) <> "" [COLOR=Navy]Then[/COLOR]
            s(1) = "." & s(1)
            sNewFilePath = Replace(s(0), s(1), ".pdf")
            
            [COLOR=SeaGreen]'//Export to PDF with new File Path[/COLOR]
            ActiveSheet.ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=sNewFilePath, _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, OpenAfterPublish:=True
        [COLOR=Navy]End[/COLOR] [COLOR=Navy]If[/COLOR]
    [COLOR=Navy]Else[/COLOR]
        [COLOR=SeaGreen]'//Error: file path not found[/COLOR]
        MsgBox "Error: this workbook may be unsaved.  Please save and try again."
    [COLOR=Navy]End[/COLOR] [COLOR=Navy]If[/COLOR]
    
    [COLOR=Navy]Set[/COLOR] FSO = [COLOR=Navy]Nothing[/COLOR]

[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

It may seem overkill checking if your XL file exists but unsaved workbooks have no filepath so it's an error that could be very confusing at some later date.

The PDF is saved correctly, but can you tell me how to save the PDF with the name in cell A1?

 

[Atom Bomb]

The PDF is saved correctly, but can you tell me how to save the PDF with the name in cell A1?

This should work:
Dim savefilename As String
savefilename = ActiveSheet.Cells(row,column).Value 'assign your row and column number where the data is
sNewFilePath = savefilename & ".pdf"

 

[Jurok89]

This should work:
Dim savefilename As String
savefilename = ActiveSheet.Cells(row,column).Value 'assign your row and column number where the data is
sNewFilePath = savefilename & ".pdf"

Hi thx very much for the code. I just have one more question.
If i apply this code as a action for ClickButton which is for example in the Sheet1 and i want Sheet2 to convert to PDF file what should i do?
I´m trying to change the ActiveSheet command to Sheets("Sheet2") but this is not working.
Can you help me with this? Thanks.

 

[akinkaraman]

Hello it works fine. How can it save just for the selected area only by asking vertical or horizantal?

I guess this will work. Try:

[COLOR=Navy]Sub[/COLOR] Save_as_pdf()
[COLOR=Navy]Dim[/COLOR] FSO [COLOR=Navy]As[/COLOR] [COLOR=Navy]Object[/COLOR]
[COLOR=Navy]Dim[/COLOR] s(1) [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
[COLOR=Navy]Dim[/COLOR] sNewFilePath [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]

    [COLOR=Navy]Set[/COLOR] FSO = CreateObject("Scripting.FileSystemObject")
    s(0) = ThisWorkbook.FullName
    
    [COLOR=Navy]If[/COLOR] FSO.FileExists(s(0)) [COLOR=Navy]Then[/COLOR]
        [COLOR=SeaGreen]'//Change Excel Extension to PDF extension in FilePath[/COLOR]
        s(1) = FSO.GetExtensionName(s(0))
        [COLOR=Navy]If[/COLOR] s(1) <> "" [COLOR=Navy]Then[/COLOR]
            s(1) = "." & s(1)
            sNewFilePath = Replace(s(0), s(1), ".pdf")
            
            [COLOR=SeaGreen]'//Export to PDF with new File Path[/COLOR]
            ActiveSheet.ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=sNewFilePath, _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, OpenAfterPublish:=True
        [COLOR=Navy]End[/COLOR] [COLOR=Navy]If[/COLOR]
    [COLOR=Navy]Else[/COLOR]
        [COLOR=SeaGreen]'//Error: file path not found[/COLOR]
        MsgBox "Error: this workbook may be unsaved.  Please save and try again."
    [COLOR=Navy]End[/COLOR] [COLOR=Navy]If[/COLOR]
    
    [COLOR=Navy]Set[/COLOR] FSO = [COLOR=Navy]Nothing[/COLOR]

[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

It may seem overkill checking if your XL file exists but unsaved workbooks have no filepath so it's an error that could be very confusing at some later date.