webquery-cookie problems

[festdaddy]

I am trying to access data from a website that has many different
stores. A seperate page is used to input the zipcode of the store.
Everytime this page is accessed and a new zipcode is entered, the store
is set as the default and the info is saved in a cookie.

I can automate chaging the store, and the webquery works fine. The
problem is that the webquery, after being run once, doesn't acknowledge
the change in the cookie, i.e. if I set a particular store, download
the data, then change the store and try to download the new stores
data, i end up with the old stores data. its as if the webquery has a
cache or something... is there a way to refresh or clear the cache, or
is there another mechanism to make the webquery check the cookie each
time?

 

[Robin Hammond]

I'm not sure if this is the answer as far as cookies are concerned but
here's a suggestion that comes from database web queries. The web server can
cache the response, so the trick is to add a dummy parameter to your query
in order to force a refresh. For example, this would launch a Yahoo page
showing a stock quote, but I have appended the Dummy bit.

e.g.
http://finance.yahoo.com/q?s=0005.hk&Dummy=00000000
to do it again and force a refresh
http://finance.yahoo.com/q?s=0005.hk&Dummy=000000001

Robin Hammond
www.enhanceddatasystems.com

 

[festdaddy]

Thanks Robin, but unfortunately that doesn't work. Does anybody have
any good references as to how web queries work? I'm finding the
documentation in MSDN and in J-walks books lacking...

I know manypeople don't take these posts seriously without some code,
so here it us. I know its ugly, but the parts work (as long as your
browser is IE 6 with favorites shut off...) Any suggestions on how to
execute the button (that I am currently hitting tab 69 times to
activate) would be appreciated.

Part 1: Changing the store info

Sub storechange()
Zip = "08096"
'01923 = ma, 08096 = nj, 33566 = Fl, 31501 = GA, 02895 = RI. there are
many others
Const URL As String = _

"http://www.lowes.com/lowes/lkn?action=productAccess&reset=true&referurl="
Dim ie As Object
Set ie = CreateObject("internetexplorer.application")
Dim gShell As WshShell
Set gShell = New WshShell
gShell.AppActivate ("Microsoft Internet Explorer")

With ie
.Visible = True
.Navigate URL
Do While .ReadyState <> 4: Loop
'//Send data to IE
With .Document.all
.SHIP_ZIP.Value = Zip
'Do While ie.ReadyState <> 4: Loop
End With
End With

i = 0
Do While i < 69
gShell.SendKeys ("{TAB}")
i = i + 1
Loop


gShell.SendKeys ("{ENTER}")


Set ie = Nothing
Set gShell = Nothing
ie.Quit
'closes window

End Sub


Part 2: getting the data

Sub wqtest()
With ActiveSheet.QueryTables.Add(Connection:= _

"URL;http://www.lowes.com/lkn?action=productList&catalogId=STUDS",
Destination _
:=ActiveCell)
.Name = "lkn?action=productList&catalogId=STUDS"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
'.WebTables = "12"'
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=True
'.MaintainConnection = False
'.RobustConnect = xlAsRequired
'these were things I tried to solve my problem, but they didn't
work
End With
'MsgBox ActiveSheet.QueryTables(1).Parameters.Application

End Sub