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

Burrgogi

Active Member
Joined
Nov 3, 2005
Messages
277
Office Version
  1. 2010
Platform
  1. Windows
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: 9
Last edited:

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,532
Messages
5,832,286
Members
430,126
Latest member
bwatts

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