PDA

View Full Version : Copy data from Excel to Internet Explorer



xxxjjttxxx
02-01-2007, 06:33 AM
I am trying to copy cell contents from Excel to an Internet Explorer form (Order application). I have been able to copy from excel to a mainframe session and back; I can't seem to figure out what syntax to use to activate and copy to the IE form. Using AppActivate I am able activate the Internet Explorer session. The "with application" command doesn't tab inside the IE form.

Sub NavigateTGSR()
ie = "Order application"

AppActivate ie
With Application
.SendKeys "{TAB}", True
.SendKeys "{TAB}", True
.SendKeys "a", True

End With
End Sub

Thanks in advance for any help.

Charlize
02-01-2007, 07:14 AM
Is it on an intranet or the internet ? What's the url your using to make an order ?

Charlize

xxxjjttxxx
02-01-2007, 07:22 AM
It's on the internet, but the website is password protected. I will already have logged into the website when i run the macro.

Charlize
02-01-2007, 08:38 AM
It's on the internet, but the website is password protected. I will already have logged into the website when i run the macro.
I'm afraid that I can't help you with so little background info. No url, not knowing which controls are used on the website to be filled in, how many forms on a page ...

You'll have to explore the page that is being used to make orders to know where what is needed to be filled in.

Give me at least the url for the site to show you how to automatically fill in a form based on data in excel.

Charlize

Justinlabenne
02-02-2007, 02:49 PM
This sample contains an old class module that I was building but never really finished to make it more portable: View the code in the standard module for how to login, and navigate to the page containing the form to fill out.

