how to use VBA to publish an excel worksheet as XPS document and save with a custom filename?

cringey

New Member
Joined
Jul 3, 2012
Messages
1
I would like to create a macro to produce an XPS document from an excel worksheet that can then be saved with the anme of a value in a cell in another worksheet.

I need to use the print to microsoft office XPS document writer to create the XPS file as the document loses format when using the Save as or export as file type function.

The macro below enables me to publish as xps and open the save as dialogue box but as I intend to do this for many worksheets I would like to be able to automate the saving of each worksheet with a specific file name.

If anyone could advise how to modify the macro below to automate the saving process it would be much appreciated.
Dim i As Integer
For i = 1 To 3

Sheets("Search").Select
Range("C1").Select
ActiveCell.FormulaR1C1 = i
Sheets("Output Page").Select
Application.ActivePrinter = "Microsoft XPS Document Writer on Ne00:"
ExecuteExcel4Macro _
"PRINT(2,1,2,1,,,,,,,,2,""Microsoft XPS Document Writer on Ne00:"",,TRUE,,FALSE)"
Next i
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

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