Import A CSV File into Excel (Requires Login/Pass)

Question

I've looked at dozens of similar topics here without finding what I'm looking for. If I was doing it manually, I would navigate to the URL, given some parameters:

https://the.website.com/mobile/rest/reportservice/exportReport?name=shared%2FMV+Report.ppr&format=csv&showTotals=false&valueDate=Today

When I go there, it requests a username and password in a pop-up box, which I enter, and it downloads the CSV.

This Python code works perfectly:

import requests
from requests.auth import HTTPBasicAuth

USERNAME = "i_cant_tell_you_guys"
PASSWORD = "definitely_cant_share_this"

auth = HTTPBasicAuth(USERNAME, PASSWORD)

URL = "https://the.website.com/mobile/rest/reportservice/exportReport?name=shared%2FMV+Report.ppr&format=csv&showTotals=false&valueDate=Today"

resp = requests.get(URL.format(USERNAME, PASSWORD), auth=auth, verify=False)

print resp.content

But I can't seem to replicate it in VBA. Is it possible? If not, could I have Excel and Python work together somehow?

Thanks!

Edit: Several people suggested this approach: VBA WinHTTP to download file from password proteced https website but since the URL I'm pulling isn't the actual file URL, it won't work.

Edit: Finally got it working. The website had a certificate mismatch, so I had to tell WinHTTP to ignore SSL errors. Here's the new code:

Sub SaveFileFromURL()

    Const Option_SSLErrorIgnoreFlags = 4
    Const SslErrorFlag_Ignore_All = 13056
    Const HTTPREQUEST_SETCREDENTIALS_FOR_SERVER = 0

    Dim FileNum As Long
    Dim FileData() As Byte
    Dim WHTTP As Object

    mainUrl = "https://website.com"
    fileUrl = "https://website.com/myfile.csv"
    filePath = "C:\myfile.csv"

    myuser = "login"
    mypass = "pass"

    Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")


    WHTTP.Open "POST", mainUrl, False
    WHTTP.SetCredentials myuser, mypass, HTTPREQUEST_SETCREDENTIALS_FOR_SERVER
    WHTTP.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    WHTTP.Option(Option_SSLErrorIgnoreFlags) = SslErrorFlag_Ignore_All

    WHTTP.Send

    WHTTP.Open "GET", fileUrl, False
    WHTTP.Send

    FileData = WHTTP.ResponseBody
    Set WHTTP = Nothing

    FileNum = FreeFile

    Open filePath For Binary Access Write As #FileNum
        Put #FileNum, 1, FileData
    Close #FileNum

    MsgBox "File has been saved!", vbInformation, "Success"

End Sub

Now I'm getting "Run-time error '75': Path/File access error"

Seek and Ye shall find :) http://stackoverflow.com/questions/22051960/vba-winhttp-to-download-file-from-password-proteced-https-website — Jules, Feb 03 '16 at 02:42
If you asked the right question (which is how to download a file from a password-protected site in VBA) you would have found the linked post yourself. Your question has absolutely nothing to do with importing a CSV file into Excel; it's all about being able to download the file. Ask the question you need an answer to, instead of confusing and convoluting everything with non-relevant information. You'll get help much quicker (if you don't find an existing question that provides the answer already). :-) — Ken White, Feb 03 '16 at 02:45
Thanks for the help Ken! I very specifically need to not download the file however. I need to import it directly into Excel. Even temporarily saving it, importing it, and deleting it, won't work. — Mike K, Feb 03 '16 at 03:52
Why won't that work? Don't have access to local folders or is there a security issue? You basically need to find some kind of COM HTTP library that will let you load a text stream into a variable. Maybe this library has a method you can use: http://stackoverflow.com/questions/3119207/sending-http-requests-with-vba-from-word — Nick.Mc, Feb 03 '16 at 04:02
Here's more info on it - maybe you can use responsestream or responsetext. https://msdn.microsoft.com/en-us/library/windows/desktop/aa384106(v=vs.85).aspx — Nick.Mc, Feb 03 '16 at 04:03
Have you tried that using your url? Is there any error message? — Jules, Feb 03 '16 at 04:37
@MikeK What is the actual error message? tag my id or I won't see your reply. — Jules, Feb 04 '16 at 22:46
@Jules _Run-time error '-2147012858 (80072f06)':_ _Automation Error_ I really appreciate the help! — Mike K, Feb 05 '16 at 01:09
@Jules I tried to capture more detail on the error... the code that causes the error is "WHTTP.Send". If I use On Error Resume Next before that line, and then MsgBox the ErrDescription, I get "The host name in the certificate is invalid or does not match" — Mike K, Feb 05 '16 at 01:21
@Jules The search continues... from more Googling, and inspecting the certificate of the website, the certificate is for "portal.website.com" but I'm trying to access "webservices.website.com". Is there anyway to get around this? I don't care if the certificates don't match. — Mike K, Feb 05 '16 at 01:28
@Jules More Googling, but now I believe I'm at an impasse... I set WinHTTP to ignore Ssl error flags, and now I get this error: "The connection with the server was terminated abnormally". Sorry for sending so many messages... I believe I'm at the root of the problem now. — Mike K, Feb 05 '16 at 01:57
@mikek Is the server intranet or internet? When using winhttp, it uses IE setting proxy, etc. I would guess php ignore IE setting and connect directly to the server. — Jules, Feb 05 '16 at 02:45
@mikek, sorry I was wrong WinHTTP does not use IE proxy settings. XMLHTTPRequest does. So if you use proxy from your machine, you have to set up the proxy in the code. — Jules, Feb 05 '16 at 02:58
@Jules I actually got it working using 'SetCredentials' instead! — Mike K, Feb 05 '16 at 03:22

Import A CSV File into Excel (Requires Login/Pass)

0 Answers0