Visual Basic :: VB On Pivot Table

Create A Pivot Table From Another Table
Hello,

I have a table (tblCompAttend) that has 4 fields;
ID #
Name
Day
TField1
This table can have multiple instances of the same ID # with a different Day and TField1 entry. What I need to do is put that data into another table that has ID #, Name and Day1 through Day 31 as fields. This would create a pivot table of sorts, I guess, where the ID# and the Name are only entered once creating the record or line. Then the entry in tblCompAttend.Day would dictate the Day1 through Day 31 in the new table where the corresponding tblCompAttend.TField1 data would go.

I would end up with one record per ID # with various days in that record having data.

I have tried several scenarios in vba, but only got so far as to have it enter the ID # and Name.

I would appreciate any advice.

Thanks,
~ M ~

Pivot Table Help
I need to allow the user to choose a date to display on a pivot table. The pivot table reads data from an outside source, then filters to the date needed. If I set a date in the code, it works fine, but if I try to assign the date to a variable and filter with that, it gives me errors. can anyone help?

Here is my code (I have truncated it to fit on the page):

Code:
x = "2005-10-07 00:00:00"

"DTData with merge` `DTData with merge`" & Chr(13) & "" & Chr(10) & "WHERE (`DTData with merge`.Day={ts 'x'})" )
This, however, works just fine:

Code:
"DTData with merge` `DTData with merge`" & Chr(13) & "" & Chr(10) & "WHERE (`DTData with merge`.Day={ts '2005-02-16 00:00:00'})")
Any ideas?

Pivot Table In VBA
Can anyone explain how to program pivot tables (in MS Excel) in VBA with data from a spreadsheet.
Does anyone have a code example or a link?

Thanks

Pivot Table
I'm using this to select a page within a pivot table, it works fine. The problem is that aberdeen does not always exist so it uses the previous page selection twice.

I have tried to use the if function but it doesn't work properly. Any tips on where I'm going wrong? Is it even possible?

Code:
Set pvttable = Worksheets("Recruiter Summary").Range("A8").PivotTable
Worksheets("Recruiter Summary").Activate
pvttable.PivotFields("Office").CurrentPage = "Aberdeen"

Code:
Set pvttable = Worksheets("Recruiter Summary").Range("A8").PivotTable
Worksheets("Recruiter Summary").Activate
If ((pvttable.PivotFields("Office").CurrentPage = "Aberdeen") = True) Then
Range("A7") = "blah"
Else
Range("B8") = "blah blah"
End If

Thanks

Pivot Table With ADO
How can I use an ADO connection string with pivot table? When I macro recorded the pivot table using outside source data, it used DAO.

Okay, Last Try On Pivot Table
I created this file with the absolute smallest example I could. All I want to be able to do is click on a button to select everything in a pivot table field (I know you can use the pivot drop down, but I want to use the code somewhere else). No matter what I do I can't get it to work. If anyone would be so kind as to look at the file I would appreciate it.

Thanks

Pivot Table
Hello,

I would like to create a pivot table with a sheet witch the number of rows is not constant.

I give you the code (only the beginning) :

ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"Année!R1C1:R29C28", TableDestination:="[230703.xls]TCD!R1C1", TableName:= _
"Tableau croisé dynamique2"
The source data is Année!R1C1:R29C28. It is locked.

I would like that "R29C28" becomes RXC28 with X the number of rows.

Thanks.
Elve

Ps:I am french so excuse me if my english is not perfect

Pivot A Table In MS SQL
How would I pivot this table using MS SQL? My program needs
to read from the result table.

Origanal:

Prod Val Date
a 200 Jul04
a 100 Aug04
a 300 Sep04
a 400 Oct04
a 500 Nov04

Result:
Jul04 Aug04 Sep04 Oct04 Nov04
a 200 100 300 400 500

Where Prod is in column 1 and the Dates will now be listed in seperate columns.

Thanks

What Are Pivot Table Use For?
What are pivot table use for? Anyone has an example?

Thanks

Pivot Table Vba
I am using following function to create pivote table from sql source and export it to excel. Excel file is creating sucessfull. But data is loading in excel file when I open the created file. But my requirement is it should be loaded automatically and saved with data Before opening the file. Because I need to automate the process Every day data will change. Same file If I open next day then next day data will appear. I hope u understand my requirement.

Data should be loaded in exel file withought opening file. I am attaching the function for pivot table for your verification. I hope I will get the solution as soon as possible.

Sub PivotTest()
Dim strProvider
Dim view
Dim fsets
Dim c
Dim newtotal
Dim PivotTable1
Dim rs

strProvider = "Microsoft.Jet.OLEDB.4.0"
Set cnnConnection = CreateObject("ADODB.Connection")
cnnConnection.Open "dsn=DsnName1;uid=Uid1;pwd=pwd1"
Set PivotTable1 = CreateObject("OWC.PivotTable")
PivotTable1.ConnectionString = cnnConnection.ConnectionString

Dim tmpExcel, pivotTbl

