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?

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" wrote in message
oups.com...
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?

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