On Dec 5, 5:04 pm, sebastienm
wrote:
Actually, you can also set the recordset to the pivot cache as a data source
for the pivot table... which is what you requested in your question.
An example is given in the xl help for Recordset.
--
Regards,
Sébastien
<http://www.ondemandanalysis.com



"sebastienm" wrote:
Hi,
To set the connection of a pivot table, you would set the
PivotCahce.Connection as follow:
.COnnection="ODBC;" & <odbc_connection_string
or .Connection = "OLEDB;" & <oledb_connection_string

eg something like:
activesheet.pivottables(1).pivotcache.connection= _
"ODBC;" & "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=HOSTLOC)(PORT=1526))" & _
"(CONNECT_DATA=(SID=DEV4))); uid=USER; pwd=PASS;"

I am not sure you can change Connection after the pivot has been built. If
not, the above code may not work and you would have to set the Connection at
creation time (mod from macro recorder):

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal)
.Connection = Array( _
Array( "ODBC;" & "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)") _
, Array("(HOST=HOSTLOC)(PORT=1526))" & _
"(CONNECT_DATA=(SID=DEV4))); uid=USER; pwd=PASS;"))

''' ...

Regards,
Sébastien
<http://www.ondemandanalysis.com

"mark" wrote:

Hi.

I've been working with building PivotTables, in vb code, form external
Oracle sources.

I have it working, one way (the xlExternal data source, with the appropriate
.Connection property). But, the way that it is working still requires the
tnsnams.ora file to define what "DEV4" (the name of the database instance)
might mean, etc.

The other day, I was looking on the web and found an article about building
the connect string right into the code, eliminating the need for the
tnsnames.ora file.

The relevant code, minus the database username and password, is he
<<<<<<<<<<<<<<
Dim strCon As String

strCon = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=HOSTLOC)(PORT=1526))" & _
"(CONNECT_DATA=(SID=DEV4))); uid=USER; pwd=PASS;"

Dim oCon As Object
Set oCon = CreateObject("ADODB.Connection")

Dim oRs As Object
Set oRs = CreateObject("ADODB.Recordset")

oCon.Open strCon

Set oRs = oCon.Execute("SELECT * from xxaai_test_lab_forecast")
<<<<<<<<<<<<<<<<<<

That works fine, and I can then go through and put that data into a
spreadsheet, should I happen to want to, which I don't.

But, I would like to know how to put that recordset into a pivot table,
somewhat directly.

Can anyone suggest a way to do that?

Thanks,
Mark- Hide quoted text -

- Show quoted text -

Sebastienm,

I saw your comments about assigning an ADO recordset directly to an
existing pivot table. I am doing just that, but have a problem iwht
the talbes sharing a pivot cache. PLease see my commnents and code,
below. This has been giving me fits! Thank you in advance.

I am using ADO recordsets to refresh existing pivot tables. The user
inputs a start and end date to cells in sheet, then clicks a custom
button to refresh. My code builds the recordset with ADO then assigns
it to the PivotCache.Recordset. I have five pivot tables, each on its
own sheet, but even though I am resetting the recordset each time in
the code, the tables seem to be sharing the same PivotCache -- so when
I refresh one, the others are refreshed, too. This is not what I
want; each table needs to be independent.

How can I correct this? My code is below:


Public Sub UpdatePivotTable(ByRef pvt As PivotTable, ByRef strSql As
String)
' uses ADO recordset to populate pivot


Const sSOURCE As String = "UpdatePivotTable()"


Dim pvtCache As PivotCache
Dim rstData As ADODB.Recordset


On Error GoTo ErrHandler


With Application
.ScreenUpdating = False
.Cursor = xlWait
.Calculation = xlCalculationManual
.StatusBar = "Requerying database, please wait..."
End With


' open global connection object
If gCnn Is Nothing Then Call OpenAccessConnection
gCnn.Open


' populate recordset
Set rstData = New ADODB.Recordset
rstData.Open strSql, gCnn, adOpenStatic, adLockReadOnly


' check for records
If rstData.EOF Then
MsgBox "No matching records.", vbError, "No Data"
GoTo ExitHere
End If


' since there are records
' assign recordset to pivot cache and refresh
Set pvtCache = pvt.PivotCache
Set pvtCache.Recordset = rstData


' refresh pivot
With pvt
.PivotCache.Refresh
.SaveData = False
.EnableFieldDialog = False
.EnableFieldList = False
.EnableWizard = False
End With


ExitHe
'tidy up
On Error Resume Next
Set pvtCache = Nothing
Set pvt = Nothing
rstData.Close
Set rstData = Nothing
gCnn.Close
'reset defaults
With Application
.ScreenUpdating = True
.Cursor = xlDefault
.StatusBar = False
.Calculation = xlCalculationAutomatic
End With


Exit Sub


ErrHandler:
If bCentralErrorHandler(msMODULE, sSOURCE, , True) Then
Stop
Resume
Else
Resume ExitHere
End If
End Sub