Converting a worksheet to PDF using VBA PrintOut method

Question

I am trying to convert a specific worksheet to PDF using a VBA macro using PrintOut method. I want that when the macro is run, the active worksheet is converted to PDF and saved down to a specific location on SharePoint with the filename same as that of the workbook.

I have the following code:

Target_File_Draft = ActiveSheet.Range("AJ21").Value & "\" & ActiveSheet.Range("AJ24").Value

*'Cell AJ21 contains the path to the SharePoint folder and AJ24 contains the name of the file. So Target_File_Draft takes on a value like: https://contoso.sharepoint.com/Site/ABClibrary/ABCFolder/FileXYZ.pdf*

' Print Draft Report

ActiveSheet.PrintOut ActivePrinter:="Microsoft Print to PDF", PrintToFile:=True, PrToFileName:=Target_File_Draft

When I run this macro, I get

Run-time error 1004: Your file could not be printed due to an error on Microsoft Print to PDF on e01

There are several possible reasons:

• There may not be enough memory available. Try closing files etc
• If you use a network for printing, there may be a problem with the network etc

I don't think that either of the two reasons apply in my case.

I did some investigation, the problem is to do with the PrToFileName attribute. It seems they way I am passing a value to this variable is not correct. Does anyone know of a way how I can specify the filepath as well as filename while using printout method?

I am using Windows 10, running the macro from Excel 2016, working on workbooks on SharePoint Online and trying to save the PDF back to SharePoint Online.

PS: I can't use 'save as pdf' or 'export as pdf' methods in my macro because of other limitations (they don't work with Digital Rights Management).

Answer 1

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ActiveWorkbook.Path & "\" & Desktop & "Monthly.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True