VBA issue on Mac.

[Russk68]

Hi All,

This code was written by someone else who I lost contact with.

This macro saves worksheets to PDFs from a list of worksheet names in column A. It then only prints the number of pages in column B per sheet only if there is a yes in column C per sheet.
Before it saves, a prompt will ask where to save the file.
Short version, I can print out my entire workbook with the desired sheets with a few clicks.

1st issue: In the latest version of Excel for Mac (16.23) the PDFs that are generated, ignore the page breaks and will be 2 or more pages when it is only 1 page and saves in the selected folder.
I then tried this as a work around:
2nd issue: In Excel for Mac 2011, the PDFs are saved properly, but the PDF show up on my desktop instead of inside the selected folder.

This is driving me crazy because part of it will only work in 16.23 and vise versa for 14.77. I would be happy if I could get this to work on either version.

Thank you!

Russ

Sub prtmeMac()
    Dim cRng As Range
    Dim FolderName As String
    Dim Folderstring As String
    Dim blprinted As Boolean
    Dim fdlg As Object

    FolderName = Select_Folder_On_Mac
    Folderstring = FolderName

    On Error Resume Next
    TestStr = Dir(Folderstring & "/" & Fstr, vbDirectory)
    On Error GoTo 0
    'f TestStr = vbNullString Then MkDir Folderstring & "/" & Fstr

    For Each cRng In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
        If cRng.Offset(0, 2).Value = "Yes" Then
            Filename = Sheets(cRng.Value).Name & " " & Format(Now, "dd-mmm-yyyy hh-mm") & ".pdf"
           FilePathName = Folderstring & Application.PathSeparator & Filename

            With Sheets(cRng.Value).PageSetup
                .Orientation = Sheets(cRng.Value).PageSetup.Orientation
            End With

             Sheets(cRng.Value).Select
              blprinted = True

              Sheets(cRng.Value).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
               FilePathName, Quality:=xlQualityStandard, _
               From:=1, To:=cRng.Offset(0, 1).Value, _
               IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

        End If
    Next
    Sheets("PRINT").Select
    If blprinted = False Then
        MsgBox "No sheets were selected to be printed."
    Else
        MsgBox "The folder can be found at this location :" & Folderstring
    End If
End Sub

 

[mikerickson]

The use of the "/" path separator in the testStr declaration is probably messing things up. Mac uses a ":" path separator. Or you could use Application.PathSeparator to make it cross-platform.

 

[Russk68]

Hi Mike,

Thank you for responding!

I replaced the "/" with Application.PathSeparator in the TestStr line. is that where it was supposed go? I tried it as it is here and the file still ends up on my desktop.

Sub prtmeMac()
Dim cRng As Range
Dim FolderName As String
Dim Folderstring As String
Dim blprinted As Boolean
Dim fdlg As Object

FolderName = Select_Folder_On_Mac
Folderstring = FolderName

On Error Resume Next
TestStr = Dir(Folderstring & Application.PathSeparator & Fstr, vbDirectory)
On Error GoTo 0
'f TestStr = vbNullString Then MkDir Folderstring & "/" & Fstr

For Each cRng In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
If cRng.Offset(0, 2).Value = "Yes" Then
Filename = Sheets(cRng.Value).Name & " " & Format(Now, "dd-mmm-yyyy hh-mm") & ".pdf"
FilePathName = Folderstring & Application.PathSeparator & Filename

With Sheets(cRng.Value).PageSetup
.Orientation = Sheets(cRng.Value).PageSetup.Orientation
End With

Sheets(cRng.Value).Select
blprinted = True

Sheets(cRng.Value).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
FilePathName, Quality:=xlQualityStandard, _
From:=1, To:=cRng.Offset(0, 1).Value, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

End If
Next
Sheets("PRINT").Select
If blprinted = False Then
MsgBox "No sheets were selected to be printed."
Else
MsgBox "The folder can be found at this location :" & Folderstring
End If
End Sub