Save Excel worksheet from workbook as PDF in Excel Mac 2011

[Jmoz092]

I saw another thread here about saving excel spreadsheets as PDFs and tried to modify the code (with my woefully lacking VBA skills) into an existing code I received help with here before and I'm having trouble getting the macro to do anything.

I previously received help to get my excel worksheet to SaveAs, that's working, intermittently, sans the occasional "Visual Basic Editor 400" error. I'd like to now apply the same process, to the same worksheet (after users have created input in one or two specific cells) but save it as a PDF to our network.

Below is my attempt to modify the code form this thread, to fit the SaveAs code I was using:

Sub PDFtest()


    Dim thisfile As String
    Dim FilePth As String
    
    If Application.PathSeparator = ":" Then
        FilePth = "data:officeforms:ultrasoundorders:" & Range("Ak1").Value & ":" & Range("Ak2").Value & ":"
        thisfile = Range("Ak3").Value
        ActiveWorkbook.ExportAsFixedFormat FilePth & thisfile, FileFormat:=xlTypePDF, Filename:=thisfile, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=False
    Else
        FilePth = "data\officeforms\ultrasoundorders\" & Range("Ak1").Value & "\" & Range("Ak2").Value & "\"
        thisfile = Range("Ak3").Value
        ActiveWorkbook.ExportAsFixedFormat FilePth & thisfile, FileFormat:=xlTypePDF, Filename:=thisfile, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=False


    End If


End Sub

When we run the macro, we're brought to the debug window with an error message box that reads: "Compile error: Named argument not found"
Then the name of the sub is highlighted yellow and "FileFormat:=" is highlighted blue.

Hopefully I'm just making a movie mistake. Thanks for any feedback you're able to provide.

 

[Jmoz092]

In addition to saving the excel file as a PDF, we're also trying to save a second PDF if the data in the excel file calls for it.

If there is data in cell I5, then we would need to save a second PDF, in addition to the first PDF, in a Windows or Mac environment as outlined in the code in post 1. As additional info, Cells AK1:AK3 populate with info if cell I4 has data in it. Cell I5, for the second PDF, populates data in cells AO1:AO3. I don't know how to write this "Next" function into the code we're working on.

Thanks for helping me work through my novice mistakes.

Sub PDFtest()


    Dim thisfile As String
    Dim FilePth As String
    
    If Application.PathSeparator = ":" Then
        FilePth = "data:officeforms:ultrasoundorders:" & Range("Ak1").Value & ":" & Range("Ak2").Value & ":"
        thisfile = Range("Ak3").Value & ".PDF"
        ActiveWorkbook.ExportAsFixedFormat FilePth & thisfile, FileFormat:=xlTypePDF, Filename:=thisfile, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=False
    Else
        FilePth = "data\officeforms\ultrasoundorders\" & Range("Ak1").Value & "\" & Range("Ak2").Value & "\"
        thisfile = Range("Ak3").Value & ".PDF"
        ActiveWorkbook.ExportAsFixedFormat FilePth & thisfile, FileFormat:=xlTypePDF, Filename:=thisfile, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=False
    Next
           If WorksheetFunction.CountA( _
            Sheet1.Range("I5")) > 1 Then
           FilePth = "data\officeforms\ultrasoundorders\" & Range("Ao1").Value & "\" & Range("Ao2").Value & "\"
        thisfile = Range("Ao3").Value & ".PDF"
        ActiveWorkbook.ExportAsFixedFormat FilePth & thisfile, FileFormat:=xlTypePDF, Filename:=thisfile, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=False
    


    End If


End Sub

 

[Jmoz092]

Does anyone know why I'd be receiving an error, and the macro not working because of the FileFormat in the code?

 

[Jmoz092]

Ive searched through multiple sites and examples and I'm not finding a solution for this macro not saving as a PDF. Is it a problem with using excel Mac 2011 and/or windows platforms? Even when I remove one or the others' save paths, I still get a file format error. Every other example Ive seen uses the same structure to the macro I'm trying to implement...what am I missing??

 

[Jmoz092]

We've had success with this code renaming the PDF as intended, but only saving to the user's desktop. How can we alter it to save to our network?

[COLOR=#454545][FONT=&quot][SIZE=3]Sub Save_ActSht_as_Pdf()[/SIZE][/FONT][/COLOR]
[SIZE=3][/SIZE][COLOR=#454545][FONT=&quot][SIZE=3]' Saves active sheet as PDF.[/SIZE][/FONT][/COLOR]
[SIZE=3][/SIZE][COLOR=#454545][FONT=&quot][SIZE=3]Dim Name As String[/SIZE][/FONT][/COLOR]
[SIZE=3][/SIZE][COLOR=#454545][FONT=&quot][SIZE=3]Dim filepath As String[/SIZE][/FONT][/COLOR]
[SIZE=3][/SIZE][COLOR=#454545][FONT=&quot][SIZE=3]
[/SIZE][/FONT][/COLOR]
[SIZE=3][/SIZE][COLOR=#454545][FONT=&quot][SIZE=3]filepath = "filepath1:filepath2:filepath3:" & Range("Ak1").Value & ":" & Range("Ak2").Value & ":"[/SIZE][/FONT][/COLOR]
[SIZE=3][/SIZE][COLOR=#454545][FONT=&quot][SIZE=3]Name = Range("Ak3").Value[/SIZE][/FONT][/COLOR]
[SIZE=3][/SIZE][COLOR=#454545][FONT=&quot][SIZE=3]ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Name, _[/SIZE][/FONT][/COLOR]
[SIZE=3][/SIZE][COLOR=#454545][FONT=&quot][SIZE=3]Quality:=xlQualityStandard, IncludeDocProperties:=True, _[/SIZE][/FONT][/COLOR]
[SIZE=3][/SIZE][COLOR=#454545][FONT=&quot][SIZE=3]IgnorePrintAreas:=False, OpenAfterPublish:=False[/SIZE][/FONT][/COLOR]
[SIZE=3][/SIZE][COLOR=#454545][FONT=&quot][SIZE=3]ActiveSheet.SaveAs filepath & Name[/SIZE][/FONT][/COLOR]
[SIZE=3][/SIZE][COLOR=#454545][FONT=&quot][SIZE=3]
[/SIZE][/FONT][/COLOR]
[SIZE=3][/SIZE][COLOR=#454545][FONT=&quot][SIZE=3]End Sub[/SIZE][/FONT][/COLOR]

 

[Jmoz092]

Hi, we're still having problems with this project.

Is there a section on the forums to solicit paid solutions? Thanks.