FollowHyperlink (Excel 2003 VBA)

Topic

New Reply

Based on data entered by a user, I can develop the http: address of a file that I need to download from a website. It looks like the FollowHyperlink method will download the file based on the address I pass to it. However, it does not allow me to specify where tdhe file is stored and it also looks like it will try to launch the file using the application that the file extension indicates should be used. I need to have the file stored in a specific folder where my code can manipulate it and I do not want it launched. Is there some other method that will do this?

Reply | Quote

Replies

FollowHyperlink downloads a file to your browser cache, then opens it. You could save the file to the desired location after it has been opened.

Alternatively, you can use the code from Download File Using HTTP to download the file to the desired location.
Note: on my PC, Microsoft Internet Transfer Control wasn’t listed in Tools | References, so I had to browse for C:WindowsSystem32msinet.ocx. The code worked in a small test (I downloaded an attachment to a post in the Excel forum)

Reply | Quote

Thanks. However, I have just had to buy a new computer and much to my dislike the new one came with VISTA Home Premimum installed. From what I can find out by searching the MS web site, MSINET.OCX does not come with VISTA, and I can’t find where it was replaced with something else. It also looks like the old MSINET.OCX can not be installed on VISTA.

Reply | Quote

I have attached a zipped workbook that contains code using the Wininet.dll file that comes with Internet Explorer, I assume that it’s available on Windows Vista too.
The class module HTTP in the workbook is derived from Modules: Internet Data Transfer Library on the Access Web. I removed Access-specific elements and unused code.

The procedure to use is DownloadFile, see TestDownload for an example of its use:

Sub TestDownload()
DownloadFile “http://www.mvps.org/access/acknowledge.htm”, “C:test.htm”
End Sub

I hope that this works on Vista.

Reply | Quote

Thanks, I’ll take a look at that. WININET.DLL does seem to be there.

Reply | Quote

I thought that was going to work. I tested it by downloading a .xls file from here in the lounge and everything went perfectly. However, when I tried it on the real file that I need to download (a 23meg .mpg file), the file I ended up with was 12kb. I didn’t get any reported error. Do you know if there is some limit on what can be downloaded using this?

Reply | Quote

I successfully downloaded a 21 MB video using the code, so it works for large files too.
Two things to look out for:
– Make sure that you have an URL that points to the file that you want to download, not to the web page that contains the download link.
– Make sure that the file can actually be downloaded – if it’s provided as streaming video that may not be possible.

Reply | Quote

OK, I think I see the problem. The download is from a proprietary site which I have to log onto before I can download the files. I thought that, if I was currently logged onto the site in IE, then I could use this to download the files.. However, it looks like the 12kb file is actually the HTML for a logon page. Any way around that.

Reply | Quote

I’m afraid I don’t have a solution for that. I don’t think that SendKeys to send the username and password will work, but you might give it a try.

Reply | Quote

The ability to logon is present in the Wininet API functions, but how to invoke it is beyond me and I cannot find a relevant example in Google or the newsgroups. Perhaps someone who is more familiar with these API functions can help.

Reply | Quote

According to this MSDN article, you specify the username and password as parameters to the Wininet internetconnect call.

StuartR

Reply | Quote

Thanks. I played ariund with InternetConnect but I can’t figure out how to make it work.

Reply | Quote

Here is an example of how to declare InternetConnect from Visual Basic. This site also has examples of many other Wininet calls
Here is an example of using all the Wininet API calls in sequence (but not from VB)
Here is an example of logging in to an ftp server and fetching a single file from an ftp server in VB.

StuartR

Reply | Quote

Hi Legare,

Perhaps this: http://www.tek-tips.com/faqs.cfm?fid=6400%5B/url%5D might be useful.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote