web query

  • Thread starter Thread starter imran_subzwari
  • Start date Start date
I

imran_subzwari

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


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