Download xlsx file from password protected website

Question

I'm trying to download a xlsx file from a password protected website to use in PBI.

On PBI I already tried to use Power Query and the Web Connector. I also tried using Power Automate (online version with HTTP connector, since my desktop version doesn't run on background). And finally I'm using VBA. But all of them returns a file with the website HTML code, instead of the data which should be in the xlsx.

The code from the last try with VBA (which I found here is bellow (with a generic website URL)):

Sub DownloadFile()

    Dim evalURL As String
    Dim streamObject As Object
    Dim winHttpRequest As Object
    Set winHttpRequest = CreateObject("Microsoft.XMLHTTP")

    evalURL = "https://generic_website.com/Excel_file.xslx" '

    winHttpRequest.Open "GET", evalURL, False, "username", "password"
    winHttpRequest.send

    If winHttpRequest.Status = 200 Then
        Set streamObject = CreateObject("ADODB.Stream")
        streamObject.Open
        streamObject.Type = 1
        streamObject.Write winHttpRequest.responseBody
        streamObject.SaveToFile "C:\Users\MyUser\Downloads\Excel_file.xslx", 2 ' 1 = no overwrite, 2 = overwrite
        streamObject.Close
    End If

End Sub

If I log into the website and open the URL directly in a browser, it downloads the .xlsx file.

Is there any way to do that? I have no idea what's happening, since the same code worked to other people.

---

UPDATE:

I tried the VBA code bellow, and get the results you can see in the image here.

Sub Login()

Dim response As String

With CreateObject("Microsoft.XMLHTTP")
  .Open "GET", "https://generic_website.com/Excel_file.xslx", False, "username", "password"
  .send
  response = .responseText
End With

MsgBox response

End Sub

Answer 1

I do not know if this works as I cant login, and dont know the file. hopefully it can point you in the right direction.

' VBA Editor->Tools->References
' find and select the following
' Microsoft WinHTTP Services,version 5.1
' Microsoft HTML Object Library

Sub GetFile()
Dim URL As String: URL = "your url"
Dim File As String: File = "your url/file.xslx"
Dim Email As String: Email = "your@address.com"
Dim Password As String: Password = "Your Password"

Dim Cookie As String
Dim Token As String
Dim Message As String

Dim HTML As HTMLDocument: Set HTML = New HTMLDocument
Dim HTTP As WinHttpRequest: Set HTTP = New WinHttpRequest

' you potentially need the csrf_token to post the login form.
' so we get the token, and any cookies sent
HTTP.Open "GET", URL, True
HTTP.Send
HTTP.WaitForResponse

HTML.body.innerHTML = HTTP.ResponseText

Cookie = HTTP.GetResponseHeader("Set-Cookie")
Token = HTML.getElementsByName("csrf_token")(0).Value

Message = "csrf_token=" & Token & "&email=" & URLEncode(Email) & "&senha=" & URLEncode(Password)

HTTP.Open "POST", URL, True
HTTP.SetRequestHeader "Content-type", "application/x-www-form-urlencoded"
HTTP.SetRequestHeader "Cookie", Cookie
HTTP.Send Message
HTTP.WaitForResponse
Cookie = HTTP.GetResponseHeader("Set-Cookie")

' i dont have credentials so dont know what happens after this point and cannot test any further

HTTP.Open "GET", File, True
HTTP.SetRequestHeader "Cookie", Cookie
HTTP.Send
HTTP.WaitForResponse

msgbox HTTP.responseText

' if the runtime error still occurs then not sure what to do
' however,
' if the above message box looks like HTML, it didnt work.

' if it doesn't, it MIGHT have worked, you just need to
' figure out how to save the data to a file

'Dim FileNumber As Integer: FileNumber = FreeFile()

'Open "C:\destination.xslx" For Binary Access Write As #FileNumber
'Put #FileNumber, 1, HTTP.ResponseBody
'Close #FileNumber

End Sub

'https://stackoverflow.com/a/218199/212869
Public Function URLEncode(StringVal As String) As String
Dim StringLen As Long: StringLen = Len(StringVal)

If StringLen > 0 Then
  ReDim result(StringLen) As String
  Dim i As Long, CharCode As Integer
  Dim Char As String

  For i = 1 To StringLen
    Char = Mid$(StringVal, i, 1)
    CharCode = Asc(Char)
    Select Case CharCode
      Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126
        result(i) = Char
      Case 32
        result(i) = "+"
      Case 0 To 15
        result(i) = "%0" & Hex(CharCode)
      Case Else
        result(i) = "%" & Hex(CharCode)
    End Select
  Next i
  URLEncode = Join(result, "")
End If
End Function