Forbidden access to OneDrive pdf files via VBA

Question

GOAL:
By VBA I have to download a pdf from OneDrive.

PROBLEM:
I am following a bunch of tutorials, but when the download has finished and I try to open the downloaded pdf file I receive this error message:

"Adobe Reader could not open [name_file.pdf] because it is either not a supported file type or because the file has been damaged (for example, it was sent as an email attachment and wasn't correctly decoded)."

ROUTINE:
This the routine to download the file.

Sub DownloadFile(indirizzoOnedrive, indirizzoSalvataggio, nomeCommessa)
Dim myURL As String Dim Salvataggio As String Dim nomeFile As String Dim WinHttpReq As Object

myURL = indirizzoOnedrive

Salvataggio = indirizzoSalvataggio

nomeFile = nomeCommessa

Set WinHttpReq = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    WinHttpReq.Open "GET", myURL, False  'usr and pwd are commented because in one drive I permit access to everybody that has link, "username", "password"
    WinHttpReq.send

If WinHttpReq.Status = 200 Then

    Set oStream = CreateObject("ADODB.Stream")
    With oStream
        .Open
        .Type = 1
        .Write WinHttpReq.responseBody
        .SaveToFile Salvataggio & "\" & nomeFile & ".pdf", 2
        .Close
    End With
    
End If

Set WinHttpReq = Nothing
Set oStream = Nothing

End Sub

ANALYSIS
I tried to open the downloaded pdf in a text editor and I saw that it contained HTML information.
Opening the file via a browser gives me this error message:

"Can't sign in
Your browser is currently set to block cookies. To use this service, you must allow cookies.
Cookies are small text files stored on your computer that communicate when you are logged in. For information on how to allow cookies, see the help of your web browser."

The link I pass to the variable "myURL" works if open from a normal browser (so if I work on it outside the VBA code). Apparently therefore the denial seems to interest only VBA.

Are you sure the URL you're downloading from is in fact the url of a PDF and not a web page that redirects to one? In other words, does it end in ".pdf"? — jsheeran, Mar 30 '22 at 08:57
Hi jsheeran, yes, I confirm you that url end with ".pdf" Here the example of it (for security issue I just have modified on it personal information): https://mydomain-my.sharepoint.com/personal/user_mydomain_it/Documents/App/Microsoft%20Forms/NCR_internal/Question%203/439_Tablet%20Nde.pdf This link is generated by a tablet that compiles a Microsoft forms and upload the pdf file in specified folder in onedrive. — Salton Sea, Mar 30 '22 at 11:02

score 0 · Answer 1 · edited Mar 30 '22 at 17:57

Instead of the aforementioned code, I found a solution using this code:

Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" _
                                 Alias "URLDownloadToFileA" ( _
                                 ByVal pCaller As Long, _
                                 ByVal szURL As String, _
                                 ByVal szFileName As String, _
                                 ByVal dwReserved As Long, _
                                 ByVal lpfnCB As Long _
                                 ) As Long

DownloadFileFromWeb driveFilename, folderPath, numero_conformita


Function DownloadFileFromWeb(strURL As String, strSavePath As String, namePDF As String) As Long

    Dim Salvataggio, myURL As String
    Dim downloadPDF As Long
    
    myURL = strURL
    
    Salvataggio = strSavePath & "\" & namePDF & ".pdf"
  
    downloadPDF = URLDownloadToFile(0, myURL, Salvataggio, 0, 0)
    
    If downloadPDF = 0 Then
        MsgBox "Pdf scaricato con successo!"
    Else
        MsgBox "ATTENZIONE: Pdf NON  scaricato!!!"
    End If
    
End Function

In this mode, the OneDrive download seems to work.

Forbidden access to OneDrive pdf files via VBA

1 Answers1