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?

 

[jlajla24]

Any suggestions?

I've found the macro to automatically save to PDF and to point to a specific directory in doing so. But how to I automatically name the new PDF file as the same name of the excel file that it's saving from?

 

[VoG]

Can you post the code that you currently have.

 

[jlajla24]

This works fine as long as I have a name in the "filename" position at the end of the directory string. Is there a phrase or something to input there that will automatically name the new PDF file as the current xls file?

Sub Save_as_pdf()
'
' Save_as_pdf Macro
' Saves as a PDF
'
' Keyboard Shortcut: Ctrl+Shift+P
'
ChDir "\\D001fs003\documents\joe.collison\My Documents\GSA\GSA Proposals\PDF"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"\\D001fs003\documents\joe.collison\My Documents\GSA\GSA Proposals\PDF\filename" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
End Sub

 

[xenou]

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.

 

[jlajla24]

That works perfectly. Now what if I wanted to keep the same file name but save to a subfolder of where the XL file is located?

 

[xenou]

It depends on if you know the subfolder name (it is static and always the same) or if you want to dynamically pick a subfolder. In the former case we re-write the code for where you really want to save the file. For the latter we rewrite the code to use a folder picker.

ξ

 

[snowball6750]

I recently copied your macro and it worked magically. I'm very novice with excel and macros and i'm trying to learn it. However, i wondered if you could help by also teaching me how do I include using the same macro but making the options under saving to pdf to save the entire workbook vs. saving only one sheet at a time? I would greatly appreciate the assistance.

 

[jjames]

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?

 

[kepyas2012]

Sir thank you so much for your codes!

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.