chaniging a date in a url!!
 

Hi Guys,

I have set-up a Macro that goes to a website to get a competitors rat
see below:

Sub getit()

myurl
"http://www.all-hotels.com/xbook/servlet/DisplayRates?show=&partner=8&hotl_id=421725&type=a book&filter=&splurgeUrl=/europe/united_kingdom/england/south_east/bracknell_e1.htm&*txtArrivalDate1=17-Apr-2006*&*txtDepartureDate1=18-Apr-2006*&locationID=450210&hotel_name=&lastLocation=B racknell&cboArrivalMonths=Apr-2006&cboArrivalDays=10&cboNights=03&number_of_adul ts1=2&number_of_children1=0&number_of_beds1=1"

Wit
ActiveWorkbook.Worksheets("Bush").QueryTables.Add( Connection:="URL;"
myurl, Destination:=Worksheets("Bush").Cells(1, 1))

.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True

End With

End Sub

As you can see by the bold type the URL contains a date!
Is ther any way I can amend this if a enter a date into a cell i
excel.

Your Help would be gratefully appreciated.

Regards

Kevin Gun

--
gunny197
-----------------------------------------------------------------------
gunny1979's Profile: http://www.excelforum.com/member.php...fo&userid=3293
View this thread: http://www.excelforum.com/showthread.php?threadid=52756

chaniging a date in a url!!
 

something like
myrul="somehting" & range("a1") & "restof url"

--
Don Guillett
SalesAid Software

"gunny1979" wrote
in message ...

Hi Guys,

I have set-up a Macro that goes to a website to get a competitors rate
see below:

Sub getit()

myurl =
"http://www.all-hotels.com/xbook/servlet/DisplayRates?show=&partner=8&hotl_id=421725&type=a book&filter=&splurgeUrl=/europe/united_kingdom/england/south_east/bracknell_e1.htm&*txtArrivalDate1=17-Apr-2006*&*txtDepartureDate1=18-Apr-2006*&locationID=450210&hotel_name=&lastLocation=B racknell&cboArrivalMonths=Apr-2006&cboArrivalDays=10&cboNights=03&number_of_adul ts1=2&number_of_children1=0&number_of_beds1=1"

With
ActiveWorkbook.Worksheets("Bush").QueryTables.Add( Connection:="URL;" &
myurl, Destination:=Worksheets("Bush").Cells(1, 1))

BackgroundQuery = True
TablesOnlyFromHTML = False
Refresh BackgroundQuery:=False
SaveData = True

End With

End Sub

As you can see by the bold type the URL contains a date!
Is ther any way I can amend this if a enter a date into a cell in
excel.

Your Help would be gratefully appreciated.

Regards

Kevin Gunn


--
gunny1979
------------------------------------------------------------------------
gunny1979's Profile:
http://www.excelforum.com/member.php...o&userid=32932
View this thread: http://www.excelforum.com/showthread...hreadid=527568

chaniging a date in a url!!
 

myurl =
"http://www.all-hotels.com/xbook/servlet/DisplayRates?show=&partner=8&hotl_id=421725&type=a book&filter=&splurgeUrl=/europe/united_kingdom/england/south_east/bracknell_e1.htm&*txtArrivalDate1=XXXX*&*txtDepart ureDate1=YYYY*&locationID=450210&hotel_name=&lastL ocation=Bracknell&cboArrivalMonths=ZZZZ&cboArrival Days=XYZ&cboNights=STU&number_of_adults1=2&number_ of_children1=0&number_of_beds1=1"
MyUrl = Replace(MyURL,"XXXX",format(Range("A1"),"dd-mmm-yyyy"))
MyUrl = Replace(MyURL,"YYYY",format(Range("A2"),"dd-mmm-yyyy"))
MyUrl = Replace(MyURL,"ZZZZ",format(Range("A1"),"mmm-yyyy"))
MyUrl = Replace(MyURL,"XYZ", some formula that figures out arrival days)
MyUrl = Replace(MyURL,"STU", some formula that figures out nights)

And so forth

--
Regards,
Tom Ogilvy


"gunny1979" wrote:


Hi Guys,

I have set-up a Macro that goes to a website to get a competitors rate
see below:

Sub getit()

myurl =
"http://www.all-hotels.com/xbook/servlet/DisplayRates?show=&partner=8&hotl_id=421725&type=a book&filter=&splurgeUrl=/europe/united_kingdom/england/south_east/bracknell_e1.htm&*txtArrivalDate1=17-Apr-2006*&*txtDepartureDate1=18-Apr-2006*&locationID=450210&hotel_name=&lastLocation=B racknell&cboArrivalMonths=Apr-2006&cboArrivalDays=10&cboNights=03&number_of_adul ts1=2&number_of_children1=0&number_of_beds1=1"

With
ActiveWorkbook.Worksheets("Bush").QueryTables.Add( Connection:="URL;" &
myurl, Destination:=Worksheets("Bush").Cells(1, 1))

.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True

End With

End Sub

As you can see by the bold type the URL contains a date!
Is ther any way I can amend this if a enter a date into a cell in
excel.

Your Help would be gratefully appreciated.

Regards

Kevin Gunn


--
gunny1979
------------------------------------------------------------------------
gunny1979's Profile: http://www.excelforum.com/member.php...o&userid=32932
View this thread: http://www.excelforum.com/showthread...hreadid=527568

chaniging a date in a url!!
 

Hi I tried both of these and it does not like it, its there any othe
way I can try?

--
gunny197
-----------------------------------------------------------------------
gunny1979's Profile: http://www.excelforum.com/member.php...fo&userid=3293
View this thread: http://www.excelforum.com/showthread.php?threadid=52756

chaniging a date in a url!!
 

There is no reason why it should not work if you did it properly. Feel free
to send me a copy of your workbook and I will have a look.

--
Don Guillett
SalesAid Software

"gunny1979" wrote
in message ...

Hi I tried both of these and it does not like it, its there any other
way I can try??


--
gunny1979
------------------------------------------------------------------------
gunny1979's Profile:
http://www.excelforum.com/member.php...o&userid=32932
View this thread: http://www.excelforum.com/showthread...hreadid=527568