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