Recorded Excel macro not working

• • Reply

  10 years ago

  by subratkabi

  Subrat Kabi

  Minneapolis, United States

  Joined 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.

  Report abuse