webquery-cookie problems



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

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.

to do it again and force a refresh

Robin Hammond


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 = _

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

gShell.SendKeys ("{ENTER}")

Set ie = Nothing
Set gShell = Nothing
'closes window

End Sub

Part 2: getting the data

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

Destination _
.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
End With
'MsgBox ActiveSheet.QueryTables(1).Parameters.Application

End Sub

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question