CODEWITHSUNDEEP
excelexcel-2016vba
inm
inm

Reputation: 21

Error While Printing

My macro is shown below. I am trying to save my excel sheet to a pdf document. When I run my macro I get an "error while printing" message, followed by: Run-time error '1004': Application-defined or object-defined error

I am running Microsoft Excel for Mac version 15.13.1.

This is my Macro. What is wrong?

Sub SAVE_DIRECTORY_IN_PDF_FORMAT()
Dim DIRECTORY As Worksheet
Dim DIRECTORY2 As Worksheet

Sheets("DIRECTORY").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:="DIRECTORY.pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=False

Sheets("DIRECTORY2").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:="DIRECTORY with Emails.pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub

Upvotes: 2

Views: 3968

Answers (4)

Pavel Charny
Pavel Charny

Reputation: 1

The solution with overwriting PDF still doesn't work with latest update 15.15 of MS Excel 2016 producing a message of "Error while printing" - 2011 works fine. Why they decided to change this VBA system - God only knows

Upvotes: 0

barneyb
barneyb

Reputation: 11

Just came up against the same problem, which I agree seems to be a problem with Excel 2016 for Mac, and I think have a solution (albeit incredibly inelegant).

The problem seems to go away if you're overwriting an existing pdf file rather than creating a new one. So solution was to do a SaveAs on the workbook using the desired .pdf path, then do the ExportAsFixedFormat to the same path, then resave the workbook in its original path (if desired)...

Dim currentPath As String

currentPath = Application.ActiveWorkbook.FullName

ActiveWorkbook.SaveAs Filename:="DIRECTORY.pdf"

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:="DIRECTORY.pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=False

ActiveWorkbook.SaveAs Filename:=currentPath

Upvotes: 1

inm
inm

Reputation: 21

Thanks to everyone that responded. I found the answer to the macro problem. Excel 2016 for Mac does not support all the macro functions. I had to go back to Excel 2011 for Mac for everything to work. Apparently there is no Excel 2013 for Mac.

I will now wait for Microsoft to upgrade the 2016 version to fix the macro issues.

Upvotes: 0

BruceWayne
BruceWayne

Reputation: 23283

For the filename, try to include the path of the file, i.e. Filename:="C:\Users\inm\Desktop\DIRECTORY.pdf".

Upvotes: 0

Related Questions