Export as PDF for Windows and Mac

[mummbles74]

I have this code that saves a file as a PDF in the folder location that the excel document is saved in based on certain cell values.

Is there a way to make this work for Windows and MAC?

Sub GenerateLoopDrgs()

Dim Row As Long

Row = 4
With Worksheets("Review Data")
Do Until IsEmpty(.Cells(Row, "G").Value)
Worksheets(.Cells(Row, "G").Value).Range("BX1") = .Cells(Row, "A").Value
Worksheets(.Cells(Row, "G").Value).ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & .Cells(Row, "B") & "-" & .Cells(Row, "C") & "-" & .Cells(Row, "D") & "-" & .Cells(Row, "A") & "-" & Worksheets(.Cells(Row, "G").Value).Range("BX4").Value

Row = Row + 1
Loop
End With

MsgBox "Drawings have been generated.", vbOKOnly, "Cenelec Standards Inspections LTD"
End Sub

 

[Sam_D_Ben]

Something like this,

Dim Row As Long
Dim folderPath As String
Dim fileName As String

Row = 4
With Worksheets("Review Data")
    folderPath = ThisWorkbook.Path & IIf(InStr(1, folderPath, ":") > 0, "\", "/")
    
    Do Until IsEmpty(.Cells(Row, "G").Value)
        fileName = folderPath & .Cells(Row, "B") & "-" & _
                  .Cells(Row, "C") & "-" & _
                  .Cells(Row, "D") & "-" & _
                  .Cells(Row, "A") & "-" & _
                  Worksheets(.Cells(Row, "G").Value).Range("BX4").Value
        
        Worksheets(.Cells(Row, "G").Value).ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=fileName

        Row = Row + 1
    Loop
End With

 

[mummbles74]

Many thanks that worked great. I changed the file name format a little but tested on MAC and Windows machine and worked as required.

With the code it is meant to loop until column G is empty, the cell is empty but there is a formula in there that returns no result (vlookup) is there a way to make it ignore a formula?

 

[RoryA]

FYI, using Application.PathSeparator is the usual way to get the correct separator for file paths.

 

[Sam_D_Ben]

Something like this,

If Not (.Cells(Row, "G").Value = "" And .Cells(Row, "G").HasFormula) Then

<Checking if the cell is empty and has formula, This will ignore and Do Next>