The FillWebForm method within the class will need to have the fields (coIEForm(0),etc.... modified for the web form you are submitting.

ivgayt
05-04-2007, 02:44 PM
I am in an issue similar to xxxjjttxxx, I have an excel file that has many columns with data and one of those columns is named "cas" and is filled with numbers like "105-57-7" that is technical data. I have like 3100 rows with data like this, and what I want is to obtain more data to fill the rest of the columns


by accessing the web page: apps3.fao.org/jecfa/flav_agents/flavag-q.jsp?language=en

There, if you look at the bottom right of the page, you will see a field named "CAS number", there you have three fields where you have to fill the number I told you, in the first one "105" in the second "57" and in the last one "7". After that if you click on the search button, another page will be opened with a lot of info. From that page I want to collect the Name, Latest JECFA evaluation, etc. and each one would be one column in my excel file.

So the issue here is that imagine do that manually, I would like that my excel file would take the data of the "cas number" for a row , and automatically be able to open thsi web page, fill the 3 cas number fields I told you and after that, obtain the data from the new page and copying it to excel, and then doing the same for the next row until it arrives to the 3100 item. Also, there are some cas numbers this database doesn't have, so it would after clicking on "search" it would open a web page but with no data and it would show the legend "No records found".

Please let me know if you can help me. :help

Thanks,
Ivonne
:hi:

Charlize
05-06-2007, 02:04 AM
To get you started.Sub go_for_it()
'put 105-57-7 in a cell and stay on that cell when you
'start the macro. normally the value will be filled in
'in the webform. This in the main routine.
OpenCasNo ActiveCell.Value, _
"http://apps3.fao.org/jecfa/flav_agents/flavag-q.jsp?language=en"
End Sub
Sub OpenCasNo(Vnumber As String, EmailSite As String)
If Vnumber = "" Or EmailSite = "" Then Exit Sub
Dim ie As Object, oDoc As Object ', oList As Object, oLists As Object
Dim ieForm As Variant
Dim WebPage As String
Dim wb As Workbook
Set ie = CreateObject("InternetExplorer.Application")

WebPage = EmailSite

ie.Visible = True

ie.Navigate WebPage

'Loop until IE pages is fully loaded
Do Until ie.ReadyState = 4 'READYSTATE_COMPLETE
Loop

Set oDoc = ie.Document
Set ieForm = oDoc.forms(0)

ieForm(7).innertext = Split(Vnumber, "-")(0)
ieForm(8).innertext = Split(Vnumber, "-")(1)
ieForm(9).innertext = Split(Vnumber, "-")(2)

ie.Document.forms(0).Submit

ie.Visible = True

Application.Wait (Now + TimeValue("0:00:5"))
' Select All Intenet Explorer data and then
ie.ExecWB 17, 0
'Copy to the clipboard
ie.ExecWB 12, 0
ie.Visible = False
ie.Quit
Set ie = Nothing
Application.Wait (Now + TimeValue("0:00:1"))
'Create new workbook where we are going to paste the clipboard
Set wb = Workbooks.Add
Application.Wait (Now + TimeValue("0:00:3"))
SendKeys "%"
Application.Wait (Now + TimeValue("0:00:1"))
'w and p must be changed to your local menu commands
'edit and paste (I think) so e and p
SendKeys "w"
Application.Wait (Now + TimeValue("0:00:1"))
SendKeys "p"
Application.Wait (Now + TimeValue("0:00:1"))
SendKeys "{UP}"
End Sub
One little remark. There are plenty of guys/girls at this forum who are capable of doing a lot more then me. I'm just a humble student at this forum.

Next step is to be able to read the html tables on the site instead of a copy paste action (that's what we have got now).

But it takes time to analyse the site and to get the names of the variables of a site (and we must hope that the sitebuilders don't change something on the lay-out and/or names.

Charlize

ivgayt
05-07-2007, 08:28 AM
Thanks so much Charlize!!! It worked perfectly!!! I just have to change a little things to have it fix for my needs, but the main thing is done!! :bow:

See you around, and if you say you are just a student, I wonder what things you will do when you finish school!!! Wish you the best.

:beerchug:
Cheers,
Ivonne

Charlize
05-07-2007, 02:33 PM
A little bit more finetuned. I Think this is more or less the thing you want. The formatting still needs to be done by hand (auto wordwrap in cell ...). But you can start with this. Test this before really using it. If you intend to do 3100 items you can change the ie.visible = truetoie.visible = falseYou could also use the same ie instance instead of recreating a new one everytime you process a casno. So close the ie instance after all the casno are done.

Charlize

Charlize
05-07-2007, 02:38 PM
See you around, and if you say you are just a student, I wonder what things you will do when you finish school!!! Wish you the best.Well Yvonne, I meant a student at this forum not at a school. But when does the learning ends :dunno ??? A couple of months ago I didn't now a thing about vba.

Charlize

ivgayt
05-09-2007, 09:23 AM
Hi again Charlize,

I agree with you, everybody are always students since everyday we are able to learn something new no matter what it is. :)

Well, regarding the code, I have tried to do something but I don't know why it doesn't work.

The code looks like this, the code I added is on blue,

Sub OpenCasNo(Vnumber As String, EmailSite As String)
If Vnumber = "" Or EmailSite = "" Then Exit Sub
Dim ie As Object, oDoc As Object ', oList As Object, oLists As Object
Dim ieForm As Variant
Dim WebPage As String
Dim wb As Workbook


'
Set ie = CreateObject("InternetExplorer.Application")

WebPage = EmailSite

ie.Visible = True

ie.Navigate WebPage

'Loop until IE pages is fully loaded
Do Until ie.ReadyState = 4 'READYSTATE_COMPLETE
Loop

Set oDoc = ie.Document
Set ieForm = oDoc.forms(0)

ieForm(7).innertext = Split(Vnumber, "-")(0)
ieForm(8).innertext = Split(Vnumber, "-")(1)
ieForm(9).innertext = Split(Vnumber, "-")(2)

ie.Document.forms(0).Submit

ie.Visible = True

Application.Wait (Now + TimeValue("0:00:5"))
' Select All Intenet Explorer data and then
ie.ExecWB 17, 0
'Copy to the clipboard
ie.ExecWB 12, 0
ie.Visible = False
ie.Quit
Set ie = Nothing
Application.Wait (Now + TimeValue("0:00:1"))
'Create new workbook where we are going to paste the clipboard
'Set wb = Workbooks.Add YO QUITE ESTA

''''''A?ADO DESDE AQUI
Sheets("Hoja2").Activate 'I add this

Application.Wait (Now + TimeValue("0:00:3"))
SendKeys "%"
Application.Wait (Now + TimeValue("0:00:1"))
'w and p must be changed to your local menu commands
'edit and paste (I think) so e and p, I use "e" y "g"
SendKeys "e"
Application.Wait (Now + TimeValue("0:00:1"))
SendKeys "g"
Application.Wait (Now + TimeValue("0:00:1"))
SendKeys "{UP}"

'my code starts here

ThisWorkbook.Sheets("Hoja2").Range("B10:B27").Select
Selection.Copy
Sheets("Datos GoodCents-Clave Job").Activate
ThisWorkbook.Sheets("Datos GoodCents-Clave Job").Range("K2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=
_False, Transpose:=True


End Sub

Ok, the issue here is that I changed not to open a new workbook, I just use the same workbook to run all the code, so when it has copied the info from IE, it just goes to the sheet called "Hoja2", there, it paste all the data from the clipboard, and everything it's fine until the last code I put in blue that has a label 'my code starts here. From there, it won't run correctly. What I want to do is to select from sheet "Hoja2" just the data I need for filling my other sheet in the same workbook that has the 3100 rows (sheet "Datos GoodCents-Clave Job") , so I just select thet data and want to copy to the other sheet trannsposing the info.

If I run this last code in another function or macro, it does what I need, but if I run it from this one go_for_it, I don't know what happens but it doesn't paste the info in sheet ("Hoja2") and just select empty cells and then it goes to the other sheet and it goes to a cell I didn't specified.

So that's the problem, I have tried many things but it is still the same. The thing of using the same instance of ie it's a good option, I haven't tried it, first, I would like this thing of copying the data works.

Thanks again,
Ivonne

Charlize
05-09-2007, 11:13 AM
Hi again Charlize,

I agree with you, everybody are always students since everyday we are able to learn something new no matter what it is. :)

Well, regarding the code, I have tried to do something but I don't know why it doesn't work.

The code looks like this, the code I added is on blue, ...Try running the zipfile that I posted as the second solution. This one deals with the copying of the tables on the webpage.

Regards,
Charlize

Charlize
05-11-2007, 04:26 PM
Was it usefull ? Just curious.

Norie
05-12-2007, 10:51 AM
Charlize

Very useful code, especially liked the GetAllTables sub.:)

Looked kind of familiar, wonder where I've seen it before.:rolleyes:

Charlize
05-12-2007, 11:32 AM
Charlize

Very useful code, especially liked the GetAllTables sub.:)

