VBA to open a web-hosted file

[Isaac]

Hi, I have a publicly available Excel file like this:

https://something.something.com/something/01resources/FileName.xlsx

I need to be able to grab a copy of that file using VBA. At first I thought it might be as easy as using Excel VBA's Workbooks.Open(path/URL) method, but that returned "Unable to open the file, the file may not exist" (etc) - from the excel application object

Then I tried Application.FollowHyperlink (thinking, once it's opened, I'll use GetObject to set the Excel application object, then that object's Workbooks() to grab the now-open named workbook) - but that failed on the Application.FollowHyperlink - cannot open the specified file.

When the client simply sends me a word document with the link - I click on it, get the Security prompt - and then the file simply opens, directly in Excel, no problem.

What's a good method in VBA to either open this file in Excel, or actually Download a copy of it?

 

[theDBguy]

Hi. Two options that come to mind immediately are HttpRequest class or URLDownload API (I hope I remembered that correctly).

Sent from phone...

 

[arnelgp]

Excel & VBA: Download Internet Files Automatically - 2 Macros - My Engineering World

 

[conception_native_0123]

At first I thought it might be as easy as using Excel VBA's Workbooks.Open(path/URL) method, but that returned "Unable to open the file, the file may not exist" (etc) - from the excel application object

i hope this helps in understanding...

workbooks is just a local collection, so it doesn't apply to web links. at least that's what I read a while back. sounds like there is a solution here though, already,...

 

[Isaac]

Hi. Two options that come to mind immediately are HttpRequest class or URLDownload API (I hope I remembered that correctly).

Sent from phone...

HttpRequest worked great - thanks, dbGuy. Much appreciated.
Code I used in case it helps anyone:

Dim myURL As String
myURL = "here i put the direct hotlink to the file, which when clicked as a url, 'opened' directly in Excel"

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

If WinHttpReq.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.responseBody
    oStream.SaveToFile "C:\Users\file.xlsx", 2 ' 1 = no overwrite, 2 = overwrite
    oStream.Close
End If

 

[theDBguy]

HttpRequest worked great - thanks, dbGuy. Much appreciated.
Code I used in case it helps anyone:

Dim myURL As String
myURL = "here i put the direct hotlink to the file, which when clicked as a url, 'opened' directly in Excel"

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

If WinHttpReq.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.responseBody
    oStream.SaveToFile "C:\Users\file.xlsx", 2 ' 1 = no overwrite, 2 = overwrite
    oStream.Close
End If

Congratulations! Glad to hear you got it sorted out. Good luck with your project.