excel - Unable to save as PDF from VBA in mac

Question

I am trying to save a range from a sheet as PDF through VBA. Following is the code that I have written.

Sheets("PO Format").PageSetup.PrintArea = "$B$6 : $J$42"
Sheets("PO Format").Range("B6:J42").ExportAsFixedFormat Type:=xlTypePDF, Filename:=Sheets("User Settings").Range("B15") & "/" & Sheets("PO Format").Range("F7"), Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

The weird thing is that it is working fine on one system and I able to save it at the mentioned path and then also send out a mail, but while testing it on another system I am getting the error as "Error while printing". Completely clueless. Can anyone help??

Answer 1

In Mac Office 2016 Microsoft have to deal with Apple’s sandbox requirements due to which VBA needs permission to access folders and this is not prompted while trying to save via VBA instead it gives an error. But there are a few places on Mac that one can use to let the code do what it needs to do without user interaction.

one such path is /Users/username/Library/Group Containers/UBF8T346G9.Office This folder gets created when Office 2016 is installed.

So I saved the PDF to this folder, moved it to the desired location and it is working fine now.

For more details and the code go through http://www.rondebruin.nl/mac/mac034.htm