Webqueries and variables problem

[dazman]

Hi,

I have a saved macro that queries the FT fund site to get some financial data and it works great. however, as soon as I start to break up the URL string and add my own variables to it, it stops working. Does anyone know if I have to do something special to get this to work:

'Even with this small change it doesn't work where we are specifying the day! -tried sDayGet as a string and a single

'With ActiveSheet.QueryTables.Add(Connection:= _
' "URL;http://mwprices.ft.com/custom/ftfundcenter-com/html-fundsHistorical.asp?symb=F1%3AMGITA&countrycode=F1%3AMGITA&mwHistDate=" & sDayGet & "%2F07%2F2005&x=19&y=12" _
' , Destination:=Range("E1"))
' .Name = _
' "html-fundsHistorical.asp?symb=F1%3AMGITA&countrycode=F1%3AMGITA&mwHistDate=29%2F07%2F2005&x=19&y=12"

'this works

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://mwprices.ft.com/custom/ftfundcenter-com/html-fundsHistorical.asp?symb=F1%3AMGITA&countrycode=F1%3AMGITA&mwHistDate=29%2F07%2F2005&x=19&y=12" _
, Destination:=Range("E1"))
.Name = _
"html-fundsHistorical.asp?symb=F1%3AMGITA&countrycode=F1%3AMGITA&mwHistDate=29%2F07%2F2005&x=19&y=12"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingRTF
.WebTables = "12"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Range("B3").Select

[Don Guillett]

This is a text string so
"ddddd"&yourvariable&"dddd"

--
Don Guillett
SalesAid Software
[email protected]
"dazman" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
>
> I have a saved macro that queries the FT fund site to get some
> financial data and it works great. however, as soon as I start to break
> up the URL string and add my own variables to it, it stops working. Does
> anyone know if I have to do something special to get this to work:
>
> 'Even with this small change it doesn't work where we are specifying
> the day! -tried sDayGet as a string and a single
>
> 'With ActiveSheet.QueryTables.Add(Connection:= _
> '
>
"URL;http://mwprices.ft.com/custom/ftfund...Historical.asp
?symb=F1%3AMGITA&countrycode=F1%3AMGITA&mwHistDate="
> *& sDayGet &* "%2F07%2F2005&x=19&y=12" _
> ' , Destination:=Range("E1"))
> ' .Name = _
> '
>
"html-fundsHistorical.asp?symb=F1%3AMGITA&countrycode=F1%3AMGITA&mwHistDate=
29%2F07%2F2005&x=19&y=12"
>
> 'this works
>
> With ActiveSheet.QueryTables.Add(Connection:= _
>
"URL;http://mwprices.ft.com/custom/ftfund...Historical.asp
?symb=F1%3AMGITA&countrycode=F1%3AMGITA&mwHistDate=29%2F07%2F2005&x=19&y=12"
> _
> , Destination:=Range("E1"))
> Name = _
>
"html-fundsHistorical.asp?symb=F1%3AMGITA&countrycode=F1%3AMGITA&mwHistDate=
29%2F07%2F2005&x=19&y=12"
> FieldNames = True
> RowNumbers = False
> FillAdjacentFormulas = False
> PreserveFormatting = False
> RefreshOnFileOpen = False
> BackgroundQuery = True
> RefreshStyle = xlInsertDeleteCells
> SavePassword = False
> SaveData = True
> AdjustColumnWidth = True
> RefreshPeriod = 0
> WebSelectionType = xlSpecifiedTables
> WebFormatting = xlWebFormattingRTF
> WebTables = "12"
> WebPreFormattedTextToColumns = True
> WebConsecutiveDelimitersAsOne = True
> WebSingleBlockTextImport = False
> WebDisableDateRecognition = False
> WebDisableRedirections = False
> Refresh BackgroundQuery:=False
> End With
> Range("B3").Select
>
>
> --
> dazman
> ------------------------------------------------------------------------
> dazman's Profile:
http://www.excelforum.com/member.php...o&userid=25903
> View this thread: http://www.excelforum.com/showthread...hreadid=392878
>

[dazman]

thanks for the reply, but I think that's what I'm doing. Does anyone know if you can dynamically change these static Excel Webqueries in this way?

[Don Guillett]

Perhaps you should look again?

--
Don Guillett
SalesAid Software
[email protected]
"dazman" <[email protected]> wrote in
message news:[email protected]...
>
> thanks for the reply, but I think that's what I'm doing. Does anyone
> know if you can dynamically change these static Excel Webqueries in
> this way?
>
>
> --
> dazman
> ------------------------------------------------------------------------
> dazman's Profile:
http://www.excelforum.com/member.php...o&userid=25903
> View this thread: http://www.excelforum.com/showthread...hreadid=392878
>