Prev: Call a subroutine from ThisWorkBook
Next: how to query my web site from VBA and return a value to VBA
From: Subrat on 17 May 2010 01:25
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
From: Paul Robinson on 17 May 2010 03:57
Hi
The URL is split over 2 lines. Is that just because of my mail reader
or is it that way in the macro? As in some of your other lines you
would need a " _" to continue the line. Clutching at straws a bit!
regards
Paul

On May 17, 6:25 am, Subrat <Sub...(a)discussions.microsoft.com> wrote:
> 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]![as­1,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

 | 
Pages: 1
Prev: Call a subroutine from ThisWorkBook
Next: how to query my web site from VBA and return a value to VBA