Passing user name and password with VBA to intranet log-in

Posted on 2011-03-02
Medium Priority
Last Modified: 2012-06-27
I have several processes that take about three hours to run.  Right now, the user needs to log onto the intranet, enter their credientials to get to the underying reporting package.  Since most of the users have their credientials "remembered", is there a way to do a SendKEy of some sort with VBA to OK the dialog box so the code can run, that is, get the excel worksheets from the intranet and downloaded into the local file?  I would like to run this process around 6:00 am, but this manual OKing of the log in data stops this.
Question by:ssmith94015
LVL 30

Expert Comment

ID: 35021072
I am sure it can be done using Windows APIs. I wouldn't recommend Sendkey though as it is highly unreliable.

Please tell me more about this intranet site? What is the caption of the window? A screen shot as well?


Author Comment

ID: 35021468
Hi Sid.  This is a site where all the client monthly base data information is held.  The VBA Excel process takes these 160 reports, puts them all in one master workbook local to the user and from there the client's individual reports are created.  These master workbooks hold the history as once the month is over, the data on the intranet is destroyed and a new month's data is available.  Only the URL to each of the Excel workbooks on the site is used, there actually is no user interface, it is only a pointer to the workbook on the intra net, this pointer is located in the VBA Excel workbooks that is used to process and only opens workbook once the URL is processes.  However, to get to the intranet site, login creditional must be passed.  Sorry, no screen shots as confidential data.  The log in is the same as the user's machine log in so I was hoping that may provide help.
LVL 12

Expert Comment

ID: 35021519
If it were a database, you could use Windows NT Authentication.  I could definitely be wrong, but I don't think Microsoft is going to write an API or something that allows you to retrieve the user's password.  It seems like that would be too scary of a security threat.
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

LVL 30

Accepted Solution

SiddharthRout earned 2000 total points
ID: 35021544
>>>Sorry, no screen shots as confidential data.

Well, with this little info, we don't have much to proceed on. However, lets talckle it in a different manner. When you say intranet, What kind of user interface are we talking about. You mentioned 'sendkey' so apparently there is a 'window' where you need to send them. You could use the FindWindow API to find that window and then either use SendMessage/PostMessage API to download your files.

Visit this link for info in APIs



Author Closing Comment

ID: 35021850
Yes, I know there is so little, but I think you are correct.  The logon dialog box appears, but then as I was thinking, it appears four times during the processing, but I am going to investigate the APIs.

Expert Comment

ID: 35022050
This is what I use to log-on to my mail account.

Now this deals with main window fields not pop-ups so I don't know if it will work.

but give it a go, change the details, it may be a starting point

Dim HDoc As HTMLDocument
Sub WebsiteLogon()

Dim htmlElement As IHTMLElement
Dim Url As String

On Error GoTo Err_Handler
    Url = "https://login.yahoo.com"
    Set browser = New InternetExplorer
    browser.Silent = True
    browser.navigate Url
    browser.Visible = True
    Loop Until browser.ReadyState = READYSTATE_COMPLETE
    Set HDoc = browser.Document
    HDoc.all.Email.Value = "sample@yahoomail.com"
    HDoc.all.passwd.Value = "xxxxxxxxxx"
    For Each htmlElement In HDoc.getElementsByTagName("input")
        If htmlElement.Type = "submit" Then htmlElement.Click: Exit For
    If Err <> 0 Then
        Debug.Assert Err = 0
        Resume Next
    End If
End Sub
LVL 30

Expert Comment

ID: 35022076
>>The logon dialog box appears, but then as I was thinking, it appears four times during the processing, but I am going to investigate the APIs.

Sure. Let me know if you get stuck. Any chance of posting the snapshot of the Logon Window. I am sure that won't have any confidential data?


Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Manually copying shapes and their assigned macros one by one to a new location can be tedious, but if you use the Excel utility workbook attached to this article, the process will be much quicker and easier.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question