Saving a cell reference named .pdf to desktop

[David Haywood]

Dera all,

I am using a very simple VBA code to name and save a .pdf file from an Excel worksheet:

Sub SavePDF()


'       Dim fname As String
        With ActiveSheet
        fname = .Range("B1").Value & (" ") & .Range("B3").Value & (" to ") & .Range("C3").Value
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                "Macintosh HD:Users:davidhaywood:Desktop:" & fname, Quality:= _
                xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
                OpenAfterPublish:=True

    End With

End Sub

This works but I have one problem and one question.

Problem: "B1" and "B3" are dates but the .pdf name does not capture/display the year; i.e. the name looks like this: Name 23/10/ to 1/11/.pdf; I would also like the dates to be in this format: Name 2018/10/23 to 2018/11/1.pdf so that they automatically sort themselves in date order when filed. Can this be done?

Question: Currently the macro saves the .pdf to my desktop - but I would like to send the Excel file out to others on Mac and Microsoft platforms so that they can use the macro to save the .pdf on their desktops - is there a generic address I can use?

thanks

David

 

[Chihiro]

"/" isn't a valid character in a file name. At least in PC.

Just use Format() function in VBA to suite your need.

Ex:
Format(Date, "yyyy-mm-dd")

As for second part of your question...
I'd never recommend saving documents on desktops. There are many companies that enforce IT rule, that disables local desktop and provision from network drive, or those that disable save on desktop. To ensure only business specific software are kept on the desktop.

If above isn't an issue, for PC, you can use Environ() function.

Ex:
Environ("USERPROFILE") & "\Desktop"

I'm not familiar with MAC. May be others can help you there.

 

[vletm]

David Haywood
You have 'fixed path' in Your macro, then only You could use that macro.
You've used Mac's pathseparator there which would be a challenge with PC.
Different (in Mac) versions of Excel would act own way with path separator.
"/" with file name would make interesting affects while saving.
Environ("USERPROFILE") won't work with Mac; ... or it gives empty.
It would be better to save to other fixed path than 'Desktop',
eg path with depends of Your Excel-file.

 

[chirayu]

try this

Function Excelversion() As Double
'Win Excel versions are always a whole number (15)
'Mac Excel versions show also the number of the update (15.29)
    Excelversion = Val(Application.Version)
End Function

Sub SavePDF()
Dim Fname As String
Dim FPath As String

Fname = Format(Range("B1"), "YYYYMMDD") & " " & Format(Range("B3"), "YYYYMMDD") & " to " & Range("C3") & ".pdf"

If Int(Excelversion) = Excelversion Then
    FPath = Environ$("USERPROFILE") & "\Desktop\"
ElseIf Application.PathSeparator = ":" Then
    FPath = "Macintosh HD:Users:" & Environ("USER") & ":Desktop:"
Else
    FPath = "/Users/" & Environ("USER") & "/Desktop/"
End If


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FPath & Fname

End Sub