Recorded Excel macro not working

Web Query , VBA , Excel 2007 Minneapolis, United States
  • 10 years ago

    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.teqs]![as0,20,tvgt40000]![as0,50,tcgtas0,200,tc]![as1,50,tcleas1,200,tc]" _ , Destination:=Range("A1")) .Name = _ "SC.scan?s=TSA[t.teqs]![as0,20,tvgt40000]![as0,50,tcgtas0,200,tc]![as1,50,tcleas1,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.

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Every language has an optimization operator. In C++ that operator is ‘//’”