Need Mac Tweak for GetSaveAsFilename

[dsrt16]

I googled a tweak for the mac version of GetSaveAsFilename, but it was wanting to prompt the user to first resave the whole excel workbook with a new name before making the pdf, and it also didn't seem to work when I tested it on a Mac anyway. I can navigate Macs, so maybe it did work, but it never opened my PDF file, and I couldn't figure out if it had created it.

The code below works perfectly on my PC, but I need it to work for Mac users. The first part of the code is just creating the page setup: landscape orientation and narrow margins. I hope that works on a Mac.

The second part of the code is to save the print area as a PDF with the user creating the name of the PDF file. This does not compute on a Mac.

Could someone please advise how I could add a line that says IfPC do the code I have, If Mac, do this.....?

Sub Printrange1()


    ActiveSheet.PageSetup.PrintArea = "$A$2:$K$25"
    
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftMargin = Application.InchesToPoints(0.25)
        .RightMargin = Application.InchesToPoints(0.25)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
    End With
    
    Application.PrintCommunication = True
    
    
    Dim FName As Variant
    FName = Application.GetSaveAsFilename( _
        FileFilter:="PDF files, *.pdf", _
        Title:="Export to pdf")
    If FName <> False Then
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FName _
            , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
            :=False, OpenAfterPublish:=True
    End If
    
End Sub

 

[James006]

Hello,

Ron de Bruin has an important section with all the headaches (and solutions) for Mac ...

https://www.rondebruin.nl/mac/mac015.htm

Hope this will help

 

[dsrt16]

This is the Ron de Bruin page that seemingly addresses my issue: http://www.rondebruin.nl/mac/mac005.htm.

However, if I am understanding the code correctly, the user isn't creating the filename; it is a fixed file name with the string.

I would like the code to prompt the user to enter in the file name as it does for Windows.

For Windows when I run my above posted code, it sets the print area, changes the orientation to landscape and the margins to narrow, then it prompts the user to save the file with whatever name they want to give it and they can navigate to whatever folder they want to put it in, and then it creates the PDF and opens it right away.

With Ron's code, it is making the filename a certain string, and then just telling the user "ok here is what I called the file, and here is where it is found," which is odd to me. I want the user to be able to name it what they want and put it where they want.

But I am new to VBA and might be understanding the code found on the page I put earlier.

 

[Jon Peltier]

Yeah, I had worked out some similar code for Macs which worked okay, but it was Excel 2011. In Mac Excel 2016, there are so many constraints, I never could get code to run the way I wanted on my own computer, never mind someone else's. I know it can be done, but I haven't had the bandwidth to work it all out. So for now at least I've disabled certain features in my Excel add-ins so only Windows users get them.

 

[dsrt16]

Jon,

So are you saying it can't be done?


I found this code on another forum, but I don't quite understand what it will do.

#If  Mac Then
GetName:
    Filename = Application.GetSaveAsFilename
    If Left(Right(Filename, 4), 1) = "." And LCase(Right(Filename, 4)) <> ".pdf" Then
        Filename = Left(Filename, Len(Filename) - 4) & ".pdf"
    Else
        Filename = Filename & IIf(Right(Filename, 1) = ".", "pdf", ".pdf")
    End If
    If MsgBox("Is this correct?" & Chr(10) & Filename, vbYesNo) = vbNo Then GoTo GetName

And this

#ElseIf  Mac Then
' this section is for Mac users
fName = Application.GetSaveAsFilename
' tweak the name for mac users
If fName <> False Then
' if there's ".xls" or ".xlt" at the end of the name, remove it
If (Right(fName, 4) = ".xls") Or (Right(fName, 4) = ".xlt") Then
intLength = Len(fName)
fName = Mid(fName, 1, intLength - 4)
End If

fName = fName & ".pdf"
Worksheets(myName).SaveAs FileName:=fName, FileFormat:=xlpdf
End If
#End  If

But I am not sure I understand the whole fName, 4 bit--what it is doing.


