none
EXCEL VBA - Copy cell data to web page fields

    General discussion

  • Hi, so i have been searching the form before and I couldnt really find a solution for my webpage that I am trying to input data to.

    My Goal: To copy cells from my excel sheet to input fields on a already opened webpage (in order to simplify the process). I need this basically to fill out my timesheet for work. The website is a Oracle E-business online suite, and Im hoping its simple to do.

    I found code which logs into www.gmail .com successfully, however I was not able to modify it to work with my webpage I need it for.

    Here is the google code that currently functions well on my computer and with excel 2010:

    Sub Test()
    Const cURL = "http://mail.google.com" 'Enter the web address here
    Const cUsername = "XXXXXX" 'Enter your user name here
    Const cPassword = "XXXXXX" 'Enter your Password here
    Dim IE As InternetExplorer
    Dim doc As HTMLDocument
    Dim LoginForm As HTMLFormElement
    Dim UserNameInputBox As HTMLInputElement
    Dim PasswordInputBox As HTMLInputElement
    Dim SignInButton As HTMLInputButtonElement
    Dim HTMLelement As IHTMLElement
    Dim qt As QueryTable
    Set IE = New InternetExplorer
    IE.Visible = True
    IE.Navigate cURL
    'Wait for initial page to load
    Do While IE.ReadyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop
    Set doc = IE.Document
    'Get the only form on the page
    Set LoginForm = doc.forms(0)
    'Get the User Name textbox and populate it
    'input name="Email" id="Email" size="18" value="" class="gaia le val" type="text"
    Set UserNameInputBox = LoginForm.elements("Email")
    UserNameInputBox.Value = cUsername
    'Get the password textbox and populate it
    'input name="Passwd" id="Passwd" size="18" class="gaia le val" type="password"
    Set PasswordInputBox = LoginForm.elements("Passwd")
    PasswordInputBox.Value = cPassword
    'Get the form input button and click it
    'input class="gaia le button" name="signIn" id="signIn" value="Sign in" type="submit"
    Set SignInButton = LoginForm.elements("signIn")
    SignInButton.Click
    'Wait for the new page to load
    Do While IE.ReadyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop
    End Sub

    The modifications I would like to make to the above code is the following:

    1) instead of using "Const cUsername = "XXXXXX"", I would like to transfer the numbers in a cell in excel for example Const cellA1 = the contents in cell A1.

    2) Instead of loading a new page, I would like to run the macro on a already opened page (see the html code below) with the fields ready to be typed in.

    Below is the HTML code for the webpage i need to fill out on pastebin.com. As you can see (by saving it as .htm and loading it in a webbrowser), i need to fill out the fields for Project, Task, Type, hours, etc. and for each line.

    http://pastebin.com/QmMv7YKK

    So for example in my excel file, cell A1 = "myproject", I want this text/number "myproject" to appear in the first field in the HTML site.

    I hope someone can help with this,

    Regards,

    Jason.

    Sunday, October 21, 2012 1:50 AM

