none
Macro to add text to Save As PDF filename RRS feed

  • Question

  • Terry XU MSFT kindly helped me out by providing this macro to add the text 'APPT' to the PDF filename. For some reason I can't seem to get this to work and add the text APPT to the PDF filename.

    Any of you kind people out there offer a solution - my macro skills are pitiful!

    Sub Test()
    UserNameStr = Worksheets("INFO Capture").Range("J10").Value
    DateStr = Format(Worksheets("INFO Capture").Range("K2").Value, "YYYYMMDD")
    FileNameStr = DateStr & " " & UserNameStr
    PDFNameStr = DateStr & " " & UserNameStr & " APPT"
    FolderNameStr = DateStr & " " & UserNameStr
    FullFolderStr = "C:\Users\terryx\Desktop\TestFolder\" & FolderNameStr
        
        'if the folder does not exist, create the folder
        Set FSO = CreateObject("Scripting.FileSystemObject")
        If FSO.FolderExists(FullFolderStr) = False Then
            MkDir FullFolderStr
        End If
    ActiveWorkbook.Worksheets("APPT LETTER").ExportAsFixedFormat xlTypePDF, FullFolderStr & "\" & FolderNameStr
    ActiveWorkbook.SaveAs FullFolderStr & "\" & FileNameStr
    End Sub

    Saturday, May 5, 2018 5:30 AM

Answers

  • Change the line

    ActiveWorkbook.Worksheets("APPT LETTER").ExportAsFixedFormat xlTypePDF, FullFolderStr & "\" & FolderNameStr

    to

    ActiveWorkbook.Worksheets("APPT LETTER").ExportAsFixedFormat xlTypePDF, FullFolderStr & "\" & PDFNameStr


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by T6NYY Sunday, May 6, 2018 5:54 AM
    Saturday, May 5, 2018 9:03 AM

All replies

  • The path "C:\Users\terryx\Desktop\TestFolder\" refers to a folder named TestFolder on Terry Xu's desktop. You should replace terryx with your username, and - if you haven't done that already - create a folder named TestFolder on your desktop.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, May 5, 2018 7:12 AM
  • Hi Hans

    My apologies, I have changed the path to the correct destination. The macro executes, it creates a PDF version of the worksheet and saves it in the correct location, but does not append 'APPT' to the filename.

    The PDF filename should be in the format YYYYMMDD SURNAME APPT.PDF

    Do you have solution?

    Tony

    Saturday, May 5, 2018 8:12 AM
  • Change the line

    ActiveWorkbook.Worksheets("APPT LETTER").ExportAsFixedFormat xlTypePDF, FullFolderStr & "\" & FolderNameStr

    to

    ActiveWorkbook.Worksheets("APPT LETTER").ExportAsFixedFormat xlTypePDF, FullFolderStr & "\" & PDFNameStr


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by T6NYY Sunday, May 6, 2018 5:54 AM
    Saturday, May 5, 2018 9:03 AM
  • Hans

    Your modification worked like a dream.

    Many thanks, much appreciated

    Tony

    Sunday, May 6, 2018 5:54 AM