Exportasfixedformat to PDF, Office 2016 and MacOS not printing single sheet

[Duane]

Hey gang,

The following code works perfectly in Microsoft 365 on a Windows 8.1 machine...

Sub SavetoPDF()

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        Range("FolderPath") & "\" & Range("FileName") & ".pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True

End Sub

...and by "works perfectly", I mean, I click a button to run this code, it outputs a crisp PDF of the activesheet, saved with the naming convention required, to the required folder.

Nice.

Until the workbook is opened in Excel 2016 on a Mac, which is when all hell breaks loose:

1. it "prints" all sheets, not just the active sheet, even though the code starts with ActiveSheet.ExportAsFixedFormat
2. it ignores set print areas, even though IgnorePrintAreas is set to false (which means I want it to pay attention to the print area I set)
3. it went to Excel, and not to PDF at all.

Any tips to help me navigate this? Thanks to any/all who may be able to chip in.

PS - I know there are issues with folder management in Office 2016 on a Mac. I think I've successfully gotten around that for now. The above inquiry is now to zero in on smoothening the output wrinkles when running this on a Mac.

 

[cooper645]

Have just gone through this in another post. Mac won't open after publish. You can workaround with a hyperlink.

I'm sure you can adjust the code below to suit your need with the named ranges & sheet names. If not let me know and I'll rehash it

Sub ExportPDF()
Dim sFile As String

sFile = Application.DefaultFilePath & "\" & _
ActiveWorkbook.Name & ".pdf"

Sheets("Sheet1").Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
FileName:=sFile, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True

With Worksheets("Sheet1")
.Hyperlinks.Add Anchor:=.Range("F54"), _
Address:=sFile, _
ScreenTip:="Open saved PDF", _
TextToDisplay:="My PDF File"
End With



Range("F54").Hyperlinks(1).Follow

Range("F54").ClearContents

End Sub

 

[Duane]

Have just gone through this in another post. Mac won't open after publish. You can workaround with a hyperlink.

I'm sure you can adjust the code below to suit your need with the named ranges & sheet names. If not let me know and I'll rehash it

Sub ExportPDF()
Dim sFile As String

sFile = Application.DefaultFilePath & "\" & _
ActiveWorkbook.Name & ".pdf"

Sheets("Sheet1").Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
FileName:=sFile, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True

With Worksheets("Sheet1")
.Hyperlinks.Add Anchor:=.Range("F54"), _
Address:=sFile, _
ScreenTip:="Open saved PDF", _
TextToDisplay:="My PDF File"
End With



Range("F54").Hyperlinks(1).Follow

Range("F54").ClearContents

End Sub

Hey, thanks for chipping in on this. A few questions

1. does this replace my code, or should I call it at the end of my code?
2. Does this address why it's printing multiple pages and not just the one page that was set as the print range?
3. Does this address why it was outputting as Excel and not PDF? Or, in other words, will this spit out a PDF for me?

Thanks for your patience - as you can tell, I am (nowhere near) fluent in VBA.

 

[cooper645]

1. You can either call it at the end or add it. If you keep it as it’s own code then you can use it in future macros by calling rather than re writing it every time.

2. I hope it sorts it, but I will run a sample workbook this evening and test/adjust as needed.

3. I hope it spits out a .pdf.

the last part of the code adds a hyperlink to the pdf created and opens the link, then deletes the link.

As I’m away from the PC and Macbook I can’t test anything, but if you have time to test and let me know if something doesn’t work, I’ll ? into it this afternoon/ evening.

obviously you may need to amend Sheet1 and the F54 range to suit your data. You could use ActiveSheet and a cell way out of the way that will likely never have data or A1 etc

 

[Duane]

1. You can either call it at the end or add it. If you keep it as it’s own code then you can use it in future macros by calling rather than re writing it every time.

2. I hope it sorts it, but I will run a sample workbook this evening and test/adjust as needed.

3. I hope it spits out a .pdf.

the last part of the code adds a hyperlink to the pdf created and opens the link, then deletes the link.

