Results 1 to 8 of 8

Thread: Modifying Macro to add Column B to file name when exporting
Thanks Thanks: 0 Likes Likes: 0

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

    Default Modifying Macro to add Column B to file name when exporting

    Hello - I found this code on the message board which works great for my purposes, expect that I am hoping to include the value in Column C to the file name when exporting.

    Code is below:

    Code:
    Sub exportPages()
    
    Set Sht = Worksheets("Sheet1")
    ' The variable Sht now holds the sheet that should be printed, so we can use that in the macro instead of repeating Worksheets("Sheet1")
    ExportDir = "C:\temp"
    ' A string/text with the export directory, should end with a \
    NrPages = Sht.HPageBreaks.Count + 1
    ' Using the Sht object, count the number of horizontal page breaks and add one to know the number of pages
    For p = 1 To NrPages
        ' Loop though the pages, actually the loop is only there as a kind of "counter"
        If p = 1 Then
            RwStart = 1
            ' The first page starts at the top and has no page break before it, so the row with your name is 1
        Else
            RwStart = Sht.HPageBreaks(p - 1).Location.Row
            ' Page 2 starts after Pagebreak 1, this code finds the row of the page break (=the row directly after it)
        End If
        
        FoundName = Sht.Range("B" & RwStart).Value
        ExportName = "Export_" & FoundName & "_" & p & ".pdf"
        ' Create a variable with the name in the sheet
        
        Sht.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ExportDir & ExportName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, From:=p, to:=p, OpenAfterPublish:=False
        ' Export the current page with the current name to the default location
    Next
    Set Sht = Nothing
    ' Clean up variables
    End Sub
    Last edited by Fluff; Jun 25th, 2019 at 10:24 AM. Reason: Added code tags & updated op

  2. #2
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,850
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Modifying Marco to add Column B to file name when exporting

    if you replicate this >
    " & FoundName & " for your new name part that would work
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

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

    Default Re: Modifying Marco to add Column B to file name when exporting

    Thank you, I am not very familiar with macros, so I am not sure exactly where I would add this comment.

  4. #4
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,850
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Modifying Marco to add Column B to file name when exporting

    so where is the value you need to include ?
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  5. #5
    New Member
    Join Date
    Sep 2017
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Modifying Marco to add Column B to file name when exporting

    Column C of the same line that the value from Column B is included

  6. #6
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,850
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Modifying Marco to add Column B to file name when exporting

    FoundName = Sht.Range("B" & RwStart).Value
    FoundName2 = Sht.Range("C" & RwStart).Value
    ExportName = "Export_" & FoundName & " " & FoundName2 & "_" & p & ".pdf"
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  7. #7
    New Member
    Join Date
    Sep 2017
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Modifying Marco to add Column B to file name when exporting

    This is great - however, I discovered that I now have a different problem. The file name that it is saving for example "West Manager" & "Employee One" (Values contained in Columns B &C) - does not match with the manager and employee name from the exported file.

    How can I adjust this so that the file name is compiled from the values in Column B + Column C from directly above the page break.

  8. #8
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,850
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Modifying Marco to add Column B to file name when exporting

    static page break is fine, don't know how you determine from code where they will be
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

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
  •