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?

 

[Joakim89]

I'm using a personal macro workbook for my macro's, using this the pdf is saved with the name "PERSONAL" and in the same folder as the personal macro workbook. Is it possible to circumvent this in anyway? I want to save it with the name of the current Excel file i'm in the folder in which the file resides. Can you help me? I would appriciate it a lot, I'm very new to this macro thing

 

[akinkaraman]

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.

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

Can anyone help?

 

[haroemer]

Hello,
the proposed MAkro worked fine, thank you! What I need is to create a Makro which saves a PDF based on specific selection of cells, whereas the name and folder of the PDF have to be obtained dynamically from defined cells in the open worksheet.
I guess a simple recording (instead of coding) is not possible...

Best regards,
haroemer