Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Macro for Save As PDF Excel 2010

This is a discussion on Macro for Save As PDF Excel 2010 within the Excel Questions forums, part of the Question Forums category; I've done some searching and found alot about this but the options are always for an auto naming the file ...

  1. #1
    New Member
    Join Date
    Feb 2005
    Posts
    44

    Default Macro for Save As PDF Excel 2010

    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?

  2. #2
    New Member
    Join Date
    Feb 2005
    Posts
    44

    Default Re: Macro for Save As PDF Excel 2010

    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?

  3. #3
    VoG
    VoG is online now
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    62,219

    Default Re: Macro for Save As PDF Excel 2010

    Can you post the code that you currently have.
    HTH, Peter
    Please test any code on a copy of your workbook.

  4. #4
    New Member
    Join Date
    Feb 2005
    Posts
    44

    Default Re: Macro for Save As PDF Excel 2010

    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

  5. #5
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,056

    Default Re: Macro for Save As PDF Excel 2010

    I guess this will work. Try:

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

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  6. #6
    New Member
    Join Date
    Feb 2005
    Posts
    44

    Default Re: Macro for Save As PDF Excel 2010

    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?

  7. #7
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,056

    Default Re: Macro for Save As PDF Excel 2010

    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.

    ξ

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  8. #8
    New Member
    Join Date
    Feb 2011
    Posts
    1

    Default Re: Macro for Save As PDF Excel 2010

    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.

  9. #9
    New Member
    Join Date
    Jan 2012
    Posts
    1

    Default Re: Macro for Save As PDF Excel 2010

    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?

  10. #10
    New Member
    Join Date
    Nov 2012
    Posts
    1

    Default Re: Macro for Save As PDF Excel 2010

    Sir thank you so much for your codes!


    Quote Originally Posted by xenou View Post
    I guess this will work. Try:

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

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com