"The Resource Cannot be found" Error When Trying to Create a PDF

[masouder]

I have used a simple routine to create PDF files for several years.

Sub BuildPDF(FileName As String, PDFSheet, Show As Boolean)
    'Test if the Microsoft Add-in is installed
    If Dir(Environ("commonprogramfiles") & "\Microsoft Shared\OFFICE" _
            & Format(Val(Application.Version), "00") & "\EXP_PDF.DLL") = "" Then
        MsgBox "PDF Add-In has not been installed", vbCritical, "Create PDF"
        Exit Sub
    End If
    
'    PDFSheet.PageSetup.Orientation = xlLandscape
    
    On Error Resume Next
    PDFSheet.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            FileName:=FileName, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=Show
    On Error GoTo 0
    
End Sub

It has worked well for many, many clients; however, a recent client is receiving the error message below. He receives the message when the Excel file is on a network drive, a cloud drive or on his Desktop. Does anyone know why he may be getting this error message?

 

[starl]

I'd recommend verifying the path - I assume that's the resource that cannot be found. You verify the existence of the PDF Add-in - I recommend you check the existence of the FileName's path, too. And check the rights - ie the client has the right to read & write to the folder.

 

[smozgur]

This is not related to VBA or even Excel.

It is directly related to the URL you are using that says the file is not on the server it is currently pointing to.

 

[masouder]

Thak you for the replies. The FileName's path is the same folder where the spreadsheet resides. I use ThisWorkbook.Path to set the path then add a file name before passing to the procedure.

Does that help?

 

[starl]

There's nothing wrong with your code. The problem is you are telling Excel to create a PDF to a location it cannot find, or perhaps an invalid name. You need to check the ENTIRE folder path to make sure the client has access. And make sure the client has a right to create the file in that folder. This is not an Excel issue nor a problem with your code. It's an issue with sharepoint.

Note that VBA does not automatically create folders that don't exist, even if you are trying to save a file there.

 

[masouder]

My point about using ThisWorkbook.Path is that I assume that ensures that it is trying to save to a valid path. Is that assumption correct? (I recognize that even if it is correct the user may not have write access.)

 

[starl]

Client could have read privileges and not write ones for the folder
or - Excel does not handle cloud-based file paths properly. I have noticed that when opening a file saved to the cloud, if you use VBA to check the path, it may NOT the expected path. It is, instead, a path to a temporary file that was created for the session. Note that some Desktops are now also stored in the cloud, so testing a file on the Desktop will return the same error.

have you looked at the path the program is trying to save to? It's in the error message you posted.

 

[masouder]

We did not check the path, but I asked the client to move the Excel file to his desktop and he got a similar result, though that seems odd that it would not work on the desktop.

 

[starl]

The Desktop could be stored in the cloud, which would return the same error. Move the workbook to the C: drive and try there. I would recommend creating a folder on the C: drive just for your workbook and put it in there.

 

[masouder]

Good idea, thanks.