Web query problems

  • Thread starter Thread starter Melwin
  • Start date Start date
M

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
 
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>>>>>>
 
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
 
Back
Top