Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Excel 2003 - VBA - use ADO recordset & create pivot table

Hi....I dont know if I am the right path....


	VB:
	
 
 'Initiate PivotCache object to accept external data
Set objPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal) 
 
 
 'Assign the recordset to PivotCache object
Set objPivotCache.Recordset = rs 
 
 'Create pivot table
With objPivotCache 
    .CreatePivotTable TableDestination:=ws.Range("Z10") 
     'Use this line of code instead if you want to specify a tablename for the pivot table
     '.CreatePivotTable TableDestination:=ws.Range("Z10"), TableName:="ABCDEFG"
End With 
 
 'Place the pivot field items in table
 'Place the field item "Week" into the column section
 
[COLOR=red]With objPivotCache.PivotTables 


Post your answer or comment

comments powered by Disqus
I create reports in excel using large amounts of data extracted from a db on
a regular basis. I have created macros to convert this data and then create
pivot tables, again using a macro. I normally have to edit the macro each
time to select the correct amount of data - eg - R1C1:R7131C31 and the next
time there will be more/less rows - columns remain the same regardless. When
creating the macro, can i specify to automatically find the range which
contains only data - i.e. find the last row of data?

Hi,
I create Pivot Table in VBA from Access database (about 200 000 records). VBA load i.e. 1,7 million combination records and near 600 000 combination the program "fall down".
I use Excel 2000 and MS Access 2003.
Program code:
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.OptimizeCache = True
.Connection = Array("ODBC;DSN=Database MS Access;", _
"DBQ=C:TempData.mdb;", _
"DefaultDir=C:Temp;", _
"Driver={Microsoft Access Driver (*.mdb)};", _
"DriverId=25;FIL=MS Access;", _
"MaxBufferSize=8192;", _
"MaxScanRows=8;", _
"PageTimeout=5;", _
"SafeTransactions=0;", _
"Threads=3;", _
"UserCommitSync=YES;", _
"UID=admin;PWD=password)
.CommandType = xlCmdSql
.CommandText = Array("SELECT Table.Jedna,", _
"Table.Dva ", _
"FROM 'C:TempData.mdb'.Table Table")
.CreatePivotTable TableDestination:="'[Test.xls]List1'!R1C1", _
TableName:="Pivot Table"
End With
Thank you for your help.

I'm using ADO to create a recordset which I'm trying to use to create a
pivot table. The code is below. I get error "One or more accessor flags
were invalid" on the CreatePivotTable line. I know the recordset has data.

TIA,

Wolfie

Dim rsData As ADODB.Recordset
Dim szConnect As String
Dim szSQL As String
Dim objPivotCache As PivotCache
Dim objPivotTable As PivotTable

' Create connection string
szConnect = "Provider=SQLOLEDB; Data Source=MyServer;;User ID=myuser; " & _
"password=mypassword"

' Create the SQL statement
szSQL = Worksheets("SQL_ActPlan").Range("A100").Value

' Create the Recordset object and run the query.
Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Make sure we got records back
If Not rsData.EOF Then
' Use the record set for the pivot table
Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal)
Set objPivotCache.Recordset = rsData2
With objPivotCache
.CreatePivotTable TableDestination:="R7C1", _
TableName:="PivotTable1", ReadData:=True
End With
rsData2.Close
End If

I'm using ADO to create a recordset which I'm trying to use to create a
pivot table. The code is below. I get error "One or more accessor flags
were invalid" on the CreatePivotTable line. I know the recordset has data.

TIA,

Wolfie

Dim rsData As ADODB.Recordset
Dim szConnect As String
Dim szSQL As String
Dim objPivotCache As PivotCache
Dim objPivotTable As PivotTable

' Create connection string
szConnect = "Provider=SQLOLEDB; Data Source=MyServer;;User ID=myuser; " & _
"password=mypassword"

' Create the SQL statement
szSQL = Worksheets("SQL_ActPlan").Range("A100").Value

