Results 1 to 6 of 6

Thread: VB code to save as PDF with a changeable file name

  1. #1

    Thread Starter
    New Member
    Join Date
    Jun 2020
    Posts
    3

    VB code to save as PDF with a changeable file name

    Hi,

    I've written a macro to send an excel sheet to save as a PDF. I've got a specific (changable) name that I'd like it to save as but I can't quite work out what command to give it so that it pastes the name (which I've copied in excel in the macro - called "Filename_Paste") as the new file name.

    For example my PDF saves onto my desktop as "Filename_Paste.PDF" whereas I'd like to save as "Simon and Betty Anderson 3-6-2020" which is what I've just copied in the macro using Filename_Paste as a named range.

    I'm also trying to work out how would I get the file to save on the users desktop, at the moment it works for my desktop but this will not work when I hand it to one of my colleagues machines.

    Code is as follows:


    Code:
    Sub Macro4()
    '
    ' Macro4 Macro
    '
    '
        Range("Filename_Copy").Select
        Selection.Copy
        Range("Filename_Paste").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("Filename_Paste").Select
        Application.CutCopyMode = False
         
        Selection.Copy
        Application.CutCopyMode = False
        ChDir "C:\Users\jb\Desktop"
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "C:\Users\jb\Desktop" & "Filename_Paste", Quality:= _
            xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
            OpenAfterPublish:=True
        Range("A1").Select
    End Sub
    Any help would be appreciated!

    Col
    Last edited by dday9; Jun 16th, 2020 at 08:35 AM.

  2. #2
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,392

    Re: VB code to save as PDF with a changeable file name

    Methods to get path to user desktop:

    http://learnexcelmacro.com/wp/2012/1...h-excel-macro/

    To change the name of the saved file, replace the Filename_Paste with the actual name that you want. Where is the "Simon and Betty Anderson 3-6-2020" information stored?

  3. #3

    Thread Starter
    New Member
    Join Date
    Jun 2020
    Posts
    3

    Re: VB code to save as PDF with a changeable file name

    Quote Originally Posted by jdc2000 View Post
    Methods to get path to user desktop:

    http://learnexcelmacro.com/wp/2012/1...h-excel-macro/

    To change the name of the saved file, replace the Filename_Paste with the actual name that you want. Where is the "Simon and Betty Anderson 3-6-2020" information stored?


    Thanks "Simon and Betty Anderson 3-6-2020" is stored in the named range called "Filename_Paste"

  4. #4
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,392

    Re: VB code to save as PDF with a changeable file name

    Where in the Range would find it? Is it in a cell, and, if so, how would you locate that cell?
    Once you know where the data is located, you can change the code to use that data as the file name.

  5. #5

    Thread Starter
    New Member
    Join Date
    Jun 2020
    Posts
    3

    Re: VB code to save as PDF with a changeable file name

    Yes is in a cell, cell D43 and I named that cell as a named range "Filename_Paste". The actual data that sits in that cell is "Simon and Betty Anderson 3-6-2020" but that will change every time I hit the Macro.

    I'm just confused on how to apply the code to my situation at hand..

  6. #6
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,392

    Re: VB code to save as PDF with a changeable file name

    Possibly useful example:

    Code:
    Sub Macro4()
    '
    ' Macro4 Macro
    '
    '
        Dim strFileName as String
        strFileName = CStr(ActiveSheet.Cells(4, 43))
    
        Range("Filename_Copy").Select
        Selection.Copy
        Range("Filename_Paste").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("Filename_Paste").Select
        Application.CutCopyMode = False
         
        Selection.Copy
        Application.CutCopyMode = False
        ChDir "C:\Users\jb\Desktop"
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "C:\Users\jb\Desktop" & strFileName, Quality:= _
            xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
            OpenAfterPublish:=True
        Range("A1").Select
    End Sub

Posting Permissions

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



Click Here to Expand Forum to Full Width