pivotTbl = "d:
amaPivotTest3.xls"

PivotTable1.CommandText = "SELECT top 100 b.DATAFIELD1, c.DATAFIELD2, a.ROWFIELD2, a.ROWFIELD1 " & _
"FROM Table1 a, Table2 b, Table3 c " & _
"WHERE c.FolderName = a.ROWFIELD2 AND b.Folders = a.ROWFIELD2 "

' Get variables from the pivot table
Set view = PivotTable1.ActiveView
Set fsets = PivotTable1.ActiveView.FieldSets
Set c = PivotTable1.Constants

' Add Category to the Row axis and Item to the Column axis
view.RowAxis.InsertFieldSet fsets("[ROWFIELD1]")
view.RowAxis.InsertFieldSet fsets("[ROWFIELD2]")

' Add a new total - Sum of Price
Set newtotal = view.AddTotal("Sum of DATAFIELD1", view.FieldSets("[DATAFIELD1]").Fields(0), c.plFunctionSum)
view.DataAxis.InsertTotal newtotal
view.DataAxis.InsertFieldSet view.FieldSets("[DATAFIELD1]")

Set newtotal = view.AddTotal("Sum of DATAFIELD2", view.FieldSets("[DATAFIELD2]").Fields(0), c.plFunctionSum)
view.DataAxis.InsertTotal newtotal
view.DataAxis.InsertFieldSet view.FieldSets("[DATAFIELD2]")

PivotTable1.Refresh

' Set some visual properties
PivotTable1.DisplayExpandIndicator = True
PivotTable1.DisplayFieldList = True
PivotTable1.Refresh

tmpExcel = "d:
amaPivotTest.xls"
PivotTable1.Export tmpExcel, vbNoOpen

Set cnnConnection = Nothing
MsgBox ("Excel file created successfully")
End Sub

If you have any quiries please let me know.

Rama Dasu Puli,

Pivot Table
Hi,
I have created a simple pivot table with monthly data. The
result is to show one month at a time so the pivot table will
show customer and month. In a summary report I want the ability
to change the month in a cell which will then update the pivot
table by removing the current month and bringing in the month
selected. I can't seem to get any script to work for this, any
advice thanks Dave

Pivot Table
Hello to all,

I want to create a Pivot Table in Excel via VB 6.0
I Create/Open an Excel File, I add a workbook and I export there data from an SQL table.
Then I create a new WorkSheet (in the same workbook) and using data exported I create the Pivot table.

Everything works fine up until I try to add some calculated fields.

''************************************************ *************
xlSheet1.PivotTables("DAILY").PivotFormulas.Add ("PENDING = EARNINGS-CONFIRMED")
''************************************************ *************
PENDING = THE NAME OF THE CALCULATED FIELD
EARNINGS = NAME OF A COLUMN (IN EXPORTED DATA WORKSHEET)
CONFIRMED = NAME OF A COLUMN (IN EXPORTED DATA WORKSHEET USED IN PIVOT TABLE)

Is there something that I am missing? Below you can find the code I am using to create the pivot table.
''************************************************ **************
Private Sub ConstractPivotTables(strRepNm As String, lngRows As Long)
Dim PTSrcData As String, PvtItm As PivotItem, yr As Integer, mnth As Integer, intArrIdx As Integer

FillPivotArrays
Set WExcel = New Excel.Application
Set WBook = WExcel.Workbooks.Open(strRepNm)
Set WSheet = WBook.Worksheets("DAILY")
'CREATING THE PIVOT TABLE
PTSrcData = "DATA!R1C1:R" & CStr(lngRows) & "C21"
WBook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
PTSrcData).CreatePivotTable TableDestination:=WSheet.Range("A4"), _
TableName:="DAILYREP"
WSheet.PivotTables("DAILYREP").SmallGrid = False

'SETTING THE ROW LEVEL FIELDS FROM THE PARAMETERS PASSED
For intArrIdx = 1 To UBound(ArrPvRows)
With WSheet.PivotTables("DAILYREP").PivotFields(ArrPvRow s(intArrIdx))
.Orientation = xlRowField
.Position = intArrIdx
End With
Next intArrIdx

'SETTING THE CALCULATED FIELDS FROM THE PARAMETERS PASSED
WSheet.PivotTables("DAILYREP").PivotFormulas.Add ""PENDING = EARNINGS-CONFIRMED""

'SETTING THE DATA FIELDS FROM THE PARAMETERS PASSED
For intArrIdx = 1 To UBound(ArrPvData)
With WSheet.PivotTables("DAILYREP").PivotFields(ArrPvDat a(intArrIdx))
.Orientation = xlDataField
.Position = intArrIdx
End With
Next intArrIdx
WSheet.Cells.EntireColumn.AutoFit
WSheet.Save
weXCEL.Visible = True
End Sub
''************************************************ **************

Thanks in advance.
Afroditi Pratta

Pivot Table
How to use pivot table.

Pivot Table
Hi everyone again.

Is there any way to program the Excel pivot table for process data in a xla add-in??

any example?? any idea??