' Create the Recordset object and run the query.
Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Make sure we got records back
If Not rsData.EOF Then
' Use the record set for the pivot table
Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal)
Set objPivotCache.Recordset = rsData2
With objPivotCache
.CreatePivotTable TableDestination:="R7C1", _
TableName:="PivotTable1", ReadData:=True
End With
rsData2.Close
End If

I'm creating pivots in VBA like this:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= "Sheet1!R1C1:R2687C49").CreatePivotTable TableDestination:="", TableName:="Table1", DefaultVersion:=xlPivotTableVersion10

Where is says, "Sheet1!R1C1:R2687C49", how could I replace that with something like "used cells" to select all the data on the worksheet? The number of rows varies and I don't want to miss some rows because the data grew unexpectedly beyond "2687" rows.

Thanks!

Hi:
I am seeing a discrepancy in Excel 2003 VBA
In the built-in Help , it lists the function Sqr as the VBA fucntion to
take the square root of a real argument. Notice that the S in Sqr is in
Upper Case. Similarly for other code examples within Excel and in David M.
Bourg's book, "Excel Scientific and Engineering Cookbook". But, when I am in
VBA inside of Excel it does not allow me to type (and keep) Sqr...it converts
the S to lower case s.
This works as a square root function, but is not the same syntax as Sqr.
Whiuch is correct ? Why does VBA's actual sytax (that used) not match what
is in the books, or the built-in Help ?

Another example that works correctly is the use of Tan() (with the upper
case T) for the tangent function.

Thank you

-DaleB

Experts,

I've created a Excel Add-In using Excel 2007 and created a OLEDB connection with this Add-In to create pivot table with that conenction. I've added that OLEDB connection to Add-In (not for activeworkbook) for security resons (If I add the connection to the activeworkbook with help of Add-In my username& password will be displayed in active workbook connection properties).

I'm trying to create pivotCache to create pivot table with Add-In's OLEDB connection, it is raising error(1004: Application-defined or object-defined error).

The following code used to created pivot table cache
ThisWorkbook.Connections.Add "OlapConnection", "", Array("OLEDB;Provider=MSOLAP.3; Persist Security Info=True;Data Source=datasource;Initial Catalog=dbname;roles=Role2005;TimeOut=5000;User ID=UserID;Password=password;"), Array("SampleCube"), 1
Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:=ThisWorkbook.Connections(OlapConnection), Version:=xlPivotTableVersion12)

Can anyone help me to resolve this issue?

Thanks in advance!
Venkat

Hello and thanks for reading this thread as I'm stump!

Below is a very basic Excel 2003 VBA routine that has been in use for about 4 years. The code has worked as intended countless time with the exception of 3 isolated events that can NOT be repeated. I would greatly appreciate any clues, ideas, thoughts, prayers or the like to help me get to the root cause of these 3 isolated events. Many thanks in advance

Background:
This routine is part of a workbook that is used to prepare a list of survey questions from a set of user responses. After the user checks their applicable areas in a worksheet they click on “prepare survey” button that runs the code below. Description of each section of code:

Lines 1 to 16: notes, disable esc capability, define variables

Lines 17 to 23: check to see if survey is already prepared

Lines 24 to 26: copy value from cell Y11 in worksheet wsq to R11 in same worksheet. NOTE: This is the section of code that did not run on 3 isolated events from 3 different users from 3 different parts of a country. The value in Y11 is either TRUE or FALSE based on if the user checks a box in the worksheet. Checked box goes TRUE and uncheck goes FALSE. The code in these lines is important in that this value needs to be copied over to R11 so when the survey questions are prepared the ones corresponding to TRUE or FALSE are prepared. In all the isolated events the code ran perfect except it did NOT copy the value from Y11 to R11. When I looked at the 3 isolated events the values in Y11 corresponded to the user check selection but the value in R11 was NOT the same as IF the code just skipped over lines 24 to 26. When I open and use the 3 independent and exact files that the error occurs and clear the users input, enter their exact responses, and run the code (ie., “prepare survey” routine) the code works perfect. The error cannot be reproduced with the EXACT same user input in the EXACT same file they entered the data for all 3 files that generated the error

