>
> Alternatively you could try interposing MS Access, i.e.
> define your queries in an Access database, and use those
> queries as the datasource for the Excel report.
I would try using a web query instead of access. Make a simple (password
protected!) cgi that executes arbitrary sql and returns a html table.
Then use a web query from excel. From recording a macro, I see that you can
set the URL to be anything you want (from vb)
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.otago.ac.nz/phonebook/surnamef.html",
Destination:=Range( _
"A10"))
.Name = "surnamef"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingRTF
.WebTables = "6"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
You should thus be able to pop any parameters or sql you want in the query
string.
The advantage of this method is that excel handles getting the data into the
worksheet.
And, you don't need to install Postgres odbc drivers on your deployment
machines.
I did do this with 2003. I'm not sure if you can do this with earlier Excel
versions.
-Tim
