Export as PDF for Windows and Mac

mummbles74

Board Regular
Joined
Nov 14, 2009
Messages
125
Office Version
  1. 365
Platform
  1. Windows
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
 
Something like this,
VBA Code:
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
 
Upvote 0
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?
 
Upvote 0
FYI, using Application.PathSeparator is the usual way to get the correct separator for file paths.
 
Upvote 0
Something like this,
VBA Code:
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>
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,924
Members
453,767
Latest member
922aloose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top