score:0

i will use vba to launch powershell and run powershell script to download whatever data wanted. it's pretty easy to launch another program in vba, so i am not going to describe it.

here is the powershell script to authenticate with ntlm by giving user/password, btw, you can use usedefaultcredentials=$true in the following code to use currently logged in user.

for these headers, i just copied from fiddler, you will have to correct them for your usage.

$url = "https://xxx.yyy/team/data.csv"

$request = [system.net.webrequest]::create($url)
$request.method="get"
$credcache = new-object system.net.credentialcache
$creds = new-object system.net.networkcredential("username","password", "domain")
$credcache.add("https://xxx.yyy", "ntlm", $creds)
$request.credentials = $credcache
$request.headers.add("upgrade-insecure-requests", "1")
$request.headers.add("dnt", "1")
$request.headers.add("accept-encoding", "gzip, deflate, br")
$request.headers.add("accept-language", "en-us,en;q=0.9")
$request.useragent = "mozilla/5.0 (windows nt 10.0; win64; x64) applewebkit/537.36 (khtml, like gecko) chrome/71.0.3578.98 safari/537.36"
$request.accept = "text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8"
$response = $request.getresponse()
$requeststream = $response.getresponsestream()
$readstream = new-object system.io.streamreader $requeststream
$data = $readstream.readtoend()

if all good, in fiddler, you will see something like following from http headers,

enter image description here enter image description here

powershell to do the auth and download work, vba to load the downloaded file.


Related Query

More Query from same tag