How to export a pdf using an excel macro with ExportAsFixedFormat() if the excel file is on a cloud storage?

Question

I have an excel sheet with macros and want to export a pdf of a sheet. I am doing this successfully using the following code:

Public Sub NowPrintThePage(ByVal LieferantPdfName As String)
    PdfName = Sheets(LieferantPdfName).Range("F16").Value & " " & Mid(ThisWorkbook.Name, 10)
    Sheets(LieferantPdfName).ExportAsFixedFormat _
                                Type:=xlTypePDF, _
                                Filename:=PdfName, _
                                Quality:=xlQualityStandard, _
                                IncludeDocProperties:=False, _
                                IgnorePrintAreas:=False, _
                                OpenAfterPublish:=True
End Sub

I do not really want to save the pdf file anywhere but just want to open it such that the user can decide for himself what to do with it.

That works all fine. The problem is, if I don't open the excel file from a folder within windows but from cloud storage (OneDrive, MS Teams, etc.), the macro throws an exception at the line with "ExportAsFixedFormat(...)".

My guess is, that excel tries to save the pdf before displaying to the folder of the excel file - which is not possible because it is loaded from the cloud. Since I do not really want to save the pdf file - what can I do to generate the pdf and show it to the user, even if it is on the cloud?

Answer 1

Get the user's TEMP folder with this VBA function: ENVIRON("TMP")