Print to PDF, scaled to fit, name file with multiple cells contents

So I've got this workbook that's been working rather well for awhile now, but something new has just come up that I've never done before in VBA. It's been asked of me if I can modify this button to create a pdf instead of having it create another spreadsheet instead.

This pdf would need to be created within the same folder location as the active workbook, and be named based of cells within the active workbook. The cells which would provide the naming convention for the pdf file exist in this order... C3, E2, E3 & E4 (E3 & E4 would be concatenated). So the pdf file name would look something like the following... C3 & "-" & "Lot" & " " & E2 & "-" & "Plan" & " " & E3 & E4.pdf. Or with context... Job Name-Lot 25-Plan 2B.pdf

Also, would like for this newly created pdf to be created on a specified sheet size and scaled to fit (shrink).

With all of that said, could somebody point me in the right direction with modifying the code below to achieve this?

Never dealt with pdf export in VBA, but I'm willing to learn, and I greatly appreciate any help at all.

Thank you so much in advance.

Please Login or Register  to view this content.

Can I maybe still have it go through creating the new excel file off of the template file like it is doing currently, but then right away have it print that "new excel" file as a PDF, and then cancel the "new" excel workbook?

Now that I think about it, that would probably be the more accurate way to go about this considering that the look of the PDF needs to resemble the format of the "template" file (TWC 8.5x11 INT.xlsm).

So, maybe I could add some code at the end of this button that tells the "ThisWorkbook" to print to pdf using the filename created with ws.Range("C3"), ("E2"), ("E3"), ("E4"), like I mentioned earlier? But then it cancels the saving of the new excel file, and instead only saves the pdf?

I feel like this is possible, and most likely the best way to move forward with this, but... how do I do it?

Thanks again for letting me use this place as a sound board for ideas!

-Mike

If you record the macro, it should be something like this.

Please Login or Register  to view this content.

I've gotten myself to this point, but it's asking me to name the file and path myself with a typical windows dialog box. I need to tell this to name itself on it's own based off of my 'Dim as pdfName' and the path to the 'ThisWorkbook.path'. No pop-ups, no entry fields.

Any help?

Please Login or Register  to view this content.

What do I put in the location noted with arrows below....?

Please Login or Register  to view this content.

I gotta leave for the night. But I'll be back in 15-16 hours to get back on this.

I can't express enough my appreciation to anybody that is willing to give their free time in helping me out with this.

ExcelForum.com is a shining example of what people helping people all over the world is all about. Thank you ExcelForum for providing this sanctuary!! Godspeed.

-Mike in Cali

Please Login or Register  to view this content.

Originally Posted by Kenneth Hobson

Please Login or Register  to view this content.

I didn't think to put the 'ThisWorkbook.Path' into my 'pdfName'. This worked perfectly Kenneth. Thank you for the tip.

Now I have one last obstacle to clear before I'm complete.

I need my range selection of ("A1:M50") to be dynamic. I need to figure out how to have that range selection update itself based on data in the 'D' column.
Basically, search from D8:D500, find the row number that equals the last entry, and then modify the range entry with that row number for the 'M' column.
example: Range("A1:M(row number of last entry in col D)").

Here is my latest working code.

Please Login or Register  to view this content.

Figure it out...

The following code now successfully transfers data to an external excel template file, finds out how many rows there are with data, then adjusts the print range to include all rows with data, then prints the file to a pdf with a page that is 1 page tall and 1 page wide, and saves that pdf in the same folder as the original excel file resides in.

Please Login or Register  to view this content.

Consider this case closed.

Thanks again to Kenneth Hobson for your help.

-Mike in Cali