Results 1 to 7 of 7

Hyperlink to Website - Passing Username and Password

This is a discussion on Hyperlink to Website - Passing Username and Password within the Excel Questions forums, part of the Question Forums category; Is there a way to use a hyperlink to a website in VBA which will pass the username and password ...

  1. #1
    New Member
    Join Date
    Sep 2008
    Posts
    43

    Default Hyperlink to Website - Passing Username and Password

    Is there a way to use a hyperlink to a website in VBA which will pass the username and password required by the website?

    The URL I am using is going to a secure site, which requires a username and password, to download a csv file. Currently when I follow the hyperlink a popup authentication window opens which stops the macro until the users enters in the values needed. An additional popup then occurs asking if the file should be opened in excel.

    I would like to be able to pass the needed credentials without having to compel the user to intervene. Is this possible using the hyperlink method?

    Are there other ways to access a csv file for download from a URL?

    John Martinez

  2. #2
    Board Regular hatman's Avatar
    Join Date
    Apr 2005
    Location
    Palmer, MA
    Posts
    2,606

    Default Re: Hyperlink to Website - Passing Username and Password

    I'm not certain how the PWD will intercat with this method, but have you tried the WinAPI URLDownloadToFile?

    Function Declaration:
    Code:
    Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" _
            (ByVal pCaller As Long, _
            ByVal szURL As String, _
            ByVal szFileName As String, _
            ByVal dwReserved As Long, _
            ByVal lpfnCB As Long) As Long
    As far as populating the uname and pwd, it should be possible to determine the Windows Handle of the new popup, capture it and automate it using teh Internet Explorer Object Model Library. However, this method is tricky, and time consuming to get right. If I remember correctly, you;ll need about a half-dozen different API calls to get it right. Alternatively, you may be able to make the SendKeys Function work, though it tends to be quite unreliable, in my experience.
    (XL2010 on Windows 7 Professional SP 1)

    H0mogenized pasteurized milk from confinement animals, and butter, cheeses, and yogurts made from such milk, may look the same and carry the same name, but the inference that they are the same as [raw unpasteurized milk from pasture-fed cows] is part of the insidious deception that has been perpetrated on (us) the consumers of industrialized food.

    -Ron Schmid, "The Untold Story of Milk"

  3. #3
    New Member
    Join Date
    Sep 2008
    Posts
    43

    Default Re: Hyperlink to Website - Passing Username and Password

    Not familiar with the use of API or the Internet explorer library. What is this WinAPI trying to do? Are there any other lines of code after the Function Declaration? Do I call the Function from Visual or from excel?

    Are any of properties of the Hyperlink method intended for this purpose? The MSDN instructions on the method leave something to be desired.

    Thanks,

    John M.

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824

    Default Re: Hyperlink to Website - Passing Username and Password

    I am fairly certain that you cannot url encode username and passwords. IE 6 and >??? Anyway, this would be the syntax if it works.

    https://Username:Password@Server.Domain

    https://tstom:1234@www.google.com

  5. #5
    Board Regular hatman's Avatar
    Join Date
    Apr 2005
    Location
    Palmer, MA
    Posts
    2,606

    Default Re: Hyperlink to Website - Passing Username and Password

    Quote Originally Posted by JohnCM View Post
    Not familiar with the use of API
    API = Application Programming Interface.

    I know enough to be dangerous. Basically, WinAPI calls allow you to call functions from the Windows Operating System Libraries, among other things. The idea is that you declare to the VBA Engine what Function you wish to call from what file/library, as well as it's input/output parameters. What I listed in my previous post is this declaration, as it would appear in a standard VBA Module.

    Here is an example of the usage, it will download teh MrExcel Articles Page File to your Temp Folder (assuming that the Board Software doesn't completely bowdlerize the address for security purposes...):

    Code:
    Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" _
            (ByVal pCaller As Long, _
            ByVal szURL As String, _
            ByVal szFileName As String, _
            ByVal dwReserved As Long, _
            ByVal lpfnCB As Long) As Long
       
    Sub Download()
       Const URL_File As String = "http://www.mrexcel.com/articles.shtml"
       Const Down_Load_Folder As String = "c:\temp\dl.html"
            
        URLDownloadToFile 0, URL_File, Down_Load_Folder, 0, 0
            
    End Sub
    Quote Originally Posted by JohnCM View Post
    or the Internet explorer library.
    Here is a thread where I have a post demonstrating how to use the Internet Explorer Object Library to automatically make some website selections. Of course, it gets much more difficult if you need to capture a new instance of IE that spawns as teh result of a button clisk... difficult, but not impossible. This Article talks about instancing of IE objects.
    (XL2010 on Windows 7 Professional SP 1)

    H0mogenized pasteurized milk from confinement animals, and butter, cheeses, and yogurts made from such milk, may look the same and carry the same name, but the inference that they are the same as [raw unpasteurized milk from pasture-fed cows] is part of the insidious deception that has been perpetrated on (us) the consumers of industrialized food.

    -Ron Schmid, "The Untold Story of Milk"

  6. #6
    New Member
    Join Date
    Sep 2008
    Posts
    43

    Default Re: Hyperlink to Website - Passing Username and Password

    Hatman,

    Thank you for the example and the discussion. I will delve into the world of API as it sounds fascinating.

    Thanks for opening my eyes to a new venue of tools.

    JM

  7. #7
    Board Regular hatman's Avatar
    Join Date
    Apr 2005
    Location
    Palmer, MA
    Posts
    2,606

    Default Re: Hyperlink to Website - Passing Username and Password

    Quote Originally Posted by JohnCM View Post
    Hatman,

    Thank you for the example and the discussion. I will delve into the world of API as it sounds fascinating.

    Thanks for opening my eyes to a new venue of tools.

    JM
    You are welcome. As I mentioned, this may not be the panacea it appears to be at first glance. I believe that this API uses a protocol other than HTTP, which means that in some cases, it can bypass certain types of security. However, many other types of security will prevent this direct download method. If it is the case that the security does in fact prevent this API download (most likely, IMO) then automation of teh IE session is probably your best bet...
    (XL2010 on Windows 7 Professional SP 1)

    H0mogenized pasteurized milk from confinement animals, and butter, cheeses, and yogurts made from such milk, may look the same and carry the same name, but the inference that they are the same as [raw unpasteurized milk from pasture-fed cows] is part of the insidious deception that has been perpetrated on (us) the consumers of industrialized food.

    -Ron Schmid, "The Untold Story of Milk"

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com