Lines 26 and on state flags to status of survey, prompt user to make sure he wants to prepare questions, runs a couple other routines that move boxes around and preps over worksheets, locks worksheets and workbook, etc…

Thanks again for you time and any help,

Mike

The code:
1	Sub prepare_survey_supplier()
2	''''''''''''''''''''''''''''''''''''''''''''''
3	'
4	' CODE CONDITIONS
5	' - Clear Macro named "clear_supplier"
6	'
7	'
8	''''''''''''''''''''''''''''''''''''''''''''''
9	    
10	    ' disable esc key
11	    Application.EnableCancelKey = xlDisabled
12	    
13	    ' define variables
14	    wsq = "Supplier Questions"      'questions worksheet
15	    wsd = "Supplier Data"           'data worksheet
16	    
17	    'survey prepared?
18	    If Sheets(wsq).Range("R8:R8") = "yes" Then
19	        MsgBox "Survey already prepared.  If you would like to reprepare the survey, press the 'Clear All Data'
button."
20	        End
21	    Else
22	        'continue
23	    End If
24	    
25	    'transfer W211 check condition in to vlookup range
26	    Worksheets(wsq).Range("R11:R11") = Worksheets(wsq).Range("Y11:Y11").Value
27	    
28	    
29	    promptflag = Worksheets(wsq).Range("R4:R4")
30	    If promptflag <> "no" Then
31	    
32	        ' Data entered used to define survey correctly?
33	        '
34	        Dim Msg, Style, Title, Help, Ctxt, Response, MyString
35	        Msg = "Make sure that your response to Question 6 is correct.  Your response can not be changed after the
survey is prepared without clearing all your data entered.  If any response is incorrect, please select 'No' and correct
it."
36	        Style = vbYesNo + vbExclamation ' Define buttons.
37	        Title = "All responses correct?"  ' Define title.
38	        Ctxt = 1000 ' Define topic
39	        Response = MsgBox(Msg, Style, Title, Help, Ctxt)
40	        If Response = vbYes Then    ' User chose Yes.
41	            'no action
42	        Else    ' User chose No.
43	            End
44	        End If
45	    Else
46	    End If
47	    
48	
49	    
50	    
51	    'prepare hide boxes and set flags that worksheet is prepared
52	        Application.Run "hide_box_rear_supplier"
53	        Application.Run "clear_riskreport"
54	        ActiveSheet.Unprotect Password:="*****************"
55	        Worksheets(wsq).Range("R9:R9") = "TRUE"        'change common question flag to TRUE
56	        Worksheets(wsq).Range("R8:R8") = "yes"         'change survey prepared flag to yes
57	        
58	        Range("R9:R24").Locked = True
59	        Range("S11:X11").Locked = True
60	        
61	        ActiveSheet.Protect Password:="*****************"
62	    
63	    
64	    ' Sort Supplier Data based on user response
65	    
66	    ActiveWorkbook.Unprotect Password:="*****************"
67	
68	        Application.ScreenUpdating = False
69	    
70	    Sheets(wsd).Visible = True
71	    
72	    Worksheets(wsd).Select
73	    Range("C5:BZ154").Sort Key1:=Range("D5"), Order1:=xlDescending, Key2:=Range("E5") _
74	        , Order2:=xlAscending, Key3:=Range("BZ5"), Order3:=xlAscending, Header:= _
75	        xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
76	 
77	    Worksheets(wsq).Select
78	    
79	    Sheets(wsd).Visible = False
80	    
81	            Application.ScreenUpdating = True
82	        
83	    ActiveWorkbook.Protect Password:="*****************"
84	    
85	    Application.Run "set_print_display_size_supplier"
86	    
87	    If promptflag <> "no" Then
88	        MsgBox "Survey prepared.  Please complete the survey below."
89	    Else
90	    Worksheets(wsq).Range("R4:R4") = "yes"
91	    End If
92	    
93	    Worksheets(wsq).Range("S12:S13") = Worksheets(wsq).Range("R12:R13").Value
94	    Worksheets(wsq).Range("T12:T12") = True
95	        
96	    Worksheets(wsq).Range("C13").Select
97	    
98	    
99	End Sub
NOTE cross post mistake:

