Results 1 to 9 of 9

Thread: Excel vba form web browser question

  1. #1
    Join Date
    Oct 2011
    Posts
    78

    Excel vba form web browser question

    So I made a form to use for browsing the WWW. In the form initialization, I navigate to a target. In the form activation, I set the window size and position. The next thing I need to do is send some data to the form, as there is a log on screen which I must go through. I use 'Sendkeys' to send 'usrnam' and 'passwrd'. But if I place it in the initialization or activation subroutines, it never reaches the browser window. I apparently must complete the activation phase to use 'Sendkeys'. How do I get my name and pw sent to the browsed object.

  2. #2
    Join Date
    Aug 2011
    Posts
    700

    Re: Excel vba form web browser question

    When it asks for username and password, excel will hold the code execution. So no use putting code for SendKeys in excel itself. I made similar automation program, of checking for new data on a web site and updating the new data in excel. This program runs 1:00 AM in the night. What I did is at 1:00 AM I launch a Winword document (Task Scheduler). This winword document in it's open even launches the excel workbook through Shell command, so that the excel application ID is known. Allowing some time for the username and password dialog box to appear, winword sends username and password to the excel using AppActivate first. Code in Winword is something like this. (In the winword document, there is a single table with single cell, to store the ApplicationID).
    [CODE]
    Private Sub Document_Open()
    Application.OnTime Now + TimeValue("00:00:02"), "OpMyWeb"
    End Sub
    [.CODE]
    Then the procedures are in modules as under:
    Code:
    Sub OpMyWeb()
    Dim myIDnum As Long
    myIDnum = Shell("C:\Program Files\Microsoft Office\OFFICE11
    \EXCEL.EXE D:\IntraTrack\WebQuery.xls", vbNormalFocus)
    ThisDocument.Tables(1).Cell(1, 1).Select
    Selection.Text = myIDnum
    Application.OnTime Now + TimeValue("00:00:10"), "EnQuer"
    End Sub
    So allowing 10 seconds, Procedure EnQuer runs, which is only for clicking on 'Enable Auto Web Query'. (You may not need this, and you can jump to SendPass.)
    Code:
    Sub EnQuer()
    Dim myIDnum As Long
    ThisDocument.Tables(1).Cell(1, 1).Select
    myIDnum = Left(Selection.Text, Len(Selection.Text) - 2)
    AppActivate myIDnum
    SendKeys "{TAB}", True
    SendKeys "{ENTER}", True
    ThisDocument.Activate
    Application.OnTime Now + TimeValue("00:00:30"), "SendPass"
    End Sub
    So allowing 30 seconds for username & pwd box to appear, I run SendPass procedure, which sends the user name and passowrd, and then winword
    document is closed.
    Code:
    Sub SendPass()
    Dim myIDnum As Long
    ThisDocument.Tables(1).Cell(1, 1).Select
    myIDnum = Left(Selection.Text, Len(Selection.Text) - 2)
    AppActivate myIDnum
    SendKeys "username", True
    SendKeys "{TAB}", True
    SendKeys "password", True
    SendKeys "{ENTER}", True
    ThisDocument.Activate
    ThisDocument.Saved = True
    If Application.Windows.Count = 1 Then Application.Quit
    ThisDocument.Close
    End Sub

  3. #3
    Join Date
    Aug 2011
    Posts
    568

    Re: Excel vba form web browser question

    It is not necessary to create a word doc to enter username and password.(Atleast in some cases). I had similar problem. Thru VBA, create a new instance of IE and navigate to the URL which is asking username and password. Next, What you need to do is to view the source of the web page and find out the codename of the username field and password field.(If you have FrontPage installed, you can find it out almost instantly. In frontage, select normal tab, select the username field (where you will be entering the username) and then click on the html tab. The html code for the selected field will be high lighted. Note down the code name of the username. Repeat the same procedure for the password. Now, with the help of this data, you will be open the web page without manually entering the username and password. I am not a expert but it works for me. Hope it works for you also.

  4. #4
    Join Date
    Jul 2011
    Posts
    627

    Re: Excel vba form web browser question

    I am also interested in this. I edited the concerned webpage through frontpage and I got these lines(hope they are the relevant portions) :
    Code:
    <td align="right" nowrap class="bodywhite">Yahoo! ID:</td>
    <td align="right"><input name="login" size="17" value=""></td>
    </tr>
    <tr>
    <td align="right" nowrap class="bodywhite">Password:</td>
    <td align="right"><input name="passwd" size="17" value=""
    type="password"></td>
    Which are the codes. are they "login" and "passwd" highly thankful for your clarification.

  5. #5
    Join Date
    Jul 2011
    Posts
    638

    Re: Excel vba form web browser question

    Well, if username and password entry is in the web page script then these can be supplied through the VBA code (or even in the worst case, SendKeys will also work since Excel will not hold the code execution.) But if the site uses windows login, a separate dialogue box asking for username and password pops up. Here it is not possible to supply the username and password through VBA code. Excel will hold the execution of code. That's when you need to use SendKeys from another application (one of the ways, there could be better way which I would like to know.) Since OP mentioned SendKeys I assumed that that web site uses Windows login.

  6. #6
    Join Date
    Jul 2011
    Posts
    642

    Re: Excel vba form web browser question

    Since I was involved in web page scripts in my current project, I replied accordingly. I will also save your previous post for reference in future. Is there anyway to find out weather i.e. has generated any message box. I create a instance of i.e. and then navigate to the required url with username and password. If there is any error, then i.e. will generate a message box of the error and will return to the login page. In such case i would like to ask user to again input there username and password, problematically click on ok button of message box and again initiate the login process. User will not be able to see the error message box I kept i.e. hidden.

  7. #7
    Join Date
    Jun 2011
    Posts
    641

    Re: Excel vba form web browser question

    There is no any direct way to find out if IE has generated a message box (I am talking about Reference to MS Internet Control). However, if you are launching IE with a URL first time, if you give command to GoForward, it will normally generate error "Unspecified error", if no any dialogue box is active in IE. If a dialogue box is active in IE, then on GoForward, it gives another error "resource is in use." Using this, it is possible to determine if a IE has generated a dialogue box. Below is a sample code (Reference to MS Internet Control) In the below code I allow 10 seconds before giving the command and checking what error it has generated. If your site takes longer you can adjust the number of seconds. You can check the below code by purposefully giving wrong password.
    Code:
    Sub Rediculous()
    Dim IE As InternetExplorer, nCount As Long
    Set IE = New InternetExplorer
    IE.Navigate "Your website url"
    'your code for username/pwd but give WRONG PWD to test
    IE.Visible = True
    'below I allow 10 seconds to load
    'you can change the seconds as per your site speed
    nCount = Timer
    Do While Timer < nCount + 10
    DoEvents
    Loop
    On Error Resume Next
    IE.GoForward
    If InStr(1, Err.Description, "resource is in use", vbTextCompare) >
    0 Then
    'Now this means a dialog box is active in IE
    AppActivate IE
    'activate IE which by default activates the dailogue window
    SendKeys "{ENTER}", True
    ' your further code to send username and pwd
    End If
    On Error GoTo 0
    'Further code if no any dialogue box is active in IE
    End Sub

  8. #8
    Join Date
    Jun 2011
    Posts
    492

    Re: Excel vba form web browser question

    I got my username and password field names (userName & userPassword). From the source, <input type="image" src="images/button-login-t.gif">, I think this is the submit button. Then there is another line,
    Code:
    <form method="POST" action="/com.alphatrade.servlet.http.HttpLoginServlet"
    target="_top" ENCTYPE="application/x-www-form-urlencoded" name="loginForm"
    onsubmit="doLogin()">
    That must capture the 'Login' submit button action to perform the 'doLogin()' routine. How do I use this information to open this web page without manually entering the login information.

  9. #9
    Join Date
    Jun 2011
    Posts
    804

    Re: Excel vba form web browser question

    What above post discuss is on navigation with instance of IE. You are trying it for the WebQuery. It won't work with WebQuery. Your choices are.
    1. First manually open the site and login. Keep the site open, then run webquery. (Which I think you do at present, is that correct?)
    2. If above is correct then before the web query refresh, through VBA code you start IE, navigate to the url. You supply the username and password through the code. Leave the navigated site open. Then run your web query with Worksheets("xyz").QueryTables(1).Refresh. For opening the webpage through excel. In your VBA project add reference to "Microsoft Internet Control". Code could be as under:-
    Code:
    Dim IE As InternetExplorer, nCount as Long
    Set IE = New InternetExplorer
    IE.Navigate "http://in.finance.yahoo.com/p?v&k=pf_2&d=v6"
    'code for username/pwd , I don't know how.
    IE.Visible = True
    'now allow some time for IE to navigate and login 'below loop will give it 10 sec.
    Code:
    nCount = Timer
    Do While Timer < nCount + 10
    DoEvents
    Loop
    'Then refresh your webquery. Worksheets("xyz").QueryTables(1).Refresh. 'Allow about 30s for query refresh
    Code:
    nCount = Timer
    Do While Timer < nCount + 30
    DoEvents
    Loop
    'Then close IE. IE.Quit

Similar Threads

  1. Pictures appear in form of blue question mark
    By Niantic in forum Windows Software
    Replies: 4
    Last Post: 06-12-2010, 03:55 PM
  2. How to clear browser form fields
    By Rover in forum Software Development
    Replies: 3
    Last Post: 29-08-2009, 09:26 AM
  3. embed an excel file on a Outlook form
    By IMproper in forum Windows Software
    Replies: 3
    Last Post: 16-06-2009, 01:55 PM
  4. How to populate a merged cell from a form in excel
    By SoConfused in forum Windows Software
    Replies: 1
    Last Post: 09-06-2009, 08:35 PM
  5. AP ICET 2009 -Question abt Application Form
    By geetha001 in forum Education Career and Job Discussions
    Replies: 1
    Last Post: 17-03-2009, 08:37 AM

Posting Permissions

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