Looked kind of familiar, wonder where I've seen it before.:rolleyes:Yeah, I loved :love: it to, and still do. Why should I try to improve an already good piece of coding. I embrace it with the most possible gratitude and thanks the one who created it (Should I refer to the original creator ? Nothing was metionned in the original program.).

Charlize

Norie
05-12-2007, 11:46 AM
Charlize

I don't know where you got the exact code but I've a feeling it was myself that actually created at least the bare bones of it or something very similar.:)

Here's what I have in a sort of library file for automating the web.

Sub GetAllTables(d)
For Each e In d.all
If e.nodename = "TABLE" Then
Set t = e

tabno = tabno + 1
nextrow = nextrow + 1
Set rng = Range("B" & nextrow)
rng.Offset(, -1) = "Table " & tabno
For Each r In t.Rows
For Each c In r.Cells
rng.Value = c.innertext
Set rng = rng.Offset(, 1)
I = I + 1
Next c
nextrow = nextrow + 1
Set rng = rng.Offset(1, -I)
I = 0
Next r
End If
Next e
End Sub
The files contains another 24 or so modules each dealing with different aspects of this sort of thing.

Here's another, better coded example for extracting a single table.

Sub GetOneTable(d, n)
' d is the document
' n is the table to extract
Dim e As Object ' the elements of the document
Dim t As Object ' the table required
Dim r As Object ' the rows of the table
Dim c As Object ' the cells of the rows.
Dim I As Long
Dim J As Long
For Each e In d.all
If e.nodename = "TABLE" Then
J = J + 1
End If
If J = n Then
Set t = e

tabno = tabno + 1
nextrow = nextrow + 1
Set rng = Range("A" & nextrow)
For Each r In t.Rows
For Each c In r.Cells
rng.Value = c.innertext
Set rng = rng.Offset(, 1)
I = I + 1
Next c
nextrow = nextrow + 1
Set rng = rng.Offset(1, -I)
I = 0
Next r
Exit For
End If

