I wrote some code for retrieving web data and it works fine, except
that a little part of the table is not captured.
There is a "cell" in the table with the label "E-Mail" , followed by a
mail address underneath, and then the table continues.

Everything is captured, including the word "E-mail", but not the e-
mail address itself.

I have the same problem when I do the web query through the Data menu.

However, if I select the web table manually and paste it in Excel I
get everything properly.
Is there anything I could do in my code to get this solved?
(Excel 2007)
Thank you very much
Herman

If possible, post your url and what you want.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

wrote in message
...

I wrote some code for retrieving web data and it works fine, except
that a little part of the table is not captured.
There is a "cell" in the table with the label "E-Mail" , followed by a
mail address underneath, and then the table continues.

Everything is captured, including the word "E-mail", but not the e-
mail address itself.

I have the same problem when I do the web query through the Data menu.

However, if I select the web table manually and paste it in Excel I
get everything properly.
Is there anything I could do in my code to get this solved?
(Excel 2007)
Thank you very much
Herman

On 23 aug, 22:11, "Don Guillett" wrote:
If possible, post your url and what you want.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
wrote in message

...





I wrote some code for retrieving web data and it works fine, except
that a little part of the table is not captured.
There is a "cell" in the table with the label "E-Mail" , followed by a
mail address underneath, and then the table continues.

Everything is captured, including the word "E-mail", but not the e-
mail address itself.

I have the same problem when I do the web query through the Data menu.

However, if I select the web table manually and paste it in Excel I
get everything properly.
Is there anything I could do in my code to get this solved?
(Excel 2007)
Thank you very much
Herman- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

OK Don,
Here is the code, with the URL.
Note that unlike all other data the E-mail address does not appear in
the Excel table.
Thanks for your time

On 23 aug, 22:40, Herman wrote:
On 23 aug, 22:11, "Don Guillett" wrote:





If possible, post your url and what you want.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
wrote in message

...

I wrote some code for retrieving web data and it works fine, except
that a little part of the table is not captured.
There is a "cell" in the table with the label "E-Mail" , followed by a
mail address underneath, and then the table continues.

Everything is captured, including the word "E-mail", but not the e-
mail address itself.

I have the same problem when I do the web query through the Data menu.

However, if I select the web table manually and paste it in Excel I
get everything properly.
Is there anything I could do in my code to get this solved?
(Excel 2007)
Thank you very much
Herman- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

OK Don,
Here is the code, with the URL.
Note that unlike all other data the E-mail address does not appear in
the Excel table.
Thanks for your time- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Oops, forgot to paste it !!

Sub Macro3()
Dim QTL As QueryTable
Set QTL = ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.iec-iab.be/nl/contactgegevens/c3fb7c06-29a4-
dd11-96ed-005056bd424d" _
, Destination:=Range("$D$1"))
With QTL
.Name = "c3fb7c06-29a4-dd11-96ed-005056bd424d"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

I couldn't do it either.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Herman" wrote in message
...
On 23 aug, 22:11, "Don Guillett" wrote:
If possible, post your url and what you want.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
wrote in message

...





I wrote some code for retrieving web data and it works fine, except
that a little part of the table is not captured.
There is a "cell" in the table with the label "E-Mail" , followed by a
mail address underneath, and then the table continues.

Everything is captured, including the word "E-mail", but not the e-
mail address itself.

I have the same problem when I do the web query through the Data menu.

However, if I select the web table manually and paste it in Excel I
get everything properly.
Is there anything I could do in my code to get this solved?
(Excel 2007)
Thank you very much
Herman- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

OK Don,
Here is the code, with the URL.
Note that unlike all other data the E-mail address does not appear in
the Excel table.
Thanks for your time

I can do anything. Try this!

Sub WebQuery()

URL = "//www.iec-iab.be/nl/contactgegevens/" & _
"c3fb7c06-29a4-dd11-96ed-005056bd424d"

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


'get web page
IE.Navigate2 URL
Do While IE.readystate < 4 Or _
IE.Busy = True

DoEvents
Loop



RowCount = 2
'find data between first and 2nd H2 tag
State = "FindB2"
For Each itm In IE.document.all
Select Case State

