Pivot Tables and VBA

Laphan · Apr 17, 2004

Hi All

I do a lot of Excel > Query > SQL DB bits and bobs and because of the
complexity of my latest report I need to put this data into a pivot table.
I want to make it as auto as possible so the way I want to do it is as
follows:

Worksheet 1 (called "Enter Date Range") asks the user to enter a start date
in cell B1 and an end date in cell B2 before clicking a simple form button.

This form button executes the following Macro code module:

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = _
"ODBC;DSN=SQL;UID=rob;PWD=;WSID=Me;DATABASE=Accounts;AutoTranslate=No;UsePro
cForPrepare=0"
.CommandType = xlCmdSql
.CommandText = Array( <<< my SQL DB query incoprating my start and
end at values from the first worksheet >>> )
.CreatePivotTable TableDestination:="", TableName:="PivotTable1"
End With
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
With ActiveSheet.PivotTables("PivotTable1").PivotFields("NAME")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("NAME2")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("QUANTITY")
.Orientation = xlDataField
.Position = 1
End With

Although the above generates the required report, my problems are as
follows:

1) It keeps putting the above pivot table into a new worksheet everytime. I
want it to go to one specific worksheet called "Report" and when the user
goes back and enters a new date range (on the "Enter Date Range" worksheet)
I want it to simply execute and use the same "Report" worksheet to display
the new pivot report data without creating orphaned resources. Any ideas on
how to do this?

2) I'm using Excel 2000 on a PC and annoyance no. 1 is that every time the
above creates the pivot table it shows the Pivot toolbar with the extended
grey bottom bit showing the extra fields (the Display/Hide Fields button at
the far right toggles the display of this). When this is displayed it also
mucks about with the display of the report, eg blue borders and things. I
don't want any of this can the toolbar be stopped from doing this?

3) Major annoyance no.2 why the hell can't I remove the grey data field
boxes from the top of my pivot report???? It makes the report look crap and
if I click on them and select the Hide option it loses my data. The
unsightly options I am talking about are as follows:

<Sum of of a Field> <Drop Down menu of a Field>
<Drop Down menu of a Field> my data my data
my data my data my data
my data my data my data

I'm talking about the 3 encapsulated in the <>'s.

An end user will always click on these and cock the report up, any ideas on
how to hide these successfully without losing the data???

Many thanks.

Laphan

mark · Apr 18, 2004

1) It keeps putting the above pivot table into a new
worksheet everytime.

The problem is that you are repeatedly adding a new
PivotCache and calling the .CreatePivotTable method...
thus, it creates a new pivot table each time.

To have only one pivot table, either delete the original
one before you create the new one, or change the source of
the existing one (if necessary) and refresh it.

2) Pivot toolbar with the extended grey bottom bit

I think your talking about the pivot table command bar...
just use this code to turn it off:

Application.CommandBars("PivotTable").Visible = False

3) Major annoyance no.2 why the hell can't I remove the

grey data field boxes from the top of my pivot report????

I'm not sure if you can remove them in anyway, but you can
copy the data from the userange of the pivot table sheet,
and then use pastespecial to put just the data in another
place.

like this:

Sheets("pvTable").UsedRange.Copy
ActiveCell.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Would that help?

Laphan · Apr 18, 2004

Hi Mark

Many thanks for your valued feedback.

I'm no where near a VBA guru so could you explain how I achieve point 1.

All I want to do is put the new query into the same worksheet and not create
a gazillon instances (like you say) each time they run the report.

The Macro code that I have seems to be straightforward apart from the bit
where it keeps adding a new table to a new worksheet. How do I force it to
go to my worksheet and not create new instances???

Thanks

Laphan

mark said:
1) It keeps putting the above pivot table into a new

worksheet everytime.

The problem is that you are repeatedly adding a new
PivotCache and calling the .CreatePivotTable method...
thus, it creates a new pivot table each time.

To have only one pivot table, either delete the original
one before you create the new one, or change the source of
the existing one (if necessary) and refresh it.

2) Pivot toolbar with the extended grey bottom bit

I think your talking about the pivot table command bar...
just use this code to turn it off:

Application.CommandBars("PivotTable").Visible = False

3) Major annoyance no.2 why the hell can't I remove the

grey data field boxes from the top of my pivot report????

I'm not sure if you can remove them in anyway, but you can
copy the data from the userange of the pivot table sheet,
and then use pastespecial to put just the data in another
place.

like this:

Sheets("pvTable").UsedRange.Copy
ActiveCell.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Would that help?

mark · Apr 19, 2004

Hey, sorry I didn't think to look back here until this
afternoon... hope you're still looking.

I'm no where near a VBA guru so could you explain how I

achieve point 1.

Try this...

assuming that the sheet where your pivot table is located
is called pvTable, add a code line:

Sheets("pvTable").cells.delete

That will eliminate the original pivot table, but not
delete the sheet itself.

Then, in your existing code, where it said:

TableDestination:=""

change that line to:

TableDestination:= Sheets("pvTable").Range("a3")

After those two changes, your code should be able to run
repeatedly and only end up with one pivot table.