Help needed - Recorded Web query macro not working.

Question

Hi,

I recorded an excel macro in excel 2007 which is basically a web query. It runs fine while i record it (Data->Import External Data->New Web Query),but doesnt work when i run the recorded macro ( gives Run-time Error '1004': Invalid Web Query). I am guessing there is a problem with the URL since if i replace the URL with any other URL,it is working.

Below is the VBA code which got auto-generated when the macro got recorded.

Sub Stockcharts()

'

' Stockcharts Macro

' Macro recorded 5/16/2010 by subrat

'

'

    With ActiveSheet.QueryTables.Add(Connection:= _

        "URL;http://stockcharts.com/def/servlet/SC.scan?s=TSA\[t.t\_eq\_s\]!\[as0,20,tv\_gt\_40000\]!\[as0,50,tc\_gt\_as0,200,tc\]!\[as1,50,tc\_le\_as1,200,tc\]" _

        , Destination:=Range("A1"))

        .Name = _

        "SC.scan?s=TSA[t.t_eq_s]![as0,20,tv_gt_40000]![as0,50,tc_gt_as0,200,tc]![as1,50,tc_le_as1,200,tc]"

        .FieldNames = True

        .RowNumbers = False

        .FillAdjacentFormulas = False

        .PreserveFormatting = True

        .RefreshOnFileOpen = False

        .BackgroundQuery = True

        .RefreshStyle = xlInsertDeleteCells

        .SavePassword = False

        .SaveData = True

        .AdjustColumnWidth = True

        .RefreshPeriod = 0

        .WebSelectionType = xlSpecifiedTables

        .WebFormatting = xlWebFormattingNone

        .WebTables = "11"

        .WebPreFormattedTextToColumns = True

        .WebConsecutiveDelimitersAsOne = True

        .WebSingleBlockTextImport = False

        .WebDisableDateRecognition = False

        .WebDisableRedirections = False

        .Refresh BackgroundQuery:=False

    End With

    Range("C1:K11").Select

    Selection.ClearContents

End Sub

Any help would be appreciated.

Thanks.

Answer 1

The error goes away if you remove the "unsafe" characters from the URL.

Replace all [ by %5b and all ] by %5d

The result doesn't actually work for me, but I haven't tried it in Excel2007 yet.

---

Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk

Answer 2

Did you try the replacement of [ and ] characters in the URL as I suggested?

---

Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk

Answer 3

Hi Shane,

No, i am not trying to get the current value of all the stocks. The above mentioned URL returns different stocks for each trading day based on certain criteria, so the results are dynamic and keep changing everyday. I need the stock symbols for my use.

Yes, i would use MS stock quote or yahoo finance to get the current value of a stock.

Thanks,

Subrat

Answer 4

Let me ask what you are trying to do - bring back the current value of various stocks?

If that is the case why not use MS Stock Quote addin for Excel?

How to use it and where to down load it are all discussed here:

http://office.microsoft.com/en-us/excel/HA010346091033.aspx

It also tells you the advantage of using it rather than a Web query.

---

If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire

Answer 5

The URL itself works for my in Internet Explorer 8, so it is not invalid.

Clicking on Debug highlights the With statement at the start of the macro. 

As yet I haven't worked out why Excel/VBA is objecting to the syntax of the URL and nor have I found a workaround.

I'll keep trying.

---

Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk