Copy from Internet Explorer & Paste in Excel

[Uma Nandan]

Hi,

I would want some one to help me with the following:

we hv huge date to copy from internet explorer to Excel not with one click
however with many by selecting what is required.

I am doing the following as of now:
Select the data from IE by highlighting it and then do ctrl+c and go to
Excel and do ctrl+v or f4.

Internet Explorer: MS Excel:
A
B
1 Company Name Name
Copy " citigroup" 2 Paste "Citigroup" Paste "Pandit"
copy " Pandit" 3 Paste "Lehman" Paste
"David Wishon"
copy "Lehman"
Copy "David Wishon"

Is there any way that i can do this copy past in single click instead doing :
select - rightclick - copy - go to excel - rightclick-paste
Select - ctrl+C, go to excel, select the cell, ctrl+v or use F4.

is ther any way i can just put a button in excel to copy from IE & paste the
same in Excel - current active cell.

Thanks in advance for your help.

Regards,
Uma

 

[Joel]

There are two methods I have used

1) Perform a web query from worksheet

Data - Import External Data - New Web Query

Then enter the URL and select the table you are looking for. You can record
a macro while doing this. the recorded macro may be able to be modified to
automate everything.


2) You can open an Internet Explorer application from an excel macro to get
the data. Below is an example. Put this code into Excel and create a
Worksheet called "Dealers"

Sub GetDealers()
'Dim PageNumber As Object
CR = Chr(13)
LF = Chr(10)

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

URL = "http://www.nissanusa.com/apps/dealerlocator"
Request = "?zipCode=07508&tool=Home.Locator"

'get web page
IE.Navigate2 URL & Request
Do While IE.readyState <> 4
DoEvents
Loop

'get search button
Set but = IE.document.getElementById("mainSearchButton")
'put distance in listbox on webpage
Set radius = IE.document.getElementById("radius")
radius.Value = "100"


'search again a larger distance
'Select Search button and activate
but.Select
but.Click
Set SearchResults = IE.document.getElementById("searchResults")

On Error Resume Next ' Defer error handling.
Do
Err.Clear
Set PageNumber = IE.document.getElementById("pageNumber")
Pages = PageNumber.Value
DoEvents
Loop While Err.Number <> 0
On Error GoTo 0


With Sheets("Dealers")
.Cells.ClearContents
RowCount = 1

For PageCount = 1 To PageNumber.Length
PageNumber.Value = Format(PageCount, "@")
PageNumber.onchange

For Each Chld In SearchResults.Children

If Chld.innertext = "" Then
Exit For
End If
Set DealerNumberObj = _
Chld.getelementsbytagname("A")
DealerNumberStr = DealerNumberObj.Item(1).pathname
dealerNumber = _
Val(Mid(DealerNumberStr, InStr(DealerNumberStr, "'") + 1))
.Cells(RowCount, "A") = dealerNumber

ColCount = 2
dealer = Chld.innertext
Do While InStr(dealer, CR) > 0
Data = Trim(Left(dealer, InStr(dealer, CR) - 1))

'remove leading CR and LF
Do While Left(Data, 1) = LF Or _
Left(Data, 1) = CR

Data = Mid(Data, 2)
Loop
dealer = Trim(Mid(dealer, InStr(dealer, CR) + 1))
If InStr(Data, "(") > 0 And _
ColCount = 4 Then

Distance = Trim(Mid(Data, InStr(Data, "(") + 1))
Distance = Trim(Left(Distance, InStr(Distance, ")") - 1))
CityState = Trim(Left(Data, InStr(Data, "(") - 1))
.Cells(RowCount, ColCount) = CityState
.Cells(RowCount, (ColCount + 1)) = Distance
ColCount = ColCount + 2
Else
.Cells(RowCount, ColCount) = Data
ColCount = ColCount + 1
End If
Loop

'remove leading CR and LF
Do While Left(dealer, 1) = LF Or _
Left(dealer, 1) = CR

dealer = Mid(dealer, 2)
Loop
.Cells(RowCount, ColCount) = dealer
RowCount = RowCount + 1
Next Chld
Next PageCount
End With
End Sub

 

