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:=xlExter nal)
.Connection = _
"ODBC;DSN=SQL;UID=rob;PWD=;WSID=Me;DATABASE=Accoun ts;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