Macro to SaveAs PDF but alter the name

[AmandaM]

Hi all! I am comfortable with excel but diving into Visual Basic has been slow. I'm sorry if this question has been repeated a million times. My searches haven't found a workable answer.


We have our client files in excel .. example "M, Amanda Qrtly Rpt.xlsx" in 4 different folders. The first 4 tabs of each file are saved as a PDF and sent to clients each quarter.

I need to define a variable that gets the current file name/path so I can use it to save as a pdf and saves it in a holding file for review, and adds the quarter ending date. the result should be "M, Amanda Qrtly Rpt 9.30.19.pdf" and saved in the holding file. I can update the date in the macro each quarter.

I think I'm close... and while I know what I need, I have no idea how to write the "something" part to be the current file name. (Yes, I'm looking for classes or books but would love to finish the quarter using this shortcut.)

sub savePDF()
'
' savePDF Macro
'
    
'
    Sheets(Array("Summary", "Analysis", "Chart", "Invoice")).Select
    Sheets("Summary").Activate
    ChDir "G:\Client Documents\_PDF Holding"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "G:\Client Documents\_PDF Holding\" & something & " 9.30.19.pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True
End Sub

Any and all suggestions are greatly appreciated!!!

~ AmandaM

 

[James006]

Hi,

You are extremely close ...

While building your FileName, with concatenation, you have "something" defined as the name ...

and the date and ".pdf"

Regarding the date, as long as there is a rule... it can also be automatically defined ...

So the whole instruction would require NO intervention ...

Hope this will help

 

[AmandaM]

Thanks for responding! I need to wait on this as I do not even know how to define a variable for the file name yet. I love that setting the date can be done with rules and that will be a future project! I got too excited and ahead of myself with this one.

 

[AmandaM]

Turns out I hate waiting and I am better at excel so... I did this huge work around. I have about 60 accounts left this quarter so if it works on them, I'm good! Then I need to learn more VB for January's reports!

Sub savePDF()
'
'   savePDF Macro
'   works on accounts getting all four sections


    
    Sheets("Master Summary").Activate
    Range("J1").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "=CELL(""filename"")"
    Range("J1").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="\", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
        TrailingMinusNumbers:=True
    Range("J1:K1").Select
    Selection.Delete Shift:=xlToLeft
    Range("J1").Select
    Selection.TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="[", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "=LEFT(RC[1],LEN(RC[1])-20)"
    Range("J1").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("K1").Select
    Selection.ClearContents
    Range("J1").Select
    
    files = Range("J1")
          
    Sheets("Summary").Activate
    Range("H2").Select
    Sheets(Array("Summary", "Analysis", "Chart", "Invoice")).Select
    Sheets("Summary").Activate
    ChDir "G:\Client Documents\_PDF Holding"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "G:\Client Documents\_PDF Holding\" & files & " 9.30.19.pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True
                
    Sheets("Master Summary").Activate
    Range("J1").Select
    Selection.ClearContents
    Range("H1").Select
End Sub

 

[James006]

Congratulations ... !!!

What is so specific about your ' January's reports ' ?

 

[AmandaM]

Nothing too special... it is just the next round of quarterly reports. I'm working on making them faster, more efficient, and more accurate. I have 119 accounts and I've trimmed them from an hour down to 15 minutes. I have to plan everything around report time so learning and practicing macros really needs to be in-between reporting times, not during reporting like I did this time. Faster the reports go out, the faster the invoices can be filled and then we get paid!

 

[James006]

Hi,

Does it mean you would like a few hints to improve the performance of your above macro ...? :wink:

 

[AmandaM]

Absolutely!
I knew that was a broken way to get it done but it works and will continue to even if the file name changes. We do investment accounts so Individuals can become Joints & the reverse, 401ks can become IRAs, IRAs can become Bene IRAs... etc. So the file name will change if the account type changes. That macro will always call the current name into cells I know are blank because the Master Summary is an internal only tab that I manage & then delete the cells like they were never there. Poof!

 

[James006]

Well ...

Always a bit tricky to work on a macro ... without the file ...:wink:

Below is a first attempt at improving things

Sub savePDFV2()
        '   Revised on Oct 19, 219
        '   savePDF Macro
        '   works on accounts getting all four sections
            
            Application.Calculation = xlCalculationManual
            Application.ScreenUpdating = False
            
            Dim RefDate As String
            RefDate = "9.30.19"
            
            Sheets("Master Summary").Activate
            Range("J1").FormulaR1C1 = "=CELL(""filename"")"
            Range("J1").Value = Range("J1").Value
            
            Range("J1").TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
                Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
                :="\", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
                TrailingMinusNumbers:=True
            Range("J1:K1").Delete Shift:=xlToLeft
            Range("J1").TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
                Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
                :="[", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
            Range("J1").FormulaR1C1 = "=LEFT(RC[1],LEN(RC[1])-20)"
            Range("J1").Value = Range("J1").Value
            Range("K1").ClearContents
            Files = Range("J1")
                  
            Sheets("Summary").Activate
            Range("H2").Select
            Sheets(Array("Summary", "Analysis", "Chart", "Invoice")).Select
            Sheets("Summary").Activate
            ChDir "G:\Client Documents\_PDF Holding"
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                "G:\Client Documents\_PDF Holding\" & Files & " " & RefDate & ".pdf", _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
                :=False, OpenAfterPublish:=True
                        
            Sheets("Master Summary").Activate
            Range("J1").ClearContents
            Range("H1").Select
                    
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True
            
        End Sub

Hope this will help

 

[AmandaM]

That's slick!
I can only work on macros when I'm ahead & my boss caught up over the weekend! That won't be happening next quarter. Haha

THANK YOU!!!