Question

Locked

Web Query Excel VBA

By lumpydoo ·
I want to query the following web page with the following source code. I would like to query for specific

information IE I would like to grab only day, price and finish rather than all of the headers. Is that

possible with a web query, if so how do I do it. ( how do I select individual pieces/elements of the the

table rather than the whole table. At the bottom I included my existing code


html
>

<head
title
>Server Page</title
/head
>

<body
>


<table
>

<tr
>

<th
>

Day

</th
>

<th
>

Price

</th
>

<th
>

Finish

</th
>

<th
>

Rate

</th
>

<th
>

Value

</th
>



<th
>

1

</th
>



<th
>

2

</th
>



<th
>

3

</th
>



<th
>

4

</th
>



</tr
>









<tr
>

<td
>U2009</td
td
>3.479</td
td
>8/27/09 6:00 PM</td
td
>0.015</td
td
>0.841964901523561</td
td
>0.046912019463581256</td
td
>0.011590458342725096</td
td
>0.010646947807316898</td
td
>0.022448150410481982</td
>

</tr
>



<tr
>

<td
>V2009</td
td
>3.767</td
td
>9/28/09 6:00 PM</td
td
>0.015</td
td
>0.847743512818856</td
td
>0.1857690456358998</td
td
>0.04750179459721493</td
td
>0.013831151776210637</td
td
>0.027156804811179103</td
>

</tr
>



<tr
>

<td
>X2009</td
td
>4.643</td
td
>10/28/09 6:00 PM</td
td
>0.015</td
td
>0.7030416082384954</td
td
>0.14744789910019174</td
td
>0.03323864025414547</td
td
>0.004967201429382784</td
td
>0.013703106289507327</td
>

</tr
>



<tr
>

<td
>Z2009</td
td
>5.41</td
td
>11/24/09 6:00 PM</td
td
>0.015</td
td
>0.5734019689831045</td
td
>0.1592885903120821</td
td
>0.027236419761382025</td
td
>0.005735016984210417</td
td
>0.022642129840581893</td
>

</tr
>


With ActiveSheet.QueryTables.Add(Connection:= _

"URL;http://value.page.html" _

, Destination:=Range("A1")) 'not real url

On Error GoTo Handler



.Name = "Value.page.html" 'not real name

.FieldNames = True



.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.BackgroundQuery = True

.RefreshStyle = xlInsertDeleteCells

.SavePassword = False

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

'.WebSelectionType = xlSpecifiedTables

'.WebFormatting = xlWebFormattingAll

' .WebTables = "Term"




.WebSelectionType = xlAllTables

.WebFormatting = xlWebFormattingNone

.WebPreFormattedTextToColumns = True

.WebConsecutiveDelimitersAsOne = True

.WebSingleBlockTextImport = False

.WebDisableDateRecognition = False

.WebDisableRedirections = False

.Refresh BackgroundQuery:=False



End With


Exit Sub


Handler:

MsgBox "Check Network Connection"

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Back to Software Forum
2 total posts (Page 1 of 1)  

Related Discussions

Related Forums