Case "FindB2"
If itm.tagname = "H2" Then
State = "GetData"
End If
Case "GetData"
If itm.tagname = "H2" Then
'get next section of data
State = "FindB2"
End If

Select Case itm.tagname

Case "DIV"
colCount = 4 'start each row in column D
RowCount = RowCount + 1
Case "SPAN"
Cells(RowCount, colCount) = itm.innertext
colCount = colCount + 1
End Select
End Select
Next itm
IE.Quit
End Sub




"Don Guillett" wrote:

I couldn't do it either.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Herman" wrote in message
...
On 23 aug, 22:11, "Don Guillett" wrote:
If possible, post your url and what you want.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
wrote in message

...





I wrote some code for retrieving web data and it works fine, except
that a little part of the table is not captured.
There is a "cell" in the table with the label "E-Mail" , followed by a
mail address underneath, and then the table continues.

Everything is captured, including the word "E-mail", but not the e-
mail address itself.

I have the same problem when I do the web query through the Data menu.

However, if I select the web table manually and paste it in Excel I
get everything properly.
Is there anything I could do in my code to get this solved?
(Excel 2007)
Thank you very much
Herman- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

OK Don,
Here is the code, with the URL.
Note that unlike all other data the E-mail address does not appear in
the Excel table.
Thanks for your time

Joel, Worked just fine. I need to learn how to do this. Can you explain the
process and how to get the tags.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Joel" wrote in message
...
I can do anything. Try this!

Sub WebQuery()

URL = "//www.iec-iab.be/nl/contactgegevens/" & _
"c3fb7c06-29a4-dd11-96ed-005056bd424d"

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


'get web page
IE.Navigate2 URL
Do While IE.readystate < 4 Or _
IE.Busy = True

DoEvents
Loop



RowCount = 2
'find data between first and 2nd H2 tag
State = "FindB2"
For Each itm In IE.document.all
Select Case State

Case "FindB2"
If itm.tagname = "H2" Then
State = "GetData"
End If
Case "GetData"
If itm.tagname = "H2" Then
'get next section of data
State = "FindB2"
End If

Select Case itm.tagname

Case "DIV"
colCount = 4 'start each row in column D
RowCount = RowCount + 1
Case "SPAN"
Cells(RowCount, colCount) = itm.innertext
colCount = colCount + 1
End Select
End Select
Next itm
IE.Quit
End Sub




"Don Guillett" wrote:

I couldn't do it either.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Herman" wrote in message
...
On 23 aug, 22:11, "Don Guillett" wrote:
If possible, post your url and what you want.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
wrote in message

...





I wrote some code for retrieving web data and it works fine, except
that a little part of the table is not captured.
There is a "cell" in the table with the label "E-Mail" , followed by
a
mail address underneath, and then the table continues.

Everything is captured, including the word "E-mail", but not the e-
mail address itself.

I have the same problem when I do the web query through the Data
menu.

However, if I select the web table manually and paste it in Excel I
get everything properly.
Is there anything I could do in my code to get this solved?
(Excel 2007)
Thank you very much
Herman- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

OK Don,
Here is the code, with the URL.
Note that unlike all other data the E-mail address does not appear in
the Excel table.
Thanks for your time

I use a few methods

1) Look at source code from IE explorer using the menu View - Source. Tags
start with angle bracket and end with angle brackes

<H any text /H

or

<H any text /

The tag name isn't required to end a tag string.


2) Use Dump code below. Some webpages have an id property and some don't .
So the code below will only work on pages that support id. Pafes that don't
support id simply comment out the line with id.

3) Use the followng two functions
Set Table = IE.document.getelementsbytagname("Table")
this function works on any tag and returns multiple tags objects in an array

Set Password = objIE.document.getelementbyid("txtPassword")
The function returned objects in an array that have in the html source
"id=". Not all webpages have id properties

Note: Classnames in code below there is no function that searches for
Classname. You have to go through the All property. Also I used the
LEFT(,1024) function on innertext because some strings are very long and
create memory errors in excel.

Sub WebQuery()

URL = "//www.iec-iab.be/nl/contactgegevens/" & _
"c3fb7c06-29a4-dd11-96ed-005056bd424d"

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


'get web page
IE.Navigate2 URL
Do While IE.readystate < 4 Or _
IE.Busy = True

