Excel to PDF Naming convention
Results 1 to 9 of 9

Thread: Excel to PDF Naming convention
Thanks Thanks: 0 Likes Likes: 0

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

    Default Excel to PDF Naming convention

    Hi. I have the macro below which upon running converts the active sheet in excel to pdf.

    Code:
    Sub SaveasPDF()
    
    
    Dim wsA As Worksheet
    Dim wbA As Workbook
    Dim strName As String
    Dim strPath As String
    Dim strFile As String
    Dim strPathFile As String
    Dim myFile As Variant
    On Error GoTo errHandler
    
    Set wbA = ActiveWorkbook
    Set wsA = ActiveSheet
    
    'get active workbook folder, if saved
    strPath = wbA.Path
    If strPath = "" Then
      strPath = Application.DefaultFilePath
    End If
    strPath = strPath & ""
    
    strName = Format (Now(), "WORK AS OF MM-DD-YYYY")
    
    'create default name for savng file
    strFile = strName & ".pdf"
    strPathFile = strPath & strFile
    
    'export to PDF in current folder
        wsA.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=strPathFile, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
        'confirmation message with file info
        MsgBox "PDF file has been created: " _
          & vbCrLf _
          & strPathFile
    
    exitHandler:
        Exit Sub
    errHandler:
        MsgBox "Could not create PDF file"
        Resume exitHandler
    End Sub
    2 Issues I'm running into:

    1. The pdf name comes in weird numbers. Example, instead of WORK AS OF 06-22-2019, it comes as 6ORK AS OF 06-21-2019.

    2. There is a sheet call 'total' in my workbook which I want to be automatically included every-time while converting to pdf along with the active sheet that they will run the macro on.

    Please help.

    Thank you in advance.
    Last edited by Fluff; Jun 22nd, 2019 at 10:17 AM. Reason: code tags

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,897
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Excel to PDF Naming convention

    Hi & welcome to MrExcel.
    Try
    Code:
    strName = "WORK AS OF " & Format(Date, "MM-DD-YYYY")
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,897
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Excel to PDF Naming convention

    And to print both sheets try
    Code:
    Sheets(Array("Totals", wsA.Name)).Select
    wsA.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            FileName:="+Testing.pdf", _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    New Member
    Join Date
    Jun 2019
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel to PDF Naming convention

    It worked. Thanks. Any luck on the 2nd issue?

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,897
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Excel to PDF Naming convention

    See post#3
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    New Member
    Join Date
    Jun 2019
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel to PDF Naming convention

    I don't want to hard code my sheet name because the excel has 30 sheets. Our users can run macro from any sheet. I just want the active sheet + 'total' sheet to be converted into pdf.

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,897
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Excel to PDF Naming convention

    I just want the active sheet + 'total' sheet to be converted into pdf.
    That's what it does
    Just add the line in red to your code as shown
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    New Member
    Join Date
    Jun 2019
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel to PDF Naming convention

    Yayy! Worked perfectly. Thank you very much

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,897
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Excel to PDF Naming convention

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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
  •