All replies

  • Sunday, October 21, 2012 5:16 PM
  • Hi, that was actually the first site i looked at, however I cannot seem to identify the "ID" tags in the html code that works. this is why i posted the html code.

    Still looking for a solution, thanks.

    Sunday, October 21, 2012 8:22 PM
  • What is the actual web site?

    The best way I know of doing this is to download and install the IE Developer Toolbar, or download and install Firebug, if you use Firefox as a web browser.  With the IE Developer Toolbar installed, navigate to your site, and hit F12.  You'll see all HTML elements there.  Search for the specific elements you want.


    Ryan Shuell

    Sunday, October 21, 2012 8:30 PM
  • i cant give you the site because its only accessibly on my companys intranet. The webpage is posted in my link above.

    I found the "id" for the first field, its "Attribute1_PROJECTS_1_N1_6577display"

    So in the code I posted above, I set the following:

    Set UserNameInputBox = LoginForm.elements("Attribute1_PROJECTS_1_N1_6577display")

    However, the macro stops at this point in the code:

    Do While IE.ReadyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop

    I cant figure out why..

    Sunday, October 21, 2012 11:00 PM
  • I'm pretty sure you don't need this:

    Dim qt As QueryTable

    I can almost guarantee that the code is not crashing on this line:

    Do While IE.ReadyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop

    All that does is make sure the page loads fully before continuing to the next line of code.

    I just tried your code sample and everything worked perfectly fine on my end.  Maybe you need to reboot your computer and try again . . .


    Ryan Shuell

    Sunday, October 21, 2012 11:12 PM
  • Hi Ryan,

    So i tried my code on another computer and it worked. but nearly 100% sure that it didnt work because on the computer I need it for, I dont have full admin rights, and writing to many directories is blocked. So im thinking, sometimes the code also blocks at

    Set doc = IE.Document

    Which maybe its creating a temporary file to write to, and my account has limited priveleges so it blocks it..

    Here is the code that works on a computer with full admin rights:

    Sub Test()
    'Const cURL = "http://mail.google.com" 'Enter the web address here
    Const cURL = "C:\Users\timesheet.htm"
    Const cUsername = "NAME" 'Enter your user name here
    Const cPassword = "XXXXXX" 'Enter your Password here
    'Const cBox1 = "1234" ' This is my addition
    Const Project11 = "Project 1"
    Const Task11 = "Task 1"
    Dim IE As InternetExplorer
    Dim doc As HTMLDocument
    Dim LoginForm As HTMLFormElement
    Dim UserNameInputBox As HTMLInputElement
    Dim PasswordInputBox As HTMLInputElement
    Dim SignInButton As HTMLInputButtonElement
    Dim HTMLelement As IHTMLElement
    'Dim qt As QueryTable
    'Dim Box1 As HTMLInputElement ' This is my addition
    Set IE = New SHDocVw.InternetExplorer
    'Set IE = New InternetExplorer
    'Set IE = New InternetExplorer
    IE.Visible = True
    IE.navigate cURL
    'Wait for initial page to load
    'Do
    'Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop
    'Loop Until oBrowser.readyState = READYSTATE_COMPLETE
    Do
    Loop Until IE.readyState = READYSTATE_COMPLETE '= 4
    Set doc = IE.Document
    'Get the only form on the page
    Set LoginForm = doc.forms(0)
    'Get the User Name textbox and populate it
    'input name="Email" id="Email" size="18" value="" class="gaia le val" type="text"
    Set UserNameInputBox = LoginForm.elements("Attribute1_PROJECTS_1_N1_6577display")
    UserNameInputBox.Value = Project11
    Set UserNameInputBox = LoginForm.elements("Attribute2_PROJECTS_1_N1_6577display")
    UserNameInputBox.Value = Task11
    End Sub

    This code successfully copies the strings Project 1 and Task 1, into fields. which i am happy about.

    Here are my new questions:

    1) How can I modify this code to that it does not have to create a temporary file somewhere (IE, make it work with limited admin rights).

    2) I have been searching everywhere to find out how to use a already opened IE window instead of opening a NEW IE windows. because to get to the webpage I need to fill out, i have to go through a series of logins, and it is very complicated. So I will already have this webpage open, and I just want to "activate" it, and then use the code above, to copy the fields I need.

    Below is some code I found that works somewhat, but I cant figure out how to merge it with my original code above.

    Here is the code I found to "activate" a specific IE window:

    Option Explicit
    Private Declare Function BringWindowToTop Lib "user32" (ByVal _
     hwnd As Long) As Long
    Private Declare Function FindWindow Lib "user32" Alias _
     "FindWindowA" (ByVal lpClassName As Any, ByVal lpWindowName _
     As Any) As Long
    Public Sub IEFrameToTop()
     Dim THandle As Long
     
     'THandle = FindWindow("IEFrame", vbEmpty)
      THandle = FindWindow("IEFrame", "Timesheet - Windows Internet Explorer")
      
     If THandle = 0 Then
      MsgBox "Could not find window.", vbOKOnly
     Else
      BringWindowToTop THandle
     End If
    End Sub


    Monday, October 22, 2012 4:48 PM
  • Here you go:

    http://vba-corner.livejournal.com/4623.html

     

    Set global variables and call the Function from the Sub.

     

    'finds an open IE site by checking the URL
    Function GetOpenIEByURL(ByVal i_URL As String) As SHDocVw.InternetExplorer
    Dim objShellWindows As New SHDocVw.ShellWindows

      'ignore errors when accessing the document property
      On Error Resume Next
      'loop over all Shell-Windows
      For Each GetOpenIEByURL In objShellWindows
        'if the document is of type HTMLDocument, it is an IE window
        If TypeName(GetOpenIEByURL.Document) = "HTMLDocument" Then
          'check the URL
          If GetOpenIEByURL.Document.URL = i_URL Then
            'leave, we found the right window
            Exit Function
          End If
        End If
      Next
    End Function


    Ryan Shuell

    Monday, October 22, 2012 8:53 PM
  • Hello Jason ,

    i am sure your project was complete i want to achieve the same task , could you please give steps that needs to performed to run the script successfully .. actually i am new to .vbs a well .

    Thanks

    Sunil 

    Thursday, October 31, 2013 9:23 AM