Next e

End Sub
I say better coded because I've actually declared variables and commented the code, well a little anyway.

Most of the code in this file is pretty dirty. eg undeclared/untyped variables, unreference ranges etc.

Charlize
05-12-2007, 12:17 PM
Charlize

I don't know where you got the exact code but I've a feeling it was myself that actually created at least the bare bones of it or something very similar.:)Trust your feelings, I've just added some declarations because the code will not work due to 'option explicit' and a little tweaking to import to another worksheet. In this case I looked where the table that she needs, was situated on the import-sheet. I was thinking about only importing table 2 (because that was the one she needed) to reduce loading time. Should I pass it to the function or hardcode it in the function ? (just reread your post and saw that it was already done)

Charlize

Norie
05-12-2007, 01:52 PM
Charlize

Most of the code I've written for this sort of thing has been quick and dirty.

And many times I've had to refuse helping OPs because either they won't supply the URL or they 'appear' to be trying to access secure sites.

Normally when that happens I direct the OP to this site (http://msdn.microsoft.com/workshop/author/dhtml/reference/dhtml_reference_entry.asp).

It's where I picked up a lot of stuff.:)

ivgayt
05-13-2007, 01:18 PM
I don't know what have happened because I was not able to download the file you created for me, but after the post you said I shoud've tried with the zip file, I then could downloaded it and what I did it was just to paste my 3000 or more rows after the first and run the macro, but then, errors started to appear, speacially in the part where you set and create the internet explorer object and then when it becomes visible and another errors, but it doesn't matter, instead of doing all the 3000... rows I pasted them from 200 to 200, so errors don't appear with the same frequency and I have tried to fixed but I couldn't but as I told you it is working just as I expect and I have processed 2000 rows so I almost finish. So thanks again and I read what you talked to Norie, and I understood you adapted this code for me, and well, anyway it is very useful and I like this kind of sites that can help you a lot. I'm also a student and will keep looking at this site and I hope I can help as you someday and that we keep in touch.

:friends:

See Ya!!

Norie
05-13-2007, 01:32 PM
The problem with this sort of thing is that you really need to understand the design of the website you are trying to get the data from.

I downloaded Charlize's workbook and ran the code, it worked placing the relevant data in the worksheet.:)

Charlize
05-13-2007, 02:06 PM
I've done it with only one number, not 3000. So I don't know if every number you pass through the website has a valid lookup return. If there wasn't any info found, what webpage is returned ? If the returned error notice is in the same table as the other data, you could check on that. If not, we'll have to check every table. Problem is that you'll have to know every possible result from a query that you submit.

- Another possible change is some errorchecking on the internetconnection.
- Another one is a check on the name (B) of a number in column A. If present, skip number and go on with rest. This way of working prevents from that copy and paste by 200. You continue where you have left (so if connection is gone, error message, tomorrow you want to continue with processing, just hit button and macro will continue where importing has stopped).
- A progressbar ...

Just some ideas to further finetune this (if you want to play with it)

Charlize

Charlize
05-13-2007, 02:10 PM
The problem with this sort of thing is that you really need to understand the design of the website you are trying to get the data from.

I downloaded Charlize's workbook and ran the code, it worked placing the relevant data in the worksheet.:)A lot of debugging with F8 and plenty of notes on a piece of paper ... (and maybe some coffee to).

And from time to time some sneak previews of the html coding.

xxxjjttxxx
07-24-2007, 07:38 AM
charlize - i copied the code for "go_for_it" and tried to run it. i follow all the steps of this macro but have some issues. the website address has changed, which may contribute to some problems. i have to comment out the "ieForm(x).innertext" lines. i run this macro line by line and when i get to the webpage i manually fill in the (157 7 5). if i don't comment out the lines i get the following error: (Run-time error '91: Object variable or With block variable not set)

the macro copies the data to the clipboard, but the "%" "e" "p" doesn't paste the data to the new workbook.

the form i'm trying to fill in is on my corporate intranet so you wouldn't be able to access it. how do i find the values for x ("ieForm(x).innertext")? i viewed the source for the webpage but i'm having trouble deciphering what they are. i also have a couple of drop-down boxes that need to be selected. can macros handle that task?