thks

Pivot Table
Hi,

I am Using Pivot Tables in my Module to Show data.
I have Shown a general Template.

I have provided additional Fields so that user can drag and drop the required fields in to the pivot table and remove the unneeded ones.

Next time when it's Loaded the default template get's loaded.

I need to store the user defined Pivot view somewhere and Load it again
when requested. how to do this. Is there any way to store the user defined Pivot View and fetch it back.

Help Please
Thx in Advance

Ashok

Pivot Table Automation VB6
Hi

I have a question, I'm building an app in VB6 that uses Excel Automation, I finish the first version of it, but for the next one I need to Add a pivot table to the file that it makes the thing is that I'm getting an error in this part of the code, that it couldn't get the Pivot Table properties, My code is this one

Code:
myxlapp_3.Cells.Select
myxlapp_3.ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!C1:C9").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable5"
myxlapp_3.ActiveSheet.PivotTableWizard TableDestination:=myxlapp_3.ActiveSheet.Cells(3, 1)
myxlapp_3.ActiveSheet.Cells(3, 1).Select
myxlapp_3.ActiveSheet.PivotTables("PivotTable5").SmallGrid = False
With myxlapp_3.ActiveSheet.PivotTables("PivotTable5").PivotFields("Circuito")
.Orientation = xlRowField
.Position = 1
End With
With myxlapp_3.ActiveSheet.PivotTables("PivotTable5").PivotFields("serie")
.Orientation = xlColumnField
.Position = 1
End With
With myxlapp_3.ActiveSheet.PivotTables("PivotTable5").PivotFields("numero")
.Orientation = xlDataField
.Position = 1
End With
With myxlapp_3.ActiveSheet.PivotTables("PivotTable5").PivotFields("Tipo")
.Orientation = xlPageField
.Position = 1
End With
myxlapp_3.CommandBars("PivotTable").Visible = False

and the line when I get the error is this one, the error number is 438, that the object doesn't support the method or property

Code:
myxlapp_3.ActiveSheet.PivotTableWizard TableDestination:=myxlapp_3.ActiveSheet.Cells(3, 1)

The main question here is, Do I need to add an other refrerence to the project, or I'm making an stupid mistake trying to copy/paste the macro from Excel directly into VB6

Thank you in advance

Pivot Table Problem Using VB6
This is my first effort to automate pivottables using VB6.
I created the following VBA code using the Macro recorder.

Sub PivotExample()
'
' PivotExample Macro
' Macro recorded 3/30/2004 by G135972
'

'
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'FLM items '!R1C1:R122C35").CreatePivotTable TableDestination:="", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Hit ASI 16?")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Face Amt"), "Sum of Face Amt", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Hit ASI 16?"), "Count of Hit ASI 16?", xlCount
End Sub

It works fine.
But When I paste the above code in my VB6 application to perform the same task on the same WK book, It only gives me the grand total of the above pivot table. I want to see the individual sum and count of each item in datafield "Hit ASI 16?". There are only two values for this field (or column ) and that is either "Y" or "N".
Someone please help me! I am pulling my hairs here . Thanks! for your time>

ACCESS->Pivot-Table
Hello my name is Franka,

I have an ACCESS-DB. I will create a Pivot-Table based on a query. I choose in a form my fields, which are Data-Fields or Row-Fields... But when I start the procedure, ACCESS do the right thing an creates the Pivot-Table, but sometimes Access has errors or crash. Errors are: Paste is not working, or Remote-Server not found and so on...

Here is my Code:

Code:
Dim xlAnw As Object
Private Sub btnEdit_Click()
On Error GoTo Err_Befehl50_Click
Const xlDataField = 4
Const xlDataOnly = 4
Const xlDatabase = 1

DoCmd.Echo False
DoCmd.SetWarnings False
DoCmd.OpenQuery "AB_Gesamt"

DoCmd.RunCommand acCmdSelectAllRecords

DoCmd.RunCommand acCmdCopy

Set xlAnw = CreateObject("Excel.Application")

xlAnw.Workbooks.Add

xlAnw.Visible = True

xlAnw.ActiveSheet.Paste

DoCmd.RunCommand acCmdClose
DoCmd.Echo True

xlAnw.ActiveSheet.PivotTableWizard SourceType:=xlDatabase, _
SourceData:=ActiveCell.CurrentRegion.Address, _
TableDestination:="", TableName:="Pivot-Tabelle1"

Dim obj As Variant

If Me!Spalte.ItemsSelected.Count > 0 Then
For Each obj In Me!Spalte.ItemsSelected
xlAnw.ActiveSheet.PivotTables("Pivot-Tabelle1"). _
PivotFields(Me!Spalte.ItemData(obj)).Orientation = xlRowField
Next obj
End If

If Me!Data.ItemsSelected.Count > 0 Then
For Each obj In Me!Data.ItemsSelected
xlAnw.ActiveSheet.PivotTables("Pivot-Tabelle1"). _
PivotFields(Me!Data.ItemData(obj)).Orientation = xlDataField
Next obj
End If

