Results 1 to 10 of 10

Thread: VBA/Macro to save as a pdf with a designated path and file name

  1. #1
    New Member
    Join Date
    Feb 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA/Macro to save as a pdf with a designated path and file name

    I am extremely new to this, but this is what I have right now. This changes the name of the invoice based on a cell value. Is it possible to also change the path based on a cell value? (the bold area)


    Sub Button1_Click()
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "C:\Receivable\Invoices\Coco" & Range("K9").Text & ".pdf", Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
    True
    End Sub

  2. #2
    New Member
    Join Date
    Feb 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA/Macro to save as a pdf with a designated path and file name

    There is a \ following CoCo also, I must have deleted

  3. #3
    Board Regular
    Join Date
    Oct 2007
    Posts
    5,835
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA/Macro to save as a pdf with a designated path and file name

    Assuming K8 contains the name of the subfolder (Coco in this case):
    Code:
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "C:\Receivable\Invoices\" & Range("K8").Text & "\" & Range("K9").Text & ".pdf", Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

  4. #4
    New Member
    Join Date
    Feb 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Re: VBA/Macro to save as a pdf with a designated path and file name

    Thanks so much John_w! You are awesome!!

  5. #5
    New Member
    Join Date
    Feb 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA/Macro to save as a pdf with a designated path and file name

    So this appeared to be working but when that cell value changes it will not save to that file name and throws up a debug error. It only works with one value. Any idea on why it is doing that?

  6. #6
    Board Regular
    Join Date
    Oct 2007
    Posts
    5,835
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA/Macro to save as a pdf with a designated path and file name

    What is the cell value? What is the error?

  7. #7
    New Member
    Join Date
    Feb 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA/Macro to save as a pdf with a designated path and file name

    The cell value is referencing a pivot table with a vendor name in it. This link only works with one of the vendors. It is giving me a Run-time error '1004' document not saved. the document may be open, or an error may have been encountered when saving. Then it gives me the option to end or debug. The error does not go away when I physically type a different vendor name. I also double-checked that the file names were correct for the other vendors.

  8. #8
    Board Regular
    Join Date
    Oct 2007
    Posts
    5,835
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA/Macro to save as a pdf with a designated path and file name

    Are the K8 and K9 values valid folder and file names?

    Does the K8 subfolder exist? You would get that error if it doesn't. Insert this as the first line:
    Code:
        If Dir("C:\Receivable\Invoices\" & Range("K8").Text) = vbNullString Then MkDir "C:\Receivable\Invoices\" & Range("K8").Text

  9. #9
    New Member
    Join Date
    Feb 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA/Macro to save as a pdf with a designated path and file name

    I cannot get this code to work I am not sure if I am doing it right, I am getting a compile error now.



    Sub Button2_Click()
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:=_
    If Dir("C:\Receivable\Invoices" & Range("C88").Text) = vbNullString Then MkDir "C:\Receivable\Invoices" & Range("C88").Text" & Range("H82").Text & ".pdf", Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=
    True

    End Sub

    I checked the file names for all the vendors and they are all there.

  10. #10
    New Member
    Join Date
    Feb 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA/Macro to save as a pdf with a designated path and file name

    So, I think I figured it out. The data that the pivot table was pulling from apparently had a space after the vendor name. :/ user error on my part. Bad habit of hitting the space bar.

Some videos you may like

User Tag List

Tags for this Thread

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
  •