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.
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,
Please take a look at this:
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.
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..
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 . . .
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
Here you go:
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