Download files from web facing sharepoint site using VBA

Wondering if it is possible to download files from a web facing sharepoint site using VBA.  The files (so far testing with small (30kb) png images) are in a library which requires a password.  Needs to work with Windows Security.  My test website is an Azure Sharepoint Farm.

I've tried using:
URLDownloadToFile - ok for regular website, but can't manage to connect to the access restricted website
Object (InterenetExplorer.Application) - can open the site, can see the image, but unable to download
CreateObject("WinHTTP.WinHTTPrequest.5.1") - does not seem to retrieve the image

I'd like to do this in VBA but concede that I might have to look at VB.Net, in which case I will need a lot more help ;-)

Cheers,
LVL 15
DrTribosAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MlandaTCommented:
Have a go with these:

Basic HTTP Authentication from VBA
http://ramblings.mcpher.com/Home/excelquirks/snippets/basicauth

Also: Excel 2007: use VBA to download & save CSV from URL
https://social.msdn.microsoft.com/Forums/en-US/bd0ee306-7bb5-4ce4-8341-edd9475f84ad/excel-2007-use-vba-to-download-save-csv-from-url
The following requires Internet Explorer but will download a file in VBA:

Dim myURL As String
myURL = "http://www.somesite.com/file.csv"

Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False
WinHttpReq.Send

myURL = WinHttpReq.ResponseBody
If WinHttpReq.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.ResponseBody
    oStream.SaveToFile ("C:\file.csv")
    oStream.Close
End If

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DrTribosAuthor Commented:
Hi Mlanda - thank you for your comments.  

It's mind boggling how many approaches there are!  In the end I bit the bullet and figured out how to create a VB.Net dll using Visual Studio.  So far it works, I will post the details once testing is complete.

Other than that I had a quick look at the links you posted, both use ADODB - something to look into.

Thanks,
MlandaTCommented:
I certainly think that VB.NET is the way to go! .NET really makes internet communications stuff a lot easier. VBA gives very limited options for this sort of stuff. It was made for an earlier time... when internet communications was not assumed.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.