If this isn't possible on a Mac, is there at least Mac friendly code that would print the correct range to a PDF file with a set name. I can give it a set name. Worksheet name plus overview would work. Each worksheet name is a year. So it would be 2018 overview or 2019 overview, and that is perfectly fine.

Honestly, the user doesn't have to create their own file name, but what I do need and hope to achieve is only the range specified is exported to a PDF and it is landscape with the narrow margins (otherwise the tables won't fit).

Is there a code for that for a mac?

 

[dsrt16]

Without letting the user create their own name, which is fine, I used a different code, and it works on my PC. I don't really need them to create their own name as much as I was hoping they could choose where to save it, but I can let that go.

Would this code commute on a mac?

Sub Printrange1()


    ActiveSheet.PageSetup.PrintArea = "$A$2:$K$25"
    
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftMargin = Application.InchesToPoints(0.25)
        .RightMargin = Application.InchesToPoints(0.25)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
    End With
    
    Application.PrintCommunication = True
    
    
Dim filesavename As String

   filesavename = Range("A2").Value & "_" & "Overview"


         ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=filesavename _
        , quality:=xlQualityStandard, includedocproperties:=True, ignoreprintareas _
        :=False, openafterpublish:=True
    
    
 
End Sub

When I tested it on my PC, it worked.

 

[Jon Peltier]

I didn't say it can't be done, I said that I haven't had the time to get it working for my applications.

You should use Ron's approach. You can replace this:

FileName = ActiveSheet.Name & " " & Format(Now, "dd-mmm-yyyy hh-mm-ss") & ".pdf"

with something like this, to get a filename from the user, based on an initial suggestion:

FileName = ActiveSheet.Name & " " & Format(Now, "dd-mmm-yyyy hh-mm-ss") & ".pdf"
FileName = Application.InputBox(Prompt:="Enter a file name:", Title:="File Name", _
      Default:=FileName, Type:=2)

Not as nice as a real file dialog.

And Ron de Bruin's page on GetSaveAsFilename for Mac is here: https://www.rondebruin.nl/mac/mac016.htm

 

[dsrt16]

Thanks, Jon, for the link.

I think this is all getting way too complicated for my abilities. So do you know if this will compute on a Mac:

Sub Printrange1 ()

'enter code here to set the page setup and print area

Dim filesavename As String

   filesavename = Range("A2").Value & "_" & "Overview"


         ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=filesavename _
        , quality:=xlQualityStandard, includedocproperties:=True, ignoreprintareas _
        :=False, openafterpublish:=True
    
    
 
End Sub

User can't create own name, but that is fine at least it saves the print range as a PDF.


I got really confused because at first your message seemed simple before I went to the link you sent for Ron's page.

You said to replace this line

FileName = ActiveSheet.Name & " " & Format(Now, "dd-mmm-yyyy hh-mm-ss") & ".pdf"

with something like this, to get a filename from the user, based on an initial suggestion:

FileName = ActiveSheet.Name & " " & Format(Now, "dd-mmm-yyyy hh-mm-ss") & ".pdf"
FileName = Application.InputBox(Prompt:="Enter a file name:", Title:="File Name", _
      Default:=FileName, Type:=2)

However, looking at the code on the link you sent me, I don't see that FileName line of code you want me to replace.

Ron's code uses some long, complicated function that is called in the Sub, but does not contain the line of code you mentioned to replace, and it looks like it is saving the whole workbook as an excel document, not saving a certain area as a PDF, and since I can't make sense of his function code, I can't tweak it.

I understood your replace this line with that, but then you said use Ron's approach which doesn't match. So yeah way too complicated for me. Will the above code where there is a set name compute on a Mac?

 

[Jon Peltier]

I'm off-line for a couple days. When I return maybe I'll fire up my Mac and see what I can figure out.

 

[dsrt16]

I am no longer trying to get the Mac user to be able to save as their own name.

I am just trying to get the pdf to print with the correct margins. Since this is a new issue, I started a new forum.