calendar control - pivot table input variable

[Mike]

Hi I created active x calendar control. I started
record macro caught the pivot table build code, with a
where on a date field. calling odbc from sql2000 server
I am trying to use the pick date from the calendar
control as the start date in the where clause
so the control would be >= start_date and <= (startdate +
7)
If that can work then put in an imput field for the start
date plus input field.

Last is there anything around about embeding excel into
asp.net?

thanks

 

[Patrick Molloy]

Frankly, I don't undersatnd what you wrote.

Think about what it is that you need and try to ask a
question about it again.

If your question is about extracting data from a SQL
database to a Pivot table, the do NOT use ODBC.
Use ADO. Create a connection object and connet it to the
database.
opena recordset with your sql script to get the data from
the database. set the pivot caches RECORDSET property to
this recordset.


Here is some sample code that connects to a database then
creates a pivot table

dim ws as worksheet
Dim oConn As ADODB.Connection
Dim orst As ADODB.Recordset

set ws = worksheets.add

Set rTarget = ws.Range("C3")

sql = sMYSQL


' create objects
Set oConn = New ADODB.Connection
Set orst = New ADODB.Recordset

' connect to the database
With oConn
.ConnectionString = "PROVIDER=MSDASQL;" & _
"driver={SQL Server};" & _
"server=%server%;uid=;pwd=;" & _
"database=%database%;"
.CommandTimeout = 60
.Open
End With


' fetch the data
orst.CursorLocation = adUseClient
orst.Open sql, oConn, adOpenDynamic, adLockOptimistic

' create the pivot cache
Set ptc = ActiveWorkbook.PivotCaches.Add(xlExternal)
' populate the cache the create the pivot table
Set ptc.Recordset = rst
Set PT = ptc.CreatePivotTable(rTarget, "MyPivot")









Patrick Molloy
Microsoft Excel MVP