editing the web query through a sub

I am trying to use <get external data> to get some archival data

the macro which I created and which works is

Sub Macro2()

With Selection.QueryTable
.Connection = _

"URL;http://www.nseindia.com/content/hist...JUL/cm7JUL2005
bhav.csv"
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
End Sub

now in the url I may have to change the date from <7JUL2005> to some other
date for e.g. 8Jul2005

I tried to write the sub something like this

Public Sub newquery()
Dim scripdate As String
scripdate = InputBox("type date as for e.g. 7JUL2005")
With Selection.QueryTable
.Connection = _
"""" &
"URL;http://www.nseindia.com/content/historical/EQUITIES/2005/JUL/cm" & _
scripdate & "bhav.csv" & """"
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With

End Sub

In the input box I type 8JUL2005

But the sub stops at
..Connection = _
"""" &
"URL;http://www.nseindia.com/content/historical/EQUITIES/2005/JUL/cm" & _
scripdate & "bhav.csv" & """"

with error message
appliation defined or object defined error.

where am I doing the mistake

when I goto the <edit query> and change the date 7 to 8 it works.
But I would like to automate through a sub so that the user (who may not be
comfortable with directl;y editing the query can just input the date.

of course when I succeed wtih your help I may have to do the same thing
with the month e.g JUl before "/cm" into some other month e.g. august.
excel 2000/windows 98SE
aplogise for long message.
thanks and regards

You added too much stuff to the connection string...
With Selection.QueryTable
.Connection = _
"URL;http://www.nseindia.com/content/historical/EQUITIES/2005/JUL/cm"
& _
scripdate & "bhav.csv"

Mike F
"R.VENKATARAMAN" <vram26@vsnl$$$.net> wrote in message
news:%[email protected]...
>I am trying to use <get external data> to get some archival data
>
> the macro which I created and which works is
>
> Sub Macro2()
>
> With Selection.QueryTable
> .Connection = _
>
> "URL;http://www.nseindia.com/content/hist...JUL/cm7JUL2005
> bhav.csv"
> .WebSelectionType = xlEntirePage
> .WebFormatting = xlWebFormattingNone
> .WebPreFormattedTextToColumns = True
> .WebConsecutiveDelimitersAsOne = True
> .WebSingleBlockTextImport = False
> .WebDisableDateRecognition = False
> .Refresh BackgroundQuery:=False
> End With
> End Sub
>
> now in the url I may have to change the date from <7JUL2005> to some other
> date for e.g. 8Jul2005
>
> I tried to write the sub something like this
>
> Public Sub newquery()
> Dim scripdate As String
> scripdate = InputBox("type date as for e.g. 7JUL2005")
> With Selection.QueryTable
> .Connection = _
> """" &
> "URL;http://www.nseindia.com/content/historical/EQUITIES/2005/JUL/cm" & _
> scripdate & "bhav.csv" & """"
> .WebSelectionType = xlEntirePage
> .WebFormatting = xlWebFormattingNone
> .WebPreFormattedTextToColumns = True
> .WebConsecutiveDelimitersAsOne = True
> .WebSingleBlockTextImport = False
> .WebDisableDateRecognition = False
> .Refresh BackgroundQuery:=False
> End With
>
> End Sub
>
> In the input box I type 8JUL2005
>
> But the sub stops at
> .Connection = _
> """" &
> "URL;http://www.nseindia.com/content/historical/EQUITIES/2005/JUL/cm" & _
> scripdate & "bhav.csv" & """"
>
> with error message
> appliation defined or object defined error.
>
> where am I doing the mistake
>
> when I goto the <edit query> and change the date 7 to 8 it works.
> But I would like to automate through a sub so that the user (who may not
> be
> comfortable with directl;y editing the query can just input the date.
>
> of course when I succeed wtih your help I may have to do the same thing
> with the month e.g JUl before "/cm" into some other month e.g. august.
> excel 2000/windows 98SE
> aplogise for long message.
> thanks and regards
>
>
>

tons of thanks. I am always confused whenever double quotations occur. You
have claified the point



Mike Fogleman <[email protected]> wrote in message
news:[email protected]...
> You added too much stuff to the connection string...
> With Selection.QueryTable
> .Connection = _
>
"URL;http://www.nseindia.com/content/historical/EQUITIES/2005/JUL/cm"
> & _
> scripdate & "bhav.csv"
>
> Mike F
> "R.VENKATARAMAN" <vram26@vsnl$$$.net> wrote in message
> news:%[email protected]...
> >I am trying to use <get external data> to get some archival data
> >
> > the macro which I created and which works is
> >
> > Sub Macro2()
> >
> > With Selection.QueryTable
> > .Connection = _
> >
> >
"URL;http://www.nseindia.com/content/hist...JUL/cm7JUL2005
> > bhav.csv"
> > .WebSelectionType = xlEntirePage
> > .WebFormatting = xlWebFormattingNone
> > .WebPreFormattedTextToColumns = True
> > .WebConsecutiveDelimitersAsOne = True
> > .WebSingleBlockTextImport = False
> > .WebDisableDateRecognition = False
> > .Refresh BackgroundQuery:=False
> > End With
> > End Sub
> >
> > now in the url I may have to change the date from <7JUL2005> to some
other
> > date for e.g. 8Jul2005
> >
> > I tried to write the sub something like this
> >
> > Public Sub newquery()
> > Dim scripdate As String
> > scripdate = InputBox("type date as for e.g. 7JUL2005")
> > With Selection.QueryTable
> > .Connection = _
> > """" &
> > "URL;http://www.nseindia.com/content/historical/EQUITIES/2005/JUL/cm" &
_
> > scripdate & "bhav.csv" & """"
> > .WebSelectionType = xlEntirePage
> > .WebFormatting = xlWebFormattingNone
> > .WebPreFormattedTextToColumns = True
> > .WebConsecutiveDelimitersAsOne = True
> > .WebSingleBlockTextImport = False
> > .WebDisableDateRecognition = False
> > .Refresh BackgroundQuery:=False
> > End With
> >
> > End Sub
> >
> > In the input box I type 8JUL2005
> >
> > But the sub stops at
> > .Connection = _
> > """" &
> > "URL;http://www.nseindia.com/content/historical/EQUITIES/2005/JUL/cm" &
_
> > scripdate & "bhav.csv" & """"
> >
> > with error message
> > appliation defined or object defined error.
> >
> > where am I doing the mistake
> >
> > when I goto the <edit query> and change the date 7 to 8 it works.
> > But I would like to automate through a sub so that the user (who may not
> > be
> > comfortable with directl;y editing the query can just input the date.
> >
> > of course when I succeed wtih your help I may have to do the same thing
> > with the month e.g JUl before "/cm" into some other month e.g. august.
> > excel 2000/windows 98SE
> > aplogise for long message.
> > thanks and regards
> >
> >
> >
>
>