VBA to open a web-hosted file (1 Viewer)

Isaac

Lifelong Learner
Local time
Today, 04:56
Joined
Mar 14, 2017
Messages
5,386
Hi, I have a publicly available Excel file like this:

Code:
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?
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 04:56
Joined
Oct 29, 2018
Messages
15,590
Hi. Two options that come to mind immediately are HttpRequest class or URLDownload API (I hope I remembered that correctly).

Sent from phone...
 

conception_native_0123

Active member
Local time
Today, 06:56
Joined
Mar 13, 2021
Messages
969
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

Lifelong Learner
Local time
Today, 04:56
Joined
Mar 14, 2017
Messages
5,386
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:

Code:
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

I’m here to help
Staff member
Local time
Today, 04:56
Joined
Oct 29, 2018
Messages
15,590
HttpRequest worked great - thanks, dbGuy. Much appreciated.
Code I used in case it helps anyone:

Code:
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.
 

Users who are viewing this thread

Top Bottom