How do I use the ActiveCell's contents as a string for a web query
 

I am trying to set up a macro which will use a web query to retrieve
files corresponding to the data in row 1 of my spreadsheet (ie. a list
of values for each name in row1).

So, essentially I want to have a series of names in row 1 (ie.
A1=Smith, B1=Jones, C1=Dudley etc), and then have the macro so that
each of them returns a web query from the address
http://www.class.com/[value].txt (so that it returns a query from
http://www.class.com/Smith.txt etc.)

I have set up the macro so that it asks me which name to query (ie. I
have worked out how to do a dynamic web query), and so that the
ActiveCell is the correct cell, but I still have to click the cell each
time.

Can somebody please help me.


--
thebird
------------------------------------------------------------------------
thebird's Profile: http://www.excelforum.com/member.php...o&userid=24515
View this thread: http://www.excelforum.com/showthread...hreadid=381113

How do I use the ActiveCell's contents as a string for a web query
 

Dim Qt As QueryTable
Dim Sht As Worksheet
Dim strWebAddr As String
Dim i As Integer

Set Sht = ActiveSheet
Set Qt = Sht.QueryTables(1)

For i = 0 To 255
If ActiveCell.Offset(0, i).Value < "" Then
strWebAddr = ActiveCell.Offset(0, i).Value
With Qt
.Connection = "URL;" & strWebAddr
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "22"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.Refresh BackgroundQuery:=False
End With
Else
Exit For
End If
Next i
End Sub


"thebird" wrote in
message ...

I am trying to set up a macro which will use a web query to retrieve
files corresponding to the data in row 1 of my spreadsheet (ie. a list
of values for each name in row1).

So, essentially I want to have a series of names in row 1 (ie.
A1=Smith, B1=Jones, C1=Dudley etc), and then have the macro so that
each of them returns a web query from the address
http://www.class.com/[value].txt (so that it returns a query from
http://www.class.com/Smith.txt etc.)

I have set up the macro so that it asks me which name to query (ie. I
have worked out how to do a dynamic web query), and so that the
ActiveCell is the correct cell, but I still have to click the cell each
time.

Can somebody please help me.


--
thebird
------------------------------------------------------------------------
thebird's Profile:
http://www.excelforum.com/member.php...o&userid=24515
View this thread: http://www.excelforum.com/showthread...hreadid=381113