If Me!Zeile.ItemsSelected.Count > 0 Then
For Each obj In Me!Zeile.ItemsSelected
xlAnw.ActiveSheet.PivotTables("Pivot-Tabelle1"). _
PivotFields(Me!Zeile.ItemData(obj)).Orientation = xlColumnField
Next obj
End If

If Me!Seite.ItemsSelected.Count > 0 Then
For Each obj In Me!Seite.ItemsSelected
xlAnw.ActiveSheet.PivotTables("Pivot-Tabelle1"). _
PivotFields(Me!Seite.ItemData(obj)).Orientation = xlPageField
Next obj
End If

Set xlAnw = Nothing

Exit_Befehl50_Click:
Exit Sub

Err_Befehl50_Click:
MsgBox Err.Description
Resume Exit_Befehl50_Click

End Sub
What can I do to make it run?

Please help!

Franka

XY Plot From Pivot Table.
Hi,

I would like to plot a XY scatter chart of engineering data, say resistance measured versus various voltages, from a pivot table.

Unfortunately, Excel does not allow making XY scatter plots from a pivot table. Any way you know of around this?

A pivot plot would be nice way to visualize our data, but the X-axis data must be treated as numerical (the intervals between measurements are not uniform).

Line plots are allowed from pivotTable but they treat X-axis data as text values. So 0.1, 0.2, 1.0 would be plotted on equal intervals. Hope this makes sense.

Thank you in advance

Pivot Table Query
i hv created a pivot table and set 'ShowPages Entity = Division' so that all pages will be separated into a separate workbook.

i hv the Division field as the page field, with Divisions 1, 2, 3, 4 and 5.

however my problem now is that after each page is in its own workbook, the user is still able to change the page field to that of other pages. for example, i have 5 workbooks, each containing the data from each Division. however the user can easily change the page field on the wb which holds the data of Div 1 to Div 2 and view the records from Div 2.

is there any way to disallow the user to change the page field at each wb holding data from Div 1,2,3,4,5 respectively

Set All Pivot Table Fields To All
I have an excel file with many pivot tables. What I want to do is at opening to set all pivot table fields to all. Basically reset the file. If the file was used and there were selections made in the pivot table; when the file is reopened it shows previous selections.

My goal is to set all pivot tables to all each time the file is open. This is what I have and it doesn't seem to work.

Sub All()
Dim pt As PivotTable
Dim ws As Worksheet
Dim pf As PivotField

For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
For Each pf In pt.PivotFields
pf.CurrentPage = "(All)"
Next
Next
Next

End Sub

Use ADO Recordset With Pivot Table
I'm trying to use the results of an ADO query as the data source for a pivot table

Code:
Sub PivotTable()

Dim objPivotCache As PivotCache
Dim xlRange As Excel.Range
Dim ptTable As Excel.PivotTable
Dim xlWSheet As Excel.Worksheet
Dim objsht As Excel.Worksheet
Set objsht = sheets(sheets.Count)

Set xlRange = Range("A1")

ActiveWorkbook.sheets.Add

Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal)
Set objPivotCache.Recordset = rsPub

With objPivotCache
.CreatePivotTable TableDestination:=xlRange, _
TableName:="InvoiceData"
End With

I get to there and get a run time error 1004

What is wrong?

I stole some of this from here:

http://www.xtremevbtalk.com/printthread.php?t=97172

Thanks

TS

Excel Pivot Table Using VBA
All there the VBA experts,

I got this piece somewhere and am try to create my pivot table but without success. It shows me a run-time error "9" and "subscript out of range".

My worksheet is in Sheets(4) and has data in 35 columns and 2225 rows. RowField name is in A1, ColumnField name is in G1, PivotTables(1).PivotField name is in E1. I am trying to include the pivot table as a part of the reporting automation process in my boss's office. Any help would be much appreciated.

Code:
Sub create_my_PivotTable()
Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Dim FinalCol As Long
Set WSD = Worksheets("PivotTable")

' Delete any prior pivot tables
For Each PT In WSD.PivotTables
PT.TableRange2.Clear
Next PT

' Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=PRange)

Set PT = PTCache.CreatePivotTable(TableDestination:=WSD.Cells(2, FinalCol + 2), _
TableName:="PivotTable1")

' Set up the row & column fields
PT.AddFields RowFields:=Array("Material"), _
ColumnFields:="Cust Request Dt"
With ActiveSheet.PivotTables(1).PivotFields("Order_Type")
.Orientation = xlPageField
.Position = 1
End With
End Sub

Report Under Pivot Table
Hi There,

2 question to ask :

1) How can i display my report under the pivot table? Any coding for me to review. Thanks

2) How to change the entire column under excel to a certain format using VB 6.0? For example, the format is #,##0.000

please advice....

Pivot Table Problem
This may sound like an easy question but it has been puzzling me for days!!
Any Help on this would be great

3 meter numbers, All different readings taken on different dates. On each visit all 3 are read. I would like to be able to have a pivot table where the difference between the readings taken is shown. Based on chronological order following the date read column to sort by.

