sub routine to save a 'range' as a PDF file

[martinargimon]

Hi Chandoo !
I have attached my excel file for reference purposes.
In brief, my problem is the 'syntax' (file path name), to ensure that the printed range is saved as a PDF, in the folder corresponding to the value selected in cell "C29". ( see screen capture example below).
I have created folders for each of the UNIT number (which they will match whatever unit i select with my filter in cell C29).
I would like the 'path' to pick this UNIT number (in cell 'C29" dynamically), so that the file path name contains the unit number that corresponds with the repective folder.
Kindly review my code in the sheet 'Owner_Payment_Remittance". (the first subroutine works 100% ) right , although the second one ( commented in green) does NOT work).
Many Thanks. Regards. Martin

 

[Marc L]

Hi, according to what I could decypher so just removing the useless a VBA procedure for starters :​

Private Sub Cmdbutton_SaveRemittance_Click()
    Const C = "C:\Users\martin argimon\OneDrive - CMA (1)\Documents - CMA Realty\LMV Units\Units for Rent\#\Remittance_#"
    [A1:J78].ExportAsFixedFormat 0, Replace(C, "#", [C29]) & Format([H13], " yyyy-mm-dd "), 0, , , , , True
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[martinargimon]

Thanks Marc,
a bit difficult for me to understand as you have introduced several 'very advanced ( for me) , code. But i will try it and I appreciate and thank you for your time.
Many thanks again
Kinmd Regards. MArtin

 

[Marc L]

Don't worry as it's basics only so all is explained in VBA help …​

​

 

[martinargimon]

Hi Marc
I've tried your suggested code, it and it works perfectly well.
You understood 100% what i wanted to achieve. Thanks very much once again. Is there an internet document where i could get the explanation in a bit more detail of the code that you have used. Although is basic there are some commands ( such as replace) and the use of the # sign that i was unaware how they work. let me know if i can find anything to teach me a bit more on these topics. Kind regards. You help is greatly appreciated.
Martin

 

[Marc L]

It's first a conception context : « what to do from where to where, what I need to achieve it ? »​

Normally I use an unique codeline to save an Excel Range to a .pdf file but here, for readability, I split it in two.​

So the first codeline declares a path constant, the cell C29 variable part is qualified with # character.​

The reason why VBA Replace text function is used to allocate this cell content.​

As / character is forbidden within a Windows path the reason why VBA Format text function is used to add the date to the filename …​

​

Excel VBA - Information, Tutorials, Examples & Resources » Chandoo.org - Learn Excel, Power BI & Charting Online

Excel VBA – Information, Tutorials, Examples & Resources Excel VBA allows you to automate various activities you do in Excel. We use Excel to analyze data, create reports, prepare charts & presentations, do calculations and understand information. When we are doing all these, we spend quite some...

chandoo.org

 

[martinargimon]

Thanks Marc. Very clear. I have been going thru' a few 'You Tube' VBA basic tutorials so far. I still have a long way to go.
Once again thanks and all the best
Kind regards. Martin