DoEvents
Loop



RowCount = 2
'find data between first and 2nd H2 tag
For Each itm In IE.document.all
Range("A" & RowCount) = itm.tagname
Range("B" & RowCount) = itm.classname
Range("C" & RowCount) = itm.id
Range("D" & RowCount) = left(itm.innertextname,1024)


Next itm
IE.Quit
End Sub



"Don Guillett" wrote:

Joel, Worked just fine. I need to learn how to do this. Can you explain the
process and how to get the tags.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Joel" wrote in message
...
I can do anything. Try this!

Sub WebQuery()

URL = "//www.iec-iab.be/nl/contactgegevens/" & _
"c3fb7c06-29a4-dd11-96ed-005056bd424d"

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


'get web page
IE.Navigate2 URL
Do While IE.readystate < 4 Or _
IE.Busy = True

DoEvents
Loop



RowCount = 2
'find data between first and 2nd H2 tag
State = "FindB2"
For Each itm In IE.document.all
Select Case State

Case "FindB2"
If itm.tagname = "H2" Then
State = "GetData"
End If
Case "GetData"
If itm.tagname = "H2" Then
'get next section of data
State = "FindB2"
End If

Select Case itm.tagname

Case "DIV"
colCount = 4 'start each row in column D
RowCount = RowCount + 1
Case "SPAN"
Cells(RowCount, colCount) = itm.innertext
colCount = colCount + 1
End Select
End Select
Next itm
IE.Quit
End Sub




"Don Guillett" wrote:

I couldn't do it either.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Herman" wrote in message
...
On 23 aug, 22:11, "Don Guillett" wrote:
If possible, post your url and what you want.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
wrote in message

...





I wrote some code for retrieving web data and it works fine, except
that a little part of the table is not captured.
There is a "cell" in the table with the label "E-Mail" , followed by
a
mail address underneath, and then the table continues.

Everything is captured, including the word "E-mail", but not the e-
mail address itself.

I have the same problem when I do the web query through the Data
menu.

However, if I select the web table manually and paste it in Excel I
get everything properly.
Is there anything I could do in my code to get this solved?
(Excel 2007)
Thank you very much
Herman- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

OK Don,
Here is the code, with the URL.
Note that unlike all other data the E-mail address does not appear in
the Excel table.
Thanks for your time

Don: Here are 4 more examples of macros

1) HTML files have tables and forms. Here is an example of using both. I
first check my dump or HTML source for the wrods table and form. tables and
forms is the easiest methods to use.


Sub GetZipCodes()


ZIPCODE = InputBox("Enter 5 digit zipcode : ")

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

URL = "http://zip4.usps.com/zip4/citytown_zip.jsp"

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

Do While IE.busy = True
DoEvents
Loop
Set Form = IE.document.getElementsByTagname("Form")

Set zip5 = IE.document.getElementById("zip5")
zip5.Value = ZIPCODE


Set ZipCodebutton = Form(0).onsubmit

Form(0).submit
Do While IE.busy = True
DoEvents
Loop

Set Table = IE.document.getElementsByTagname("Table")
Location = Table(0).Rows(2).innertext
IE.Quit
MsgBox ("Zip code = " & ZIPCODE & " City/State = " & Location)


End Sub


2) Here is code that unloads a tble very simply. You can find the table
number by counting the number of teh table in the source code. Table is an
array that starts with an index of zero. I sometimes record a macro in excel
and then before a new WEBquery and use the index number from the webquery to
create my VBA code. the table number in a webquery start at zero and I have
to adjuct the number by 1



Sub WebQuery()

URL = " http://sg.finance.yahoo.com"
'use BN4.SI
StockName = InputBox("Enter Stock Initials : ")
Request = "/q/hp?s="

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


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

Do While IE.Busy = True
DoEvents
Loop

Set Table = IE.document.getelementsbytagname("Table")

RowCount = 1
For Each Row In Table(23).Rows
Colcount = 1
For Each cell In Row.Cells
MyStr = cell.innertext
For i = 1 To Len(MyStr)
Range("A" & RowCount) = Mid(MyStr, i, 1)
Range("B" & RowCount) = Asc(Mid(MyStr, i, 1))
RowCount = RowCount + 1
Next i