My data will look like this –

Column A ______ Column B _____Column C___Column D
Little Richard SP___C61L 00578____15/12/2004___63587
Little Richard SP___C66L 12364____15/12/2004___63033
Little Richard SP___C66L 09812____15/12/2004___95529
Little Richard SP___C61L 00578____15/01/2004___63597
Little Richard SP___C66L 12364____15/01/2004___63061
Little Richard SP___C66L 09812____15/01/2004___95568
Little Richard SP___C61L 00578____15/02/2004___63647
Little Richard SP___C66L 12364____15/02/2004___63172
Little Richard SP___C66L 09812____15/02/2004___95754

I need the output of days difference between readings on each meter. And a total difference between the current and last reading for the corresponding meter number.

So for the above I would like to show 31 days for the difference on all meter numbers and then for each meter number a difference of

C61L 00578 - 10
C66L 12364 - 28
C66L 09812 - 39

C61L 00578 - 50
Etc……

Then to calculate the amount used per day for each meter number but I can manually do that if I can get the differences in the pivot table

Rob

VB6 And Pivot Table Automation
During coding i get the error invalid procedure.

coding

Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object
Dim MyRange As String

xlWs.Cells(2, 1).CopyFromRecordset rsXcl

a = xlWb.Sheets("Sheet1").Range("A1").Address
lastcell = xlWb.Sheets("Sheet1").Range("A1").SpecialCells(xlCellTypeLastCell).Add ress
MyRange = "$A$1:" & lastcell

' Add pivot table
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=xlWb.Sheets("Sheet1").Range(MyRange)).CreatePivotTable TableDestination:="", _
tablename:="Pivottable2"

In the last line it says Invalid Procedure and comes in Debug option.

Is there any one to help me out.

Wildcards In Pivot Table
Folks,
Here is the question.
How do I use a wildcard in a Calculated Item formula in a pivot table.
I want to pull out the data that starts with two characters and i don't care what it ends with. For example I have 37 pivot items in a pivot field, but I only want to pull out those that start with "AB"
The items all have four digits, but they start with all sorts of things. Once again i only want the "AB" items visible when the macro runs.
Following that:
All other items have six different categories that they fall into. I can count them, but I want the macro to subtract the AB entries from whatever of the six categories they fall into. I want them counted separately.
Feeling the deadline pain here.
Sheesh
Appreciate the support.

Creating Pivot Table With Vb6
I am currently trying to make my vb application (not vba) create a pivot table in excel. What I did plan was first creating one Excel sheet with the data (in my case three columns: one with dates, one with region-names and a third one with a value that was measured in that region at that moment). then I wanted to simply create a pivot table which should have the regions in the columns and the dates in rows (or other way around) and the values with their totals in the "middle".
What I tried was:

xlBook.Worksheets.Add
xlBook.Sheets(1).Name = "Pivot Sheet"

xlBook.Sheets("Ausgangsdaten Pivot").PivotTableWizard xlDatabase, "Data for Pivot" ' where "Data for Pivot" is a named area

ActiveSheet.PivotTables(1).AddFields ColumnFields:=Array("Date", "Region")
ActiveSheet.PivotTables(1).PivotFields("Region").Orientation = xlRowField

this created an empty pivot table for me with Date and Region in Rows and Columns. But how do I get the Values in the "middle"? Does anybody have experience with this kind of a problem? Pivottables are so great. Why does creating them have to be so complicated?

Pivot Table Pivotitems Help
I have this macro which takes a block of data in the activesheet and creates a pivot table from it.

Code:
Sub PivotTable()

' This macro creates a pivot table

a = Range("A1").Address
b = Range("A1").End(xlToRight).Address
c = Range(a, b).Columns.Count
d = Range("A1").End(xlDown).Offset(0, c - 1).Address
Range(a, d).Select

' This section creates the pivot table

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=Range(a, d)).CreatePivotTable TableDestination:="" _
, TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Diagnosis Type"), "Count of Diagnosis Type", _
xlCount
With ActiveSheet.PivotTables("PivotTable1")
.PivotFields("Gender").Orientation = xlPageField
.PivotFields("Diagnosis Type").Orientation = xlColumnField
.PivotFields("Baseline Assessment Base").Orientation = xlRowField
End With

ActiveSheet.Name = "Pivot Table"
Sheets.Add.Name = "Chart Data"
Sheets("Pivot Table").Select

Sheets("Pivot Table").PivotTables("PivotTable1").PivotFields("Baseline Assessment Base").ShowAllItems = False

End Sub

I want to make all the entries for "Baseline Assessment Base" (which runs down the right of the table) blank.

I thought the last line would do it, but alas, it doesn't!

Can anyone help?

Pivot Table Grouping
Hello,

I currently have the following code to make a number of pivot tables from my data sheet (removed a lot of the macro), all works fine however I cannot work out how to group the age range automatically.

I tried the recorder but that is useless, heres what I have so far

