Hyperlink to Website - Passing Username and Password

[JohnCM]

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

 

[hatman]

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

Function Declaration:

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.

 

[JohnCM]

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.

 

[Legacy 98055]

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

 

[hatman]

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

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

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.

 

[JohnCM]

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

 

[hatman]

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

 

[Viz_Master]

I am trying to do something similar. The difference is they my Hyperlink is for Search Customer Code in our User Management System (backend). When I copy and paste the link in the chrome it works, but when I am clicking on Hyperlink it asks me to login (after login it just doesn't search)

This is my Hyperlink:
=HYPERLINK("https://ew.dptechnology.com/ew/sw/swcd.asp?ID="&E15,E15)

This is the URL, which I want to display without login:
https://ew.dptechnology.com/ew/sw/swcd.asp?ID=MAHA33464

Thanks In advance for answers.

 

[daverunt]

FYI:

https://support.microsoft.com/en-us/kb/834489