Cells(RowCount, Colcount) = cell.innertext
Next cell

RowCount = RowCount + 1
Next Row
End Sub



3) I had a request to execute JAVA code from VBA. The request was to
translate sentences on a spradsheet from one language to a second language
using the Google Java script. I didn't know how to run Java so I asked the
requestor to generate a webpage. I ask for a webpage with an input box, a
translate box, and a submit button. Below is the TEXt file he sent which I
save on my PC as a HTML and the macro I generated. The HTML code is very
simple since it was hand gerated. HTLM from software like Frontpage is much
more complicated. Put the HTML code is a HTML file. the put the macro in an
xls file. change the path name in the macro of the HTML file as required.
This code used ID and a URL as a file on the PC instead of the Internet.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"

<head
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /
<titleGoogle Translate API</title
</head

<body


<form name="f" id="f" action="#" onsubmit="translate(); return false;"

<textarea name="foreign_text" id="foreign_text" rows="4"
cols="60"</textarea
<br /
<br /

<input type="submit" id="submit_button" value="Translate into English"
onfocus="this.blur();" /
<br /
<br /

<textarea name="translation" id="translation" rows="4" cols="60"
onfocus="this.select();" readonly="true"</textarea</form
<br /

<script type="text/javascript" src="http://www.google.com/jsapi"</script
<script type="text/javascript"
google.load("language", "1");
function translate() {var
originaltext=document.forms["f"].foreign_text.value;
google.language.translate(originaltext, "", "en", function(result) {
document.forms["f"].translation.value = (result.error)?("Error:
"+result.error.message):result.translation; }); }
</script

</body
</html

Sub translate()

Dim objIE As Object
Dim strServAcct As String

URL = "c:\temp\working\translation.html"
'Open Internet Explorer
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
IE.Navigate URL

Do While IE.Busy = True Or IE.readystate < 4
DoEvents
Loop

Set ForeignCells = Range("A1:B1")
For Each cell In ForeignCells
Set ForeignText = IE.document.getElementById("foreign_text")
Set submit = IE.document.getElementById("submit_button")

ForeignText.innertext = cell.Value
submit.Select
submit.Click
Do While IE.Busy = True Or IE.readystate < 4
DoEvents
Loop
Set Translation = IE.document.getElementById("Translation")
Translation.innertext = cell.Offset(1, 0).Value
Next cell
End Sub



4) There are two methods to move from one page to another. You can use a
submit button on a webpage or simply go to a new URL address. The code below
goes to a specific webpage by generating a URL. The code is perform a google
search for specific words onfrom a spreadsheet.

Public Sub GoogleSearch1()
'Use and input box for typing in the search words
Dim szSearchWords As String
Dim szResults As String
With Sheets("Sheet1")
szSearchWords = .Range("B2").Value
End With
If Not Len(szSearchWords) 0 Then Exit Sub

'Get keywords and validate by adding + for spaces between
szSearchWords = Replace$(szSearchWords, " ", "+")

Dim ie As Object 'InternetExplorer
Set ie = CreateObject("InternetExplorer.Application")
ie.Navigate "http://www.google.com/search?hl=en&q=" & _
szSearchWords & "&meta="

'Loop until the page is fully loaded
Const READYSTATE_COMPLETE = 4
Do Until ie.ReadyState = READYSTATE_COMPLETE
With ie
.Visible = True
End With
Loop

Set Results = ie.document.getelementsbytagname("P")
For Each itm In Results
If InStr(UCase(itm.innertext), "RESULTS") Then
MsgBox (itm.innertext)
Exit For
End If
Next itm

With Sheets("Sheet2")
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
.Cells.VerticalAlignment = xlTop
End With

Set Results = ie.document.getelementsbytagname("LI")
With Sheets("Sheet3")
RowCount = 1
For Each itm In Results
.Range("A" & RowCount) = itm.innertext
RowCount = RowCount + 1
Next itm
.Cells.VerticalAlignment = xlTop
End With
'Explicitly clear memory
Set ie = Nothing
End Sub

Joel,
Thanks, I will study this when I get a chance.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Joel" wrote in message
...
Don: Here are 4 more examples of macros

1) HTML files have tables and forms. Here is an example of using both.
I
first check my dump or HTML source for the wrods table and form. tables
and
forms is the easiest methods to use.


Sub GetZipCodes()


ZIPCODE = InputBox("Enter 5 digit zipcode : ")

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

URL = "http://zip4.usps.com/zip4/citytown_zip.jsp"

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

Do While IE.busy = True
DoEvents
Loop
Set Form = IE.document.getElementsByTagname("Form")

Set zip5 = IE.document.getElementById("zip5")
zip5.Value = ZIPCODE


Set ZipCodebutton = Form(0).onsubmit

Form(0).submit
Do While IE.busy = True
DoEvents
Loop

Set Table = IE.document.getElementsByTagname("Table")
Location = Table(0).Rows(2).innertext
IE.Quit
MsgBox ("Zip code = " & ZIPCODE & " City/State = " & Location)


End Sub


2) Here is code that unloads a tble very simply. You can find the table
number by counting the number of teh table in the source code. Table is
an
array that starts with an index of zero. I sometimes record a macro in
excel
and then before a new WEBquery and use the index number from the webquery
to
create my VBA code. the table number in a webquery start at zero and I
have
to adjuct the number by 1



Sub WebQuery()

URL = " http://sg.finance.yahoo.com"
'use BN4.SI
StockName = InputBox("Enter Stock Initials : ")
Request = "/q/hp?s="

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


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

Do While IE.Busy = True
DoEvents
Loop

Set Table = IE.document.getelementsbytagname("Table")

RowCount = 1
For Each Row In Table(23).Rows
Colcount = 1
For Each cell In Row.Cells
MyStr = cell.innertext
For i = 1 To Len(MyStr)
Range("A" & RowCount) = Mid(MyStr, i, 1)
Range("B" & RowCount) = Asc(Mid(MyStr, i, 1))
RowCount = RowCount + 1
Next i

Cells(RowCount, Colcount) = cell.innertext
Next cell

RowCount = RowCount + 1
Next Row
End Sub



3) I had a request to execute JAVA code from VBA. The request was to
translate sentences on a spradsheet from one language to a second language
using the Google Java script. I didn't know how to run Java so I asked
the
requestor to generate a webpage. I ask for a webpage with an input box, a
translate box, and a submit button. Below is the TEXt file he sent which
I
save on my PC as a HTML and the macro I generated. The HTML code is very
simple since it was hand gerated. HTLM from software like Frontpage is
much
more complicated. Put the HTML code is a HTML file. the put the macro in
an
xls file. change the path name in the macro of the HTML file as required.
This code used ID and a URL as a file on the PC instead of the Internet.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"