Code:
Worksheets("Sheet1").Name = "Age Range & Gender Split"
Worksheets("Sheet2").Name = "Gift Breakdown"
Worksheets("Sheet3").Name = "Top Recruiters"

Dim PTCache As PivotCache
Dim PT As PivotTable

'agerange
Set PTCache = ActiveWorkbook.PivotCaches.Add _
(SourceType:=xlDatabase, SourceData:=Sheets("data").Range("A1").CurrentRegion.Address)

Set PT = PTCache.CreatePivotTable _
(TableDestination:=Sheets("Age Range & Gender Split").Range("a1"), _
TableName:="AgeRange")

With PT
.PivotFields("siteprimaryoffice").Orientation = xlPageField
.PivotFields("age").Orientation = xlRowField
.PivotFields("formid").Orientation = xlDataField
.PivotFields("formid").Orientation = xlDataField
.PivotFields("siteprimaryoffice").Caption = "Office"
.PivotFields("Count of formId").Caption = "Supporters"
.PivotFields("count of formid2").Caption = "Supporters %"
End With
With Sheets("Age Range & Gender Split").PivotTables("AgeRange"). _
PivotFields("Supporters %")
.Calculation = xlPercentOfTotal
.NumberFormat = "0.00%"
End With

Sheets("Age Range & Gender Split").PivotTables("AgeRange").Format xlReport2
The recorder gives me this but does not work everytime

Code:
Range("A8").Select
Selection.Group Start:=20, End:=100, By:=5
Anyone got any tips on how to code it properly?

Thanks

Pivot Table Style
Hi again,

I am trying to find what are the different values one could apply to the style of a pivot table.

If I have something like (this file I got from lacher.com)

[vb]

With ActiveSheet.PivotTables(1)
.TableRange1.Style = "PivLabel" ' style for cells above row area of table
.DataBodyRange.Style = "PivData" ' style for data area
.RowRange.Style = "PivRow" ' row area
.ColumnRange.Style = "PivColumn" ' column headings
.TableRange1.EntireColumn.AutoFit ' adjust row and cell heights to fit new style
End With

[vb]

Where do I find an exaustive list of Styles and Properties? The online help of VBA seems certainly not be the place for that.

Thanks really a lot,

Gustavo

Pivot Table And Subtotals
hi.

i'm using some code to create a pivot table and i'm trying to specify that it doesn't include subtotals on a field.
As far as i can see the code used is:

ActiveSheet.PivotTables("PivotTable1").PivotFields("name").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)

where False is stated once for each value in the field.

My problem is that the number of values will vary and so i can't write False a fixed number of times.

Does anyone know if there is a way around this?

Oh yeah, and another query. Does anyone know if the carriage return "square" excel uses can be replace with, say, a comma using code?

Thanks!

Excel Pivot Table...
When I click the refresh button, I get an error that states there are too many row or column items. Is there a such limt? Or, is my problem elsewhere? I have 7 columns and 150 rows.

Well the strange thing is that I have two users that access this pivot table; one is using Excel 2002 and the other is Excel 2000. The one using 2000 is the one getting the error and not the one using 2002. The computers themselves are the same throughout. Is is a performance issue with the computer since it works on the 2002 version?

Pivot Table Error
I try to do a pivot table within VB as a function. But I always get errors on the first line of code when I run it. Yet it was a recorded macro!

Code:
Sub Piv_Table()

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Data!R3C2:R190C2").CreatePivotTable TableDestination:= _
"'[June 2003.xls]Data'!R210C2", TableName:="PivotTable8", DefaultVersion:= _
xlPivotTableVersion10
With ActiveSheet.PivotTables("PivotTable8").PivotFields("City")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable8").AddDataField ActiveSheet.PivotTables( _
"PivotTable8").PivotFields("City"), "Count of City", xlCount
Range("B216").Select
With ActiveSheet.PivotTables("PivotTable8")
.ColumnGrand = False
.RowGrand = False
End With
End Sub

Creating A Pivot Table Using Vb
HELP PLEASE

I have a macro set up to create a pivot table from a set data range. Since the data is constantly changing in size when you run the macro it doesn't pick up the new rows of entered data.

I have the code to select the range.

Selection.CurrentRegion.Select

How do i apply this to my data range in the pivot table source data

Can anybody help me

Regards

Paul

Pivot Table Calc; Non-vba
I am sorry if this is not the place to ask this, but I have a question about pivot tables, not using vba. If there is somewhere else to go ask this, please direct me there. I do not have newsgroup access at work, otherwise I would go to an Excel forum.

I have a pivot table with a few columns and I want to show percent by category. Here is a simplified example:

Group Gender Total
A M 10
F 10
B M 12
C M 8
F 12 etc...

I want to see

Group Gender Total Percent (of Group)
A M 10 50
F 10 50
B M 12 100
C M 8 40
F 12 60

I tried doing all the percent of choices in the data fields and I tried to do a calculated field, but I can't seem to get it. Is this possible?

Thanks

Pivot Table Options
Hello-

