Results 1 to 4 of 4

Thread: export file as PDF but with the name as mentioned in cell D27 in sheet "Faktuur"
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2015
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default export file as PDF but with the name as mentioned in cell D27 in sheet "Faktuur"

    The macro currently has the following

    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "V:\ingrid\Book9.pdf", Quality:=xlQualityStandard, IncludeDocProperties:= _
    True, IgnorePrintAreas:=False, OpenAfterPublish:=False

    I want this to be changed as such that it takes the value from cell D27 in tab "Faktuur".

    Also note that in cell D27 there is a formula (=Fact_nr), so the name of the to be exported PDF should be the outcome of the formula in cell D27 e.g. "ZK2019/SBBZo06"

    I hope my question is clear.

  2. #2
    MrExcel MVP
    Join Date
    Oct 2007
    Posts
    5,897
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    2 Thread(s)

    Default Re: export file as PDF but with the name as mentioned in cell D27 in sheet "Faktuur"

    The "/" in "ZK2019/SBBZo06" is not a valid character in a file name, because it represents a subfolder separator. This code therefore replaces the "/" with "_" to create V:\ingrid\ZK2019_SBBZo06.pdf.

    Code:
        Dim PDFfile As String
        PDFfile = "V:\ingrid\" & Replace(Worksheets("Faktuur").Range("D27").Value, "/", "_") & ".pdf"
        ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

  3. #3
    New Member
    Join Date
    Jun 2015
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post Re: export file as PDF but with the name as mentioned in cell D27 in sheet "Faktuur"

    Quote Originally Posted by John_w View Post
    The "/" in "ZK2019/SBBZo06" is not a valid character in a file name, because it represents a subfolder separator. This code therefore replaces the "/" with "_" to create V:\ingrid\ZK2019_SBBZo06.pdf.

    Code:
        Dim PDFfile As String
        PDFfile = "V:\ingrid\" & Replace(Worksheets("Faktuur").Range("D27").Value, "/", "_") & ".pdf"
        ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    Thanks John.

    Q1: after adjusting the macro I get a Run-time error '-2147024773 (8007007b)':
    Document not saved

    Any idea what could be causing this?

    Q2: How should the above be written exactly in case of the Invoice-numbers already having the "_" instead of the "/"
    Thanks in advance

  4. #4
    MrExcel MVP
    Join Date
    Oct 2007
    Posts
    5,897
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    2 Thread(s)

    Default Re: export file as PDF but with the name as mentioned in cell D27 in sheet "Faktuur"

    1. Don't know. Is it a valid file name? Add:
    Code:
    MsgBox PDFfile
    2.
    Code:
    PDFfile = "V:\ingrid\" & Worksheets("Faktuur").Range("D27").Value & ".pdf"

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
  •