<head
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /
<titleGoogle Translate API</title
</head

<body


<form name="f" id="f" action="#" onsubmit="translate(); return false;"

<textarea name="foreign_text" id="foreign_text" rows="4"
cols="60"</textarea
<br /
<br /

<input type="submit" id="submit_button" value="Translate into English"
onfocus="this.blur();" /
<br /
<br /

<textarea name="translation" id="translation" rows="4" cols="60"
onfocus="this.select();" readonly="true"</textarea</form
<br /

<script type="text/javascript" src="http://www.google.com/jsapi"</script
<script type="text/javascript"
google.load("language", "1");
function translate() {var
originaltext=document.forms["f"].foreign_text.value;
google.language.translate(originaltext, "", "en", function(result) {
document.forms["f"].translation.value = (result.error)?("Error:
"+result.error.message):result.translation; }); }
</script

</body
</html

Sub translate()

Dim objIE As Object
Dim strServAcct As String

URL = "c:\temp\working\translation.html"
'Open Internet Explorer
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
IE.Navigate URL

Do While IE.Busy = True Or IE.readystate < 4
DoEvents
Loop

Set ForeignCells = Range("A1:B1")
For Each cell In ForeignCells
Set ForeignText = IE.document.getElementById("foreign_text")
Set submit = IE.document.getElementById("submit_button")

ForeignText.innertext = cell.Value
submit.Select
submit.Click
Do While IE.Busy = True Or IE.readystate < 4
DoEvents
Loop
Set Translation = IE.document.getElementById("Translation")
Translation.innertext = cell.Offset(1, 0).Value
Next cell
End Sub



4) There are two methods to move from one page to another. You can use a
submit button on a webpage or simply go to a new URL address. The code
below
goes to a specific webpage by generating a URL. The code is perform a
google
search for specific words onfrom a spreadsheet.

Public Sub GoogleSearch1()
'Use and input box for typing in the search words
Dim szSearchWords As String
Dim szResults As String
With Sheets("Sheet1")
szSearchWords = .Range("B2").Value
End With
If Not Len(szSearchWords) 0 Then Exit Sub

'Get keywords and validate by adding + for spaces between
szSearchWords = Replace$(szSearchWords, " ", "+")

Dim ie As Object 'InternetExplorer
Set ie = CreateObject("InternetExplorer.Application")
ie.Navigate "http://www.google.com/search?hl=en&q=" & _
szSearchWords & "&meta="

'Loop until the page is fully loaded
Const READYSTATE_COMPLETE = 4
Do Until ie.ReadyState = READYSTATE_COMPLETE
With ie
.Visible = True
End With
Loop

Set Results = ie.document.getelementsbytagname("P")
For Each itm In Results
If InStr(UCase(itm.innertext), "RESULTS") Then
MsgBox (itm.innertext)
Exit For
End If
Next itm

With Sheets("Sheet2")
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
.Cells.VerticalAlignment = xlTop
End With

Set Results = ie.document.getelementsbytagname("LI")
With Sheets("Sheet3")
RowCount = 1
For Each itm In Results
.Range("A" & RowCount) = itm.innertext
RowCount = RowCount + 1
Next itm
.Cells.VerticalAlignment = xlTop
End With
'Explicitly clear memory
Set ie = Nothing
End Sub

It looks a lot simplier than trying to figure it out yourself. I gave you
very simple examples. Some take me days to get working. Let me know if you
run into any problems.

"Don Guillett" wrote:

Joel,
Thanks, I will study this when I get a chance.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Joel" wrote in message
...
Don: Here are 4 more examples of macros

1) HTML files have tables and forms. Here is an example of using both.
I
first check my dump or HTML source for the wrods table and form. tables
and
forms is the easiest methods to use.


Sub GetZipCodes()


ZIPCODE = InputBox("Enter 5 digit zipcode : ")

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

URL = "http://zip4.usps.com/zip4/citytown_zip.jsp"

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

Do While IE.busy = True
DoEvents
Loop
Set Form = IE.document.getElementsByTagname("Form")

Set zip5 = IE.document.getElementById("zip5")
zip5.Value = ZIPCODE


Set ZipCodebutton = Form(0).onsubmit

Form(0).submit
Do While IE.busy = True
DoEvents
Loop

Set Table = IE.document.getElementsByTagname("Table")
Location = Table(0).Rows(2).innertext
IE.Quit
MsgBox ("Zip code = " & ZIPCODE & " City/State = " & Location)


End Sub


2) Here is code that unloads a tble very simply. You can find the table
number by counting the number of teh table in the source code. Table is
an
array that starts with an index of zero. I sometimes record a macro in
excel
and then before a new WEBquery and use the index number from the webquery
to
create my VBA code. the table number in a webquery start at zero and I
have
to adjuct the number by 1



Sub WebQuery()

URL = " http://sg.finance.yahoo.com"
'use BN4.SI
StockName = InputBox("Enter Stock Initials : ")
Request = "/q/hp?s="

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


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

Do While IE.Busy = True
DoEvents
Loop

Set Table = IE.document.getelementsbytagname("Table")

RowCount = 1
For Each Row In Table(23).Rows
Colcount = 1
For Each cell In Row.Cells
MyStr = cell.innertext
For i = 1 To Len(MyStr)
Range("A" & RowCount) = Mid(MyStr, i, 1)
Range("B" & RowCount) = Asc(Mid(MyStr, i, 1))
RowCount = RowCount + 1
Next i

Cells(RowCount, Colcount) = cell.innertext
Next cell

RowCount = RowCount + 1
Next Row
End Sub



3) I had a request to execute JAVA code from VBA. The request was to
translate sentences on a spradsheet from one language to a second language
using the Google Java script. I didn't know how to run Java so I asked
the
requestor to generate a webpage. I ask for a webpage with an input box, a
translate box, and a submit button. Below is the TEXt file he sent which
I
save on my PC as a HTML and the macro I generated. The HTML code is very
simple since it was hand gerated. HTLM from software like Frontpage is
much
more complicated. Put the HTML code is a HTML file. the put the macro in
an
xls file. change the path name in the macro of the HTML file as required.
This code used ID and a URL as a file on the PC instead of the Internet.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"