For those of you that work with pivot tables I'm sure you have come across the situation where you have something in the cells below the pivot table, then you add more data to the data source and when the pivot table refreshes it asks if you want to overwrite the cells below, which wipes out what you have there.

My question is first, is there any setting either in 2003 or 2007 that tells the pivot table to push anything outside of the table down when the table gets bigger? If not, is there a way I can detect this programmatically before it happens and do something with a macro to move this data down or cut/paste it? Part of the problem is that it is an outside program refreshing the data so I am assuming I would need to copy that whole area on opening of the file.

Any help is appreciated.

Thanks!

A Pivot Table Question
Hi, guys,
I have a Pivot table question about the following P-table I created. My problem is that I wanted Grandtotals for individual quarters, not the Grandtotal of the column. For instance, the last col (pct wanted) for 2004Q4 was the numbers I wanted and calculated using formula not produced by P-table process and hopefully P-table can produce the same type of numbers for the rest of the quarters but I couldn't figure out how.
I shopped around but nothing satisfactory has come up.
Thanks in advance.
John

Data
PERIODproductvaluevalue %pct wanted
2004Q4a207181.17%4.98%
b 484012.72%11.64%
c 431892.43%10.39%
d 1072396.04%25.79%
e 1771589.97%42.61%
f157810.89%3.80%
g33080.19%0.80%
2004Q4 Total41579423.40%100.00%
2005Q1a206581.16%
b 503032.83%
c 513852.89%
d 1114766.27%
e 18450410.38%
f171130.96%
g36610.21%
2005Q1 Total43910024.71%
2005Q2a407422.29%
b 1015285.71%
c 1143136.43%
d 22905512.89%
e 39243222.09%
f359712.02%
g77480.44%
2005Q2 Total92178951.88%
Grand Total1776683100.00%

Pivot Table Refresh?
Can someone help me with looping this so that we don't have to re-write a script for each sheet that has tables on it... I'm talking 10 - 20 tables per sheet and numerous sheets in numerous excel files...
____________________________________________
CODE:

Sub PivotRefresh()
Dim pivot As String

'I want it to run through each sheet

For Each sht In Workbooks 'Can I make this into an array of 5 out of 20 sheets?
x = 1
'I need an error statment added that will automatically goto next sheet when the Pivot table does not exist
Do While (x < 20) 'there are never more than 20 per Sheet
x = x + 1

pivot = "PivotTable" & x
ActiveSheet.PivotTables(pivot).RefreshTable

Loop

Next sht

End Sub

Excel Pivot Table With AQL
Hi,
I have a question of query database for SQL server and display summary result on Excel Pivot Table. I have been write a VBA marco to connect SQL server , retrieve data and display on Excel Pivot Table as list in below:

Sub connectSQLObject()
Dim QArray As Variant
Dim Range1 As Range
Dim ConnectString As String
Dim PivotName As String
Dim Pivot3 As PivotTable

PivotName = "Pivot3"
Worksheets(1).Select
Set Range1 = Worksheets(1).Range("A30")

ConnectString = "ODBC;" & "DBQ=" & ThisWorkbook.Path & "crmdbv310_RnD.MDF;" & "Driver={sql server};
SERVER=IMSBTPMDEV ;UID=Research ;PWD=Research ;DATABASE=crmdbv310_RnD"

QArray = Array(ConnectString, "SELECT * FROM tblCustomer WHERE CustCode < 1000")

PivotName = "Pivot3"
Worksheets(1).PivotTableWizard _
SourceType:=xlExternal, _
SourceData:=QArray, _
tableDestination:=Range1, _
tableName:=PivotName, BackgroundQuery:=False
Set Pivot3 = Worksheets(1).PivotTables(PivotName)

With Pivot3
.PivotFields("CustCode").Orientation = xlColumnField
.PivotFields("CustName").Orientation = xlRowField
.PivotFields("CompRegNo").Orientation = xlDataField
End With

End Sub

My problem are:
1. Connect SQL string problem:
I have writed a connection string statement for SQL server as show in below, this connection string is working but EXCEL always prompt a dialog box required me re-
enter password and user_id. (Note: the Database User_ID and Password are same that is 'Research') Could you help me debug below connection string. Thank you.
ConnectString = "ODBC;" & "DBQ=" & ThisWorkbook.Path & "crmdbv310_RnD.MDF;" & "Driver={sql server};
SERVER=IMSBTPMDEV ;UID=Research ;PWD=Research ;DATABASE=crmdbv310_RnD"

2. SQL query statement limitation ?
If I used simple SQL statement to retreive data and display out via Excel Pivot Table, the pivot table can easy display out the relate data fields as show in below
code:
.......
QArray = Array(ConnectString, "SELECT * FROM tblCustomer WHERE CustCode < 1000")

.......
.PivotFields("CustCode").Orientation = xlColumnField
.PivotFields("CustName").Orientation = xlRowField
.PivotFields("CompRegNo").Orientation = xlDataField
........

