Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
PivotTable from a ADODB.Recordset ?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
PivotTable from a ADODB.Recordset ?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
PivotTable from a ADODB.Recordset ?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
PivotTable from a ADODB.Recordset ?
thanks.
I had tried something similar to your first suggestion; didn't seem to work... maybe I didn't have something quite right. I'll look at it again, and also look up the help on RecordSet as you suggest. Thank you. Mark "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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
PivotTable from a ADODB.Recordset ?
Sebastien,
I see the help that you recommended, and it looks to be exactly what I need. I will try it very soon. Thanks again. Mark "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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
PivotTable from a ADODB.Recordset ?
perfect!
"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. -- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
PivotTable from a ADODB.Recordset ?
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
PivotTable from a ADODB.Recordset ?
Hi,
Do you want or not all pivots to share the same cache? a. If you don't It seems like the Pt Tables were created on the same and single Pt Cache. You have each pivot table with its own independent Pt Cache. You can add more Pt Caches in the book with: with ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal) .connection= .commandtype=... .commandtext=... -- as many as pivot tables --- set each PvtTable to its own cache -- when requesting a refresh for a pvt table, just refresh its associated Pvt Cache. b. If you do. Have you tried the ManualUpdate property of the PivotTable object? Not sure how that works, but maybe, by setting it to True on each pivot you can control the refresh of each Pivot table, independtly of its source (pvt cache). -- Regards, Sébastien <http://www.ondemandanalysis.com "Steve S" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using ADODB.Recordset | Excel Programming | |||
ADODB.RecordSet | Excel Programming | |||
0 with ADODB Recordset | Excel Programming | |||
adodb.recordset with excel | Excel Programming | |||
ADODB Recordset | Excel Programming |