web query
 

Hello,
Does any one knows why the following macro not running;
sub test()

Dim a

a = Selection ' The is the selection in sheet


' where web address is given

Sheets("Sheet2").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = a
With Selection.QueryTable.add(Connection:=a)

WebSelectionType = xlAllTables
WebFormatting = xlWebFormattingNone
WebPreFormattedTextToColumns = True
WebConsecutiveDelimitersAsOne = True
WebSingleBlockTextImport = False
WebDisableDateRecognition = False
Refresh BackgroundQuery:=False
End sub


regardsImran

web query
 

With Selection.QueryTable.add(Connection:=a)

First, you can't add a query to a selected cell, you add
it to the sheet and give it a destination cell. Also the
connection string requires two parts, a type and an
address. If the value in the cell is just an address, you
will need to prefix it with "URL;". Also you are missing
an End With. And you might want to clear the destination
sheet to get rid of the last web query results.

Sub Test()
Dim a
a = Selection

Sheets("Sheet2").Select
Range("A1").Select
Cells.Clear 'get rid of old stuff from prior query

With ActiveSheet.QueryTables.Add(Connection:="URL;" & a,
Destination:=Selection)
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With

End Sub

-----Original Message-----
Hello,
Does any one knows why the following macro not running;
sub test()

Dim a

a = Selection ' The is the selection in
sheet


' where web address is given

Sheets("Sheet2").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = a
With Selection.QueryTable.add(Connection:=a)

.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End sub


regardsImran
.