Results 1 to 14 of 14
  1. #1
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    FollowHyperlink (Excel 2003 VBA)

    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?
    Legare Coleman

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: FollowHyperlink (Excel 2003 VBA)

    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)

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FollowHyperlink (Excel 2003 VBA)

    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.
    Legare Coleman

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: FollowHyperlink (Excel 2003 VBA)

    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.
    Attached Files Attached Files

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FollowHyperlink (Excel 2003 VBA)

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

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FollowHyperlink (Excel 2003 VBA)

    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?
    Legare Coleman

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: FollowHyperlink (Excel 2003 VBA)

    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.

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FollowHyperlink (Excel 2003 VBA)

    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.
    Legare Coleman

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: FollowHyperlink (Excel 2003 VBA)

    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.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: FollowHyperlink (Excel 2003 VBA)

    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.

  11. #11
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: FollowHyperlink (Excel 2003 VBA)

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

    StuartR

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FollowHyperlink (Excel 2003 VBA)

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

  13. #13
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: FollowHyperlink (Excel 2003 VBA)

    Hi Legare,

    Perhaps this: http://www.tek-tips.com/faqs.cfm?fid=6400 might be useful.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  14. #14
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: FollowHyperlink (Excel 2003 VBA)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •