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.
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.
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.