editing the web query through a sub

[R.VENKATARAMAN]

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/historical/EQUITIES/2005/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

 

[Mike Fogleman]

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]

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