Being new to this I cross-posted at http://www.mrexcel.com/forum/showthread.php?t=498958 I would have deleted the post at mrexcel so there would not be a cross-post but I could not find the option to delete my own threads maybe it was disabled.

Sorry for being a newbie.

Previous to Excel 2003 I could use a pivot table result in a formula and copy
that formula as a relative reference. In Excel 2003 a GETPIVOTDATA fromula
is created and uses an absolute reference. Is there a way in Excel 2003 to
either make it arelative reference (without editing the formula) or revert to
the method used in all prior releases?

I would like to know if it is possible to create a defenite number of pivot table using looping strctures. Kindly let me know.

Thanks,
booo

Object Model [Excel 2003 VBA Language Reference]

Hi all,

I would like to create a pivot table using data from a txt file. I don't want to import the data into excel first, then make a pivot due to the size of the data.

I've done something similar before by using an excel file on a server to create the pivot table but I'm unable to figure out how to do this with a txt file. In the past I've used create pivot table and then chose external data source.

Is this possible, with or without VB? TIA!

Hi all,

i have different xls files of different data but with the same fields which i want to display in pivot table instead of creating pivot table for each file can i do it using macros.

if so kindly help me regarding this if possible with example code.

Thanks in advance,

regards,
venu.

I have a table of quarterly shipments by vendor/product and want to convert
this to a pivot table. Right now I have 12 quaters of data, with includes 8
quaters of actual data and 4 of estimates. If I get how pivot tables work, I
would need to add 12 rows for each vendor-product combination and have one
column that includes "data." Is there an automated way to convert an excel
table, where data is in multiple columns, into a pivot table? And then update
each quarter.

Please tell me if I am going about this the wrong way. I have only used,
never set up, pivot tables,

Ellen

I have a table of quarterly shipments by vendor/product and want to convert
this to a pivot table. Right now I have 12 quaters of data, with includes 8
quaters of actual data and 4 of estimates. If I get how pivot tables work, I
would need to add 12 rows for each vendor-product combination and have one
column that includes "data." Is there an automated way to convert an excel
table, where data is in multiple columns, into a pivot table? And then update
each quarter.

Please tell me if I am going about this the wrong way. I have only used,
never set up, pivot tables,

Ellen

I have created a pivot table in excel 2007 and used one of the Pivot Table Styles for formatting. I would now like to copy this worksheet to a new spreadsheet but just bring over the values and the formatting, not the "Pivot Table".

How do I bring over the formatting? I've tried paste special and format painter but neither will bring over the formatting within the pivot table itself. It will only bring over formatting that is outside the pivot itself.

Thank you for any help you can give me.

Justine

I have a pivot table which takes data from another tab and I am using one of the value fields(premium) as a report filter so I can exclude any zero amounts. The problem I have is that the amount of data each month increases and when I refresh the pivot it does not automatically include the new values, they remain unticked.

I am using Excel 2007, I have an old version of this which I brought over from Excel 2003 into 2007 with old style pivot tables and these ones work fine. Its just if I create a brand new pivot in 2007(blue header and footer) it does not want to tick all new updated values!

Please could anyone help

Tnahks

Dear all,

I wrote the code in creating pivot table for my source of data.
However, when run the code, it prompts with the message. (I use Excel 2000)

"Run-time error '438':
Object doesnt support this property or method"

Below please find the code also.

****************************
Sub CreatePivotTable()

Dim PTCache As PivotCache
Dim PT As PivotTable

Application.ScreenUpdating = False

'Add a new sheet for the pivot table
Worksheets.Add

'Create the cache
Set PTCache =
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase,
SourceData:=Range("A1").CurrentRegion.Address)

'Create the pivot table
Set PT = ActiveSheet.PivotTables.Add(PivotCache:=PTCache,
TableDestination:=Range("A3"))

