Need a little help with print (PDF) and VB combo

Burrgogi

Active Member
Joined
Nov 3, 2005
Messages
250
I've recorded a macro in MS Excel that selects a virtual printer on my Win7 machine. It directs the output to a folder on my hard drive here: E:\PDF conversions\

This special printer is actually a virtual printer that can take any file and convert it to a PDF file. Problem is that it always takes on the name of the application that I happen to be working with. In this case, if I'm working on a Excel file named "Household Budget 2020", the Ghost printer will generate a PDF file with the following name: "Microsoft Excel - Household Budget 2020.xlsx.pdf"

If I'm working with MS Notepad, and print from there, the filename will read as: "Notepad - something_something.txt.pdf" I think you get the idea.

I don't like all that extra stuff in the file name so I always end up renaming it manually like this: Household Budget 2020.pdf

I would love to be able to have a macro that I can call upon that will do the following:
1) Take the active open workbook and print to the virtual printer as shown in my screenshot (attached to my post).
2) Go to the output directory (E:\PDF conversions)
3) Move the newly created PDF file to: E:\Excel Workbooks\Household STuff\
4) Rename the file by removing "Microsoft Excel -" and ".xlxs" from the middle of the filename so that it becomes "{%VARIABLE%}.pdf" **

** Obviously the filename will vary and will not always be "Household Budget 2020" - I just used that in my example for the post here.

Running Win7 OS and MS 2010.

EDIT:

Before all the comments come in, I do realize that MS Office already has a "Save As PDF" functionality built-in already. For reasons that I don't want to get into right now, there are times when I need to (prefer) to print to this other virtual printer instead. I'd appreciate your help.
 

Attachments

  • Excel switch printer v2.png
    Excel switch printer v2.png
    132.2 KB · Views: 3
Last edited:

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Watch MrExcel Video

Forum statistics

Threads
1,122,240
Messages
5,595,009
Members
413,958
Latest member
SBM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top