Above query statement just limits on retreive data filed on a table from database. I have been tried join to table and display data but Excel cannot execute. The code
as show in below:
.......
QArray = Array(ConnectString, "SELECT * FROM tblCustomer.StateID ,
tblAddressState.StateName, tblCustomer.CustName , tblCustomer.CustCode, tblCustomer.CompRegNo WHERE
tblCustomer.StateId = tblAddressState.StateId")

.......
.PivotFields("tblCustomer.CustCode ").Orientation = xlColumnField
.PivotFields("tblAddressState.StateName").Orientation = xlRowField
.PivotFields("tblCustomer.CompRegNo").Orientation = xlDataField
........

Can you helping debugging above VBA Marco. Thank you.

If you can provide any web site relate Excel Pivot Table with VBA and SQL there is very thanl you so much.

Best Regards,
Hoo
URL

Pivot Table Code
Hi,
I have created a simple pivot table with monthly data. The
result is to show one month at a time so the pivot table will
show customer and month. In a summary report I want the ability
to change the month in a cell which will then update the pivot
table by removing the current month and bringing in the month
selected. I can't seem to get any script to work for this, any
advice thanks Dave

VB6 & OWC10-Pivot Table
This is my first time trying to set up a pivot table in VB6.

I the connection to me SQL server set up and I works fine. What I would like to know is how do I desing the pivot layout via code so that the user does not have to.

Hoping someone can help.

thank you

Pivot Table Printing
Hi, I'm writing to this group about a problem with the pivot table
"printing example in the OWC Toolpack

The Printer Icon is not getting enabled.

Anybody has an idea ?

Need Help With Pivot Table In Excel
I create a spread sheet from Access. On the excel side I create a pivot table and charts from the pivot table. In one case I hide several columns of data to create the chart. In my code I want column 1 and 2 of the visible columns for the series for the chart. But what I get is the hidden column one. How can I get the visible column one?

Code:
Function GetSeriesLabels(ByVal pvtTable As PivotTable) As RowCol
    Dim typRowCol As RowCol

    typRowCol.intStartRow = pvtTable.DataBodyRange.Row
    typRowCol.intEndRow = pvtTable.DataBodyRange.Row + pvtTable.DataBodyRange.Rows.Count - 2
    typRowCol.intRowCount = typRowCol.intEndRow - typRowCol.intStartRow + 1
    GetSeriesLabels = typRowCol
End Function

            SeriesLabelCells = GetSeriesLabels(pvtTbl)
            For intSeries = 1 To SeriesCount
                NewChart.SeriesCollection.NewSeries
                NewChart.SeriesCollection(intSeries).Name = pvtTbl.ColumnFields(1).PivotItems(intSeries).Name
                NewChart.SeriesCollection(intSeries).XValues = "='" & wksheet.Name & "'!" & _
                  "R" & SeriesLabelCells.intStartRow & _
                  "C1:R" & SeriesLabelCells.intEndRow & "C1"
                NewChart.SeriesCollection(intSeries).Values = "='" & wksheet.Name & "'!" & _
                    "R" & SeriesVal.intStartRow & _
                    "C" & pvtTbl.DataBodyRange.Column + intCount & _
                    ":R" & SeriesVal.intEndRow & _
                    "C" & pvtTbl.DataBodyRange.Column + intCount
                intCount = intCount + 1
            Next

Scott
The worst thing about wearing glasses is them being on your face.
Nothing is impossible, it just looks that way.

Pivot Table Splitting
i have a pivot table which i want to slpit into 3 separate pivot tables.
the initial table is made up of 4 columns in which the last column gives the total sum of the data.

my prolem is how to split this into 3 separate sheets where the sum is more than £201.00 less than £150.00 and between £151.00 and £200.00

can any one help

How To Get Only Running Sum In A Pivot Table ???
How to get only the running sum of a Data field in Excel Pivot tables. I have a pivot table and what I need is to see is only the running sum of a field and all the values above it should be invisble or deleted(if pivots allows to delete) without affecting the running sum. I am creating the Pivot from scratch through VBA code and need to hide all the values above there running sum through VBA, well even mannual method will be great if some one can let me know how to do that.

Pivot Table Row Problem
I have a work file that changes on a monthly basis. I am having trouble though with telling SourceData what my range is. I know that my table will always start at "A3" (R3C1) and that it will be 20 columns long but I don't know how many rows it will be everytime. I have tried.
Dim RangeEnd As Integer
Range("A3").Select
RangeEnd = ActiveCell.SpecialCells(xlLastCell).Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"WORKDATA!R3C1:RangeEnd",
VB does not like that at all probably because RangeEnd is an int and it looks like SourceData is looking at a string. Any suggestions, helps or hints would be greatly appreciated.

Thanks,
bcnul8r

VB On Pivot Table

Hi all,I am trying to create a VB code to allow users to choose the number of key figures and characteristics he/she wishes to pivot against in BeX reporting. Any idea how this can be done?

Hi all,

I am trying to create a VB code to allow users to choose the number of key figures and characteristics he/she wishes to pivot against in BeX reporting. Any idea how this can be done?