'Specify the fields
With PT
.PivotFields("street").Orientation = xlColumnField
.PivotFields("condition_code").Orientation = xlColumnField
.PivotFields("customer_code").Orientation = xlRowField
.PivotFields("liner_code").Orientation = xlRowField
.PivotFields("cont_type_code").Orientation = xlRowField
.DisplayFieldCaptions = False
End With

Application.ScreenUpdating = True

End Sub
****************************

Thanks in advance!!

Darwin Chan

Hi ,

I have recorded a Macro to create Pivot Tables, I would like this to run in any workbook.

The problem is that every time a Pivot Table is generated Excel alters the number of the table(Pivot Table options). This then does not match that in the Macro as below("PivotTable1").

How would I rectify this problem.

All ideas are appreciated.

Many thanks

Ade


	VB:
	
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ 
"Sheet1!R1C1:R37C6").CreatePivotTable TableDestination:="", TableName:= _ 
"PivotTable1", DefaultVersion:=xlPivotTableVersion10 
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) 
ActiveSheet.Cells(3, 1).Select 
With ActiveSheet.PivotTables("PivotTable1").PivotFields("GAME") 
    .Orientation = xlRowField 
    .Position = 1 
End With 
With ActiveSheet.PivotTables("PivotTable1").PivotFields("LANGUAGE") 
    .Orientation = xlRowField 
    .Position = 2 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hello experts,

For my 1st post I will try to follow the right rules with tags etc...

I'm not programmer just advanced user of Excel and VBA.

I try to build Pivot table with a macro out from sheets.
I want the macro select the current region for the pivot table as well as when you build a table directly in Excel.

I found a code on this forum which run in debug (f8 steps).
but when I run it directly I get the error '5' false argument or incorrect procedure call (translation from french version).


	VB:
	
Sheets.Add 
ActiveSheet.Name = "Pivot_charge" 
Sheets("DataSource").Select 
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ 
Sheets("DataSource").Cells(1).CurrentRegion.Address).CreatePivotTable TableDestination:= _ 
"'[Analyse SNP.xls]Pivot_charge'!R1C1", TableName:= _ 
"Tableau croisé dynamique1", DefaultVersion:=xlPivotTableVersion10 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I have also tried to create a myrange selection, but it didn' work. I don't know enaugh about coding.

I have also check the dynamic naming explanation, but sorry looks also complicate for me.

So I one of you could help me it will be great.

Denis

I have tried this by recording a macro while i create pivot table in the worksheet named "PIVOT_TABLE" Range = A1. Works fine until I close the workbook and reopen it, then it tries to use the current worksheet it opens to which is "CKA_WANT"

Sub Pivot_table_1()
'
' Macro1 Macro
' Macro recorded 7/25/2008 by RWilliams
'
'
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"CKA_WANT!R1C1:R2279C5").CreatePivotTable TableDestination:=Range("A1"), _
TableName:="PivotTable1"
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Part", _
ColumnFields:="Date"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("QTY")
.Orientation = xlDataField
.Caption = "Sum of QTY"
.Function = xlSum
End With
Application.CommandBars("PivotTable").Visible = False
End Sub

Excel 2003:

The PAGE filter feature in Pivot Tables, I am used to the field that you drop in there appear as a stacked set in each subsequent row in column A. I have picked up an old staff members pivot tables and the Field dropped into the PAGE filter area as running across a single row with a cell space between each.

Has anyone seen this before, and do you know where I can reset this to the way I am used too.

I am currently using Excel 2007, and was able to create Pivot tables without
a problem. However now when I highlight a range of data and click on Insert
pivot table, there are no fields in the pivot table list for me to create the
table.
How can I can i get these fields (effectively the headers of the data
columns) in the pivot table when Excel isn't recognizing them.

I can't figure out how to use median function in Pivot Tables. Is there a way
to do this? I sure hope so because it would save me a lot of time. Thanks for
any help that you can provide.

I use Excel 2002.


No luck finding an answer? You could always try Google.