[Uma Nandan]

Hi Joel,

Thank you so much. I loved it. I was excited to see the data copied into
Excell in few seconds.

I can use your 2nd option incase if i have data in table format in IE.
However the work we do does't have such kind of data all ways.

Eg: http://www.lehman.com/who/bios/
when you look at the above link for example, from this we need to Copy Name,
Title, Bio...So can you suggest something the way you have suggested below in
case 2.

Thanks & REgards,
Uma

 

[Joel]

I use a combination of methods to get the results below. I often go to the
internet explorer and look at View - Souce to see the actual HTML code to
help me get to the solution. I do some experimentations before I get my
final code.

Create for worksheets in you workbook Sheet1 to Sheet4. Then run the code
below. Look at each sheet to see how I finally got my final results in
Sheet4.



Sub GetStaff()

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

URL = "http://www.lehman.com/who/bios"

'get web page
IE.Navigate2 URL
Do While IE.readyState <> 4
DoEvents
Loop

Set SeniorManagement = IE.document.getelementsbytagname("A")

With Sheets("Sheet1")
RowCount = 1
For Each itm In SeniorManagement
.Range("A" & RowCount) = itm.Name
.Range("B" & RowCount) = itm.classname
.Range("C" & RowCount) = Left(itm.innertext, 1024)
RowCount = RowCount + 1
Next itm

End With
With Sheets("Sheet2")
RowCount = 1
For Each itm In SeniorManagement
If itm.classname = "a11purplemedium" Then
.Range("A" & RowCount) = itm.Name
.Range("B" & RowCount) = itm.classname
.Range("C" & RowCount) = Left(itm.innertext, 1024)
RowCount = RowCount + 1
End If
Next itm

End With
With Sheets("Sheet3")
RowCount = 1
For Each itm In IE.document.all
.Range("A" & RowCount) = itm.tagname
.Range("B" & RowCount) = itm.classname
.Range("C" & RowCount) = Left(itm.innertext, 1024)
RowCount = RowCount + 1
Next itm

End With
With Sheets("Sheet4")
RowCount = 0
State = GetManagement
For Each itm In IE.document.all
If itm.tagname = "SPAN" Then
RowCount = RowCount + 1
.Range("A" & RowCount) = itm.innertext
First = True
End If
If itm.tagname = "I" Then
.Range("B" & RowCount) = itm.innertext
End If
If itm.tagname = "P" And _
itm.tagname <> "" Then

If First = True Then
First = False
Else
RowCount = RowCount + 1
End If
.Range("C" & RowCount) = itm.innertext
End If
Next itm
End With

End Sub

 

[Uma Nandan]

Joel - Iam telling you ..iam amazed to see these reults. Thank you so much.

Ur a VBA Guru

I really can not think this much & write code. Have understood with your
Excellent examples.
Once again thanks for your support.

 

[Joel]

Here are some additional tips

1) Notice on the 1st 2 sheets there is a tagname "NAME" and the last 2 there
is "tagName". Not al items have the same properties. One thing I do is add
break points in the code using F9. then add a watch itm like this. I
highlight with mouse itm (any variable in the code). Then right click the
highlighted itm and select "Add Watch". On the pop up I press ok. Then when
I hit a break poit I expand the watch item by pressing the "+".

some properties to look at are
a) InnerText and innerHtml (formated)
b) children - sometimes has data
c) sibling
d) all
e) OutterText and OuterHtML

Note: the text is sometimes very long and causes memory errors. that is why
in some of my code I used LEFT to limit the string to 1024 characters.


2) tags in the source code start and end with angle brackets like

<SPAN /SPAN> sometimes the close bracket
doesn't include the name <SPAN />

I initially in your example looked at the source and saw the Stafff names
where inside the tag <A....... /A>. that was my first approach. I
then added a break point and looked at the watch window for SeniorManagement
.. I was hoping there was a properties for the Position and the Description.
When everything fails I always rever back to "For Each itm In
IE.document.all" which works for all webpages.

3) You can also find classnames like this

Set SearchResults = IE.document.getElementById("searchResults")

class names in the source code look like id=searchResults