As I’m away from the PC and Macbook I can’t test anything, but if you have time to test and let me know if something doesn’t work, I’ll ? into it this afternoon/ evening.

obviously you may need to amend Sheet1 and the F54 range to suit your data. You could use ActiveSheet and a cell way out of the way that will likely never have data or A1 etc

I will test it, thanks. Yes, I see I'll want to adjust "Sheet1" accordingly for my data. And, hey, F54 happens to work with my data, so no worries there!

Let's both circle back after some testing - MANY THANKS.

 

[cooper645]

Just test and amended slightly, so you can either run from any sheet. Works absolutely fine.

Sub ExportPDF()
Dim sFile As String, ws As Worksheet

sFile = Application.DefaultFilePath & "\" & _ 'Amend to suit your own file path.
ActiveWorkbook.Name & ".pdf"

Set ws = ActiveSheet 'Amend Activesheet for your worksheet if you wish to run from another sheet Sheets("Sheet1")

ws.Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
FileName:=sFile, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True

With ws
.Hyperlinks.Add Anchor:=.Range("F54"), _
Address:=sFile, _
ScreenTip:="Open saved PDF", _
TextToDisplay:="My PDF File"
End With

Range("F54").Hyperlinks(1).Follow

Range("F54").ClearContents

End Sub

 

[cooper645]

I made some changes with some code from Ron De Bruins website which allows the user to select where the file saves to:

Sub ExportPDF()
    Dim ws As Worksheet
    Dim folderPath As String
    Dim RootFolder As String
    Dim scriptstr As String
    Dim sFile As String

Set ws = ActiveSheet

    On Error Resume Next
    RootFolder = MacScript("return (path to desktop folder) as String")
    'Or use RootFolder = "Macintosh HD:Users:YourUserName:Desktop:TestMap:"
    'Note : for a fixed path use : as seperator in 2011 and 2016

    If Val(Application.Version) < 15 Then
        scriptstr = "(choose folder with prompt ""Select the folder""" & _
            " default location alias """ & RootFolder & """) as string"
    Else
        scriptstr = "return posix path of (choose folder with prompt ""Select the folder""" & _
            " default location alias """ & RootFolder & """) as string"
    End If

    folderPath = MacScript(scriptstr)
    On Error GoTo 0

sFile = folderPath & ActiveWorkbook.Name & ".pdf"

ws.Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
FileName:=sFile, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True

With ws
.Hyperlinks.Add Anchor:=.Range("F54"), _
Address:=sFile, _
ScreenTip:="Open saved PDF", _
TextToDisplay:="My PDF File"
End With

Range("F54").Hyperlinks(1).Follow

Range("F54").ClearContents

End Sub

 

[Duane]

Just test and amended slightly, so you can either run from any sheet. Works absolutely fine.

Sub ExportPDF()
Dim sFile As String, ws As Worksheet

sFile = Application.DefaultFilePath & "\" & _ 'Amend to suit your own file path.
ActiveWorkbook.Name & ".pdf"

Set ws = ActiveSheet 'Amend Activesheet for your worksheet if you wish to run from another sheet Sheets("Sheet1")

ws.Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
FileName:=sFile, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True

With ws
.Hyperlinks.Add Anchor:=.Range("F54"), _
Address:=sFile, _
ScreenTip:="Open saved PDF", _
TextToDisplay:="My PDF File"
End With

Range("F54").Hyperlinks(1).Follow

Range("F54").ClearContents

End Sub

I tested it, it's still printing 60+ pages and then resulting in an error message before completion.

I'm at the stage now where this is more an intellectual exercise than a necessity - I'm curious to see if/how I could make this go but, in the meantime, the practical application is going to be to just use a Windows machine to implement this solution "now".

Thanks!

 

[cooper645]

Is there a way to decide how many pages you want to print?

I have a similar set up for printing Certs. Each cert has a lookup to fill the cert number.
It only prints up to the last cert that has a filled cert number.

i just re read your first post, it’s printing all sheets correct, not just all pages on a single sheet