Web query problems

[Melwin]

Hi,

Does anyone know any good tutorials for web queries using VBA? I need to
retrieve data from a table located on a website (not whole table). How should
I write the query? What is the basic structure of a web query?

The query will be part of a public function where i use one input that will
be part of the website url from which the data is retrieved. The function
output will be a number in double format.

If someone has some simple VBA code for this, please send it to me.

Thanks

 

[Don Guillett]

You didn't post any of your attempts or url(s). One way to do this is with
an external query. Place your cursor in the table you want to fetch>right
click>import to excel>. data>external query>>>>>>

 

[Melwin]

Hi Don and thank you for your answer.

The table is located on the following web page:

http://www.taloussanomat.fi/sivu.php?page_id=17&sym=AFE1V.HSE&cat=STK&page=basicdata

The figure I need is in the table named "Kaupat". It is the first figure in
the column "Hinta".

Couold you describe how I can access this information with a function and
use it in calculations?

Thanks.

 

[Don Guillett]

To add an external query, I just recorded this using a generic connection.
Now, just substitute your url to establish
your macro. Then modify the second for refresh so you don't overload your
file. Let me know if you need more help.
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 4/29/2008 by Donald B. Guillett
'

'
With ActiveSheet.QueryTables.Add(Connection:="URL;http://www.yahoo.com",
_
Destination:=Range("A6"))
.Name = "www.yahoo"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
'==========
Sub Macro4()
'
' Macro4 Macro
' Macro recorded 4/29/2008 by Donald B. Guillett
'

'
Range("A6").Select
With Selection.QueryTable
.Connection = "URL;http://www.yahoo.com"
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

 

[ryguy7272]

Take a look at your other post.
I gave you an answer over there.



Regards,
Ryan---