VBA code to close an open instance(window) of an Adobe .pdf file (or prevent it from opening)

jfarc

Active Member
Joined
Mar 30, 2007
Messages
316
I have the following VBA code in an Excel file that asks the user to find the active Adobe PDF printer, then Prints the Active Excel worksheet(which has just been 'Select and Copy' to a new un-named workbook, then creating an Adobe .pdf file. I would rather not force the user to have to then manually close the Adobe window that automatically opens when the .pdf file is created.

Code:
        MsgBox "This routine must print to an Adobe PDF Printer." & vbCr & vbCr & _
                "Please select your assigned Adobe PDF."
        Application.Dialogs(xlDialogPrinterSetup).Show

        Sheets("Test").Select
        Sheets("Test").Copy
        
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

        Application.DisplayAlerts = False
        ActiveWindow.Close SaveChanges:=False
        Application.DisplayAlerts = True

I would like to do 1 of 2 things. Either have code in my routine that prevents Adobe from opening up the window after it Saves the .pdf file, or after it opens it up, to then simply close that opened Adobe window.

And as a bonus, two more things would be really nice:
1) Is there a way to replace the [Msgbox.... & .....DialogPrinterSetup] code above, which asks the user to select the Adobe pdf printer that would find and select it itself.

2) Preset the folder path & file name of the .pdf file that will be saved when the [ActiveWindow.SelectedSheets.PrintOut] line is run. It seems that no matter what I do, the Folder Path that is defaulted to is whatever the last .pdf file has been printed by the PC that this code is run on. I guess this is a function of Adobe to save this default each time.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Forum statistics

Threads
1,214,599
Messages
6,120,448
Members
448,966
Latest member
DannyC96

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
Back
Top