<head
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /
<titleGoogle Translate API</title
</head

<body


<form name="f" id="f" action="#" onsubmit="translate(); return false;"

<textarea name="foreign_text" id="foreign_text" rows="4"
cols="60"</textarea
<br /
<br /

<input type="submit" id="submit_button" value="Translate into English"
onfocus="this.blur();" /
<br /
<br /

<textarea name="translation" id="translation" rows="4" cols="60"
onfocus="this.select();" readonly="true"</textarea</form
<br /

<script type="text/javascript" src="http://www.google.com/jsapi"</script
<script type="text/javascript"
google.load("language", "1");
function translate() {var
originaltext=document.forms["f"].foreign_text.value;
google.language.translate(originaltext, "", "en", function(result) {
document.forms["f"].translation.value = (result.error)?("Error:
"+result.error.message):result.translation; }); }
</script

</body
</html

Sub translate()

Dim objIE As Object
Dim strServAcct As String

URL = "c:\temp\working\translation.html"
'Open Internet Explorer
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
IE.Navigate URL

Do While IE.Busy = True Or IE.readystate < 4
DoEvents
Loop

Set ForeignCells = Range("A1:B1")
For Each cell In ForeignCells
Set ForeignText = IE.document.getElementById("foreign_text")
Set submit = IE.document.getElementById("submit_button")

ForeignText.innertext = cell.Value
submit.Select
submit.Click
Do While IE.Busy = True Or IE.readystate < 4
DoEvents
Loop
Set Translation = IE.document.getElementById("Translation")
Translation.innertext = cell.Offset(1, 0).Value
Next cell
End Sub



4) There are two methods to move from one page to another. You can use a
submit button on a webpage or simply go to a new URL address. The code
below
goes to a specific webpage by generating a URL. The code is perform a
google
search for specific words onfrom a spreadsheet.

Public Sub GoogleSearch1()
'Use and input box for typing in the search words
Dim szSearchWords As String
Dim szResults As String
With Sheets("Sheet1")
szSearchWords = .Range("B2").Value
End With
If Not Len(szSearchWords) 0 Then Exit Sub

'Get keywords and validate by adding + for spaces between
szSearchWords = Replace$(szSearchWords, " ", "+")

Dim ie As Object 'InternetExplorer
Set ie = CreateObject("InternetExplorer.Application")
ie.Navigate "http://www.google.com/search?hl=en&q=" & _
szSearchWords & "&meta="

'Loop until the page is fully loaded
Const READYSTATE_COMPLETE = 4
Do Until ie.ReadyState = READYSTATE_COMPLETE
With ie
.Visible = True
End With
Loop

Set Results = ie.document.getelementsbytagname("P")
For Each itm In Results
If InStr(UCase(itm.innertext), "RESULTS") Then
MsgBox (itm.innertext)
Exit For
End If
Next itm

With Sheets("Sheet2")
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
.Cells.VerticalAlignment = xlTop
End With

Set Results = ie.document.getelementsbytagname("LI")
With Sheets("Sheet3")
RowCount = 1
For Each itm In Results
.Range("A" & RowCount) = itm.innertext
RowCount = RowCount + 1
Next itm
.Cells.VerticalAlignment = xlTop
End With
'Explicitly clear memory
Set ie = Nothing
End Sub

Joel

What, if any version, of IE are you running?

If IE8 then there's an option under ToolsDeveloper Tools (F12).

It brings up quite a handy structured HTML resemplation of the web
page in a treeview which can be very useful.

It has search facilities but one of the best bits I like about it is
that when you select a particular section of the HTML in the F12.

Then when you go back to the original page it's highlighted with a
thin, blue border.

Once other thing I find helpful for this sort of thing is <a ref
=http://msdn.microsoft.com/en-us/library/ms533050(VS.85).aspxHTML and
DHTML Reference</a