I have a workbook that runs a number of queries, on a number of pages
of a single site. More specifically, there are 4 pages, each of th
pages use jsp to return tables of data based on the date specified in
drop box menu on the page.

Each of my worksheets in my workbook connect to one of these webpages
and loops through the dates, grabbing the data for each date i
multiple queries (dynamically changing the posttext to grab a
available table of data).

The problem is when running these queries, the connection i
maintained, and there is some sort of residue that causes conflict
between pages.

A simplified example may be best to illustrate.
The date today is 2004-03-05.
On one page, a table containing tomorrows forcasted data is available
And so the code creates a query to retrieve that data with a posttex
of marketdate=2004-03-06. Now once it has finished with all th
retrievals for that page, a second sheet runs its own retrievals o
another similar webpage. Now this webpage doesn't have data availabl
for tomorrows date, however, when it goes to retrieve the data (for sa
yesterday's date, which is available), somehow it sends the posttex
for tomorrows date, which causes an error since the data does no
exist.

This problem doesnt happen all the time, generally only on refreshes o
a table that exists, and then sends the wrong posttext. I checked th
posttext property of that table, and it seems to be correct, yet i
doesn't seem to send the posttext correctly.

So I was thinking to avoid conflict between pages, by closing th
connection after each page. I understand that theres
MaintainConnection property that I can disable when adding eac
querytable. But, I actually need to maintain the connection for a give
page, because the page looks for a session cookie before accepting pos
data. So how do I close a connection to an external data source?

The only other option I can think of is, for each query se
MaintainConnection to false, and then have it update twice (once t
connect and establish the session cookies, and the second to send th
post data and retrieve the proper data), though I dont even know tha
will work if it disconnects before the second refresh, not to mentio
that it would be widely inefficient to refresh twice for each quer
table.

The urls & code:
http://mospublic.ercot.com/ercot/jsp...d_forecast.jsp
http://mospublic.ercot.com/ercot/jsp...d_forecast.jsp
http://mospublic.ercot.com/ercot/jsp...generation.jsp
http://mospublic.ercot.com/ercot/jsp...rvices_mcp.jsp


Code
-------------------

Dim ConnectURL As String
Dim strDay As String
Dim postStr As String

On Error GoTo Handle_Error

ConnectURL = "URL;" & ActiveSheet.[A2]

For i = -1 To ActiveSheet.[D1].Value
ActiveSheet.[A4].Select

strDay = Application.Text(Date + i, "yyyy-mm-dd")
postStr = "marketdate=" & strDay

If qtExist(strDay) Then
'if query tables exists, refresh
ActiveSheet.QueryTables(strDay).Refresh BackgroundQuery:=False
Else
'otherwise, add a new entry for date
ActiveSheet.QueryTables(1).ResultRange.Rows.Entire Row.Insert shift:=xlDown

With ActiveSheet.QueryTables.Add(Connection:=ConnectURL , Destination:=[B4])
.PostText = postStr
.BackgroundQuery = True
.PreserveFormatting = False
.AdjustColumnWidth = False
.RefreshStyle = xlOverwriteCells
.Name = strDay
.WebSelectionType = xlSpecifiedTables
.WebTables = "4"
.Refresh BackgroundQuery:=False
.SaveData = True
End With

'Refresh twice for initial connection to send post data
If i = -1 Then
ActiveSheet.QueryTables(strDay).Refresh BackgroundQuery:=False
End If

ActiveSheet.QueryTables(strDay).ResultRange.Rows(3 ).End(xlToLeft).Value = strDay
End If
Next i

-------------------


--
Message posted from http://www.ExcelForum.com