Forum Discussion

dominiccronshaw's avatar
dominiccronshaw
Copper Contributor
Nov 02, 2025

VBA trouble with Export as Fixed Format

 

 

Hi folks - I'm struggling to get this code for export as fixed format to work.    My aim is to use it to churn through 150 names in a dropdown (data validation using a reference to a named range called "Names") and print PDF earning statements for each person.

I have the file stored on Sharepoint and when I run it there it seems to churn through the names, but not produce the PDFs (at least not that I can find).   When I move the file to my local device (Mac), it throws an error and highlights this part as the issue.

Error when trying to execute from local copy.Button and a dynamically produced filename

 

Any help is most appreciated.

I'd also love to learn how to fire this from the version hosted on Sharepoint, but from this article it seems I'll need some more VBA.

Sub PrintAllVariablePayoutPDFs()
    
    Dim DropDown As Range    'xRg
    Dim IndivName As Range
    Dim NamesList As Range  'List of COMPANY Employees
    
    Dim PDFName As String
    Dim PDFPath As String
    Dim FullPath As String
        
    ' Turn off screen updating
    Application.ScreenUpdating = False

    ' Set where dropdown resides
    Set DropDown = Range("G2")
    
    ' Set where list of names resides
    Set NamesList = Evaluate(DropDown.Validation.Formula1)
            
    ' Set PDF path every quarter
    PDFPath = "/Users/dominiccronshaw/Library/CloudStorage/OneDrive-COMPANY/GTM Operations/Commissions/2025 Q3/Payout PDFs/"
           
    'Set PDFName
    PDFName = Range("K4").Value
    
    ' Construct the full file path
    ' FullPath = PDFPath & PDFName & ".pdf"
           
    'Steps through options in the dropdown with people's names
        For Each IndivName In NamesList
        DropDown = IndivName.Value
    
    'Prints each sheet as PDF with unique file name
    ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    FileName:=PDFName, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False
    
   Next
    
    'Turn on screen updating
    Application.ScreenUpdating = True
    
End Sub

 

 

 

excel
Excel on Mac
Macros and VBA

1 Reply

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Nov 04, 2025

    Classic Excel-on-Mac VBA snag — especially with ExportAsFixedFormat and SharePoint/OneDrive paths.

    Sub PrintAllVariablePayoutPDFs_MacSafe()
    Dim DropDown As Range
    Dim IndivName As Range
    Dim NamesList As Range
    Dim PDFName As String
    Dim PDFPath As String
    Dim FullPath As String

    ' Disable screen updates for speed
    Application.ScreenUpdating = False

    ' Dropdown location
    Set DropDown = Range("G2")
    ' List of all names (from validation)
    Set NamesList = Evaluate(DropDown.Validation.Formula1)

    ' Use a valid local path - ensure it exists first
    PDFPath = "/Users/dominiccronshaw/Library/CloudStorage/OneDrive-COMPANY/GTM Operations/Commissions/2025 Q3/Payout PDFs/"
    If Right(PDFPath, 1) <> "/" Then PDFPath = PDFPath & "/"
    If Dir(PDFPath, vbDirectory) = "" Then
        MsgBox "PDF output folder not found: " & PDFPath, vbCritical
        Exit Sub
    End If

    ' Loop through all names in list
    For Each IndivName In NamesList
        DropDown.Value = IndivName.Value
        DoEvents ' let Excel update dependent cells

        ' Refresh PDF name each time (in case K4 changes)
        PDFName = Range("K4").Value
        FullPath = PDFPath & PDFName & ".pdf"

        ' Optional debug check
        Debug.Print "Saving PDF to: " & FullPath

        ' Export as PDF (Mac-safe)
        On Error Resume Next
        ActiveSheet.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            FileName:=FullPath, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=False

        If Err.Number <> 0 Then
            MsgBox "Error saving PDF for " & IndivName.Value & vbCrLf & _
                   "Path: " & FullPath & vbCrLf & _
                   "Error: " & Err.Description, vbCritical
            Err.Clear
        End If
        On Error GoTo 0
    Next IndivName

    Application.ScreenUpdating = True
    MsgBox "All PDFs exported successfully!", vbInformation
End Sub

    Make sure the output folder exists. Excel won’t create directories.

    Avoid spaces or punctuation in folder names if possible.

    The local OneDrive path on Mac must be mounted under /Users/.../Library/CloudStorage/OneDrive-YourOrgName/.

    If the script appears to “do nothing”, open the VBA Immediate Window (Cmd+Option+I) to check the Debug.Print output — it will show exactly where it’s trying to save.

     

    This is a suggestion.

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

Resources