Important point I forgot to mention... The pivot table is refreshed upon
opening with the following macro which looks for a specific Access file in
the same folder and updates only if it finds the file. I don't seem to have
a problem when the Access file is not in the same folder and the macro exits.
Also the file now is not calculating. Earlier today while the file was open
I switched to Manual Calc to work on another file. I've returned to
Automatic Calc. The other files calc fine, but not this one. This is true
whether the Open macro runs or not. Any idea what could cause that. I've
closed Excel once, but it didn't seem to help. Any ideas about that one?
Private Sub Workbook_Open()
Dim PTCache As PivotCache
Dim PT As PivotTable
Application.DisplayAlerts = False
Application.ScreenUpdating = False
' Create a Pivot Cache
Set PTCache = ActiveWorkbook.PivotCaches.Add _
(SourceType:=xlExternal)
' Path to database file
dbFile = ThisWorkbook.Path & "\variance2007.mdb"
If Dir(dbFile) = "" Then Exit Sub
' Connection String
ConString = "ODBC;DSN=MS Access Database;DBQ=" & dbFile
' QueryString
queryString = "SELECT * FROM `" & ThisWorkbook.Path & _
"\variance2007`.Data Data"
With PTCache
.Connection = ConString
.CommandText = queryString
.MaintainConnection = False
End With
' Create pivot table
Sheets("Variance Pivots").Activate
ActiveSheet.PivotTables("ActualTable").PivotSelect "", xlDataAndLabel
Selection.ClearContents
Set PT = PTCache.CreatePivotTable( _
TableDestination:=Sheets("Variance Pivots").Range("A8"), _
TableName:="ActualTable")
' Add fields
With PT
' Add fields
.PivotFields("Code").Orientation = xlRowField
.PivotFields("Code").Subtotals = Array( _
False, False, False, False, False, False, False, False, False,
False, False, False)
.PivotFields("Cat").Orientation = xlRowField
With .PivotFields("Dom")
.Orientation = xlColumnField
.PivotItems("LA").Position = 1
.PivotItems("OC").Position = 2
.PivotItems("DC").Position = 3
.PivotItems("SD").Position = 4
.PivotItems("CH").Position = 5
.PivotItems("NY").Position = 6
.PivotItems("SF").Position = 7
.PivotItems("NJ").Position = 8
.PivotItems("SV").Position = 9
.PivotItems("VA").Position = 10
.PivotItems("Int").Position = 11
.PivotItems("GSO").Position = 12
End With
.PivotFields("Period").Orientation = xlPageField
.PivotFields("Net").Orientation = xlDataField
End With
Sheets("Trend Pivot").Activate
Range("Pivot").Select
strTable = "[" & ActiveWorkbook.Name & "]Variance Pivots!ActualTable"
ActiveSheet.PivotTableWizard SourceType:=xlPivotTable,
SourceData:=strTable
With ActiveSheet.PivotTables("TrendTable").PivotCache
.RefreshOnFileOpen = False
.SavePassword = True
End With
ActiveSheet.PivotTables("TrendTable").RefreshTable
ActiveSheet.PivotTables("TrendTable").AddFields RowFields:="Period", _
ColumnFields:="Cat", PageFields:="Loc"
Application.CommandBars("PivotTable").Visible = False
Sheets("Variance Pivots").Activate
ActiveSheet.PivotTables("ActualTable").SaveData = False
Application.CommandBars("PivotTable").Visible = False
Sheets("Total").Select
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
:
The table that changes just about everytime I open the file includes items as
follows (as rows):
EH
FB
FP
HA
...
LR
MA
where MA is the offending item which keeps rising to the top. I have two
similar files and sometimes it happens on one and not the other.
The other table includes (as columns):
Office Salary
Office Overtime
Contract Labor
Stationary and Supplies
Telephone
...
Rent
where Office Salary, Office Overtime, and Contract Labor are the offending
items, moving to the end. I think this has only happened once. Unfortunately
it may have escaped my notice in the past.
I've been using these two files for almost 7 years and this just started a
few months ago. We didn't change Excel versions, but the firm is always
running updates. My co-worker hasn't noticed it, but I don't know how often
she has accessed the file in the last few months.
Any ideas?
:
What are the items? If you move them to the end, when do they move back
to the top?
Erin Searfoss wrote:
In one file I have two pivot tables that are set to manual sort, but have
taken to resorting themselves in odd ways. One table is moving the last item
to the first position while the other is moving the first three items to the
last three positions. There is nothing in my custom list which would cause
this, and I've never manually placed them in that order. It is effecting the
formulas I have running off these tables.
Has anyone had a similar experience? Does anyone have any suggestions to
make it stop? Any help would be appreciated. Thanks.
Erin