Norie
07-24-2007, 10:51 AM
Did you not see the comments regarding needing the URL to help with this sort of thing?

xxxjjttxxx
07-26-2007, 11:27 AM
thanks norie. Did you not see the third paragraph of my above message regarding URL accessibility?

Charlize
07-26-2007, 04:13 PM
thanks norie. Did you not see the third paragraph of my above message regarding URL accessibility?That's the problem. If I can't see the page, I'm not able to debug the coding and to see which formvariables are used on the form. The code I provide you know, loops through the page you fill in for the webpage and looks for a form. If it finds one, it loops through that form and tries to find every possible variable. It's now up to you to see what you need to know to fill in the form. This is my final attempt. Option Explicit
Sub Fill_in_Intranet()
Call Intranet("First value", "Second value")
End Sub
Sub Intranet(vInputIntoForm As String, vInputIntoForm2 As String)
Dim ie As Object, oDoc As Object
Dim ieForm As Variant, form As Variant
Dim WebPage As String
Dim i As Integer

Set ie = CreateObject("InternetExplorer.Application")
'Here you fill in your intranetpage
WebPage = "\\SERVER\Intranet .html"

ie.Visible = True

ie.Navigate WebPage

'Loop until IE pages is fully loaded
Do Until ie.ReadyState = 4 'READYSTATE_COMPLETE
Loop

Set oDoc = ie.Document
Set ieForm = oDoc.forms(0)

For Each form In ieForm
'You have to have the direct window in vbe (CTRL+G - shortcut key combination)
'When you look in this window, maybe you can get the name of the variable
'of the form. When you find one, replace FormID-variable his name with the name
'that you can see in the direct window (exactly as you see it)
'When the value fills in, you can place a ' before the debug.print line
'If you have to fill in more variables, you have to change the coding a little.
'*** EXAMPLE ****
'If form.ID = "FormID-variable his name" Then
' form.innertext = vInputIntoForm
'elseif form.ID = "FormID-variable no 2 his name" then
' form.innertext = vInputIntoForm2
'End If
'
'If you want to pass more variables to this procedure, you have to declare them
'like this : Sub Intranet(vInputIntoForm As String, vInputIntoForm2 As String)
Debug.Print form.outerHTML, i
If form.ID = "FormID-variable his name" Then
form.innertext = vInputIntoForm
End If
i = i + 1
Next
'Submit the document with the values you filled in
ie.Document.forms(0).Submit

Do Until ie.ReadyState = 4 'READYSTATE_COMPLETE
Loop

Set ie = Nothing
End Sub

xxxjjttxxx
08-01-2007, 06:02 AM
thanks for the additional code charlize. i'm trying to decipher the coding of the intranet form; it looks like the coding is all over the place. i would have pasted some of the code (i honestly didn't think of that before) if you thought that would have helped. thanks again.

Charlize
08-01-2007, 06:22 AM
You have to look ad the sourcecode of the page. In ie use page, view source. Then you search for the word 'form' without '. Look in the neighbourhood of the word for variables. Also wright down how many times you find form in the page (if you need to know how many forms there are on a page). Form(0) is the first form, so we count from zero. And there is form and /form. /form means the end of the form.

Charlize
08-01-2007, 06:36 AM
Try this to find some values. Look in sheet1 when you run this thing when sheet1 is active. It will start at A1 and puts every declaration of the form in the sheet. You have to look at input type (probably not the hidden ones). Sub Intranet_form_values()
Dim ie As Object, oDoc As Object
Dim ieForm As Variant, form As Variant
Dim WebPage As String
Dim i As Integer
Dim lrow As Long
Set ie = CreateObject("InternetExplorer.Application")
'Here you fill in your intranetpage
WebPage = "http://www.gmail.com"
lrow = 1
ie.Visible = True
ie.Navigate WebPage
'Loop until IE pages is fully loaded
Do Until ie.ReadyState = 4 'READYSTATE_COMPLETE
Loop
Set oDoc = ie.Document
Set ieForm = oDoc.forms(0)
For Each form In ieForm
'write the values of the variables in sheet1 and look there for name =
Range("A" & lrow).Value = form.outerHTML
lrow = lrow + 1
Next
Set ie = Nothing
End Sub