Webqueries and variables problem
 

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


--
dazman
------------------------------------------------------------------------
dazman's Profile: http://www.excelforum.com/member.php...o&userid=25903
View this thread: http://www.excelforum.com/showthread...hreadid=392878

Webqueries and variables problem
 

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

--
Don Guillett
SalesAid Software

"dazman" wrote in
message ...

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

Webqueries and variables problem
 

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

--
dazma
-----------------------------------------------------------------------
dazman's Profile: http://www.excelforum.com/member.php...fo&userid=2590
View this thread: http://www.excelforum.com/showthread.php?threadid=39287

Webqueries and variables problem
 

Perhaps you should look again?

--
Don Guillett
SalesAid Software

"dazman" wrote in
message ...

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