Pivot table creation via Macro: a greyed rectangle appears

[steackbrit]

Hi guys and girls

Well, first message on this forum... I figured after consulting it for a very long time, I am as well ask the questions directly!

So here's my problem:
I need to update a Pivot table via VBA given new settings in a SQL Query (basically date and code settings). My process is as follows: I erase the previous PivotTable, then create a new one. It works fine but I systematically get a grey rectangle in the middle of the field, I guess it's the table field list which just pops up for a fraction of a second. Not very serious but annoying as I'm running the codes from a usf with a progressbar, and it looks messy (my USF becomes transparent and I see the rectangle behind it ^^)

Any idea or experience on this issue?

I've tried application.screenupdating=false, application.displayalerts=false and activeworkbook.showpivottablefieldlist=false

here's my code


Sub GET_ALLOCATION_HISTORY()


Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActiveWorkbook.ShowPivotTableFieldList = False

Worksheets("ALLOC_HISTORY").Select
Cells.ClearContents
Dim code As String, endd As String
code = Worksheets("settings").Range("C3").Value
endd = Worksheets("settings").Range("C6").Value

Application.ScreenUpdating = False

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = Array(Array( _
"ODBC;DSN=FUND_DATA_TEST;Description=dd;UID=jesab;APP=Microsoft Office XP;WSID=NWKR0033;DATABASE=FUND_DATA_TEST;Trusted_Connection=Ye" _
), Array("s"))
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT VW_AssetType.Date, VW_AssetType.FundCode, VW_AssetType.Weight, VW_AssetType.AssetType" & Chr(13) & "" & Chr(10) & "FROM FUND_DATA_TEST.""CA-AM\jesab"".VW_AssetType VW_AssetType" & Chr(13) & "" & Chr(10) & "WHERE (VW_AssetType.FundCode='" & code & "') AND (VW_AssetType.Date<='" & endd & "')" & Chr(13) & "" & Chr(10) & "ORDER " _
, "BY VW_AssetType.Date")
.CreatePivotTable TableDestination:= _
"ALLOC_HISTORY!R3C1", TableName:="PivotTable8", _
DefaultVersion:=xlPivotTableVersion10
End With

ActiveWorkbook.ShowPivotTableFieldList = False

With ActiveSheet.PivotTables("PivotTable8").PivotFields("Date")
.Orientation = xlRowField
.Position = 1
End With

ActiveWorkbook.ShowPivotTableFieldList = False

Application.ScreenUpdating = False
With ActiveSheet.PivotTables("PivotTable8").PivotFields("AssetType")
.Orientation = xlColumnField
.Position = 1
End With

ActiveWorkbook.ShowPivotTableFieldList = False

ActiveSheet.PivotTables("PivotTable8").AddDataField ActiveSheet.PivotTables( _
"PivotTable8").PivotFields("Weight"), "Sum of Weight", xlSum
ActiveWorkbook.ShowPivotTableFieldList = False
Range("D10").Select
With ActiveSheet.PivotTables("PivotTable8")
.ColumnGrand = False
.DisplayErrorString = True
.RowGrand = False
End With

ActiveWorkbook.ShowPivotTableFieldList = False

Cells.Select
Selection.Copy
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G13").Select
Application.CutCopyMode = False

Worksheets("settings").Select

End Sub

 

[SydneyGeek]

Hi steackbrit, welcome to the Board!

I've taken the liberty of cleaning up your code a little, mostly by assigning your pivot table to an object variable, but also by removing repeating instructions.

You should find that the ManualUpdate property does the job for you: set it to TRUE to prevent automatic refreshes, and FALSE to force a refresh at the end.

Got a question though: Why are you selecting all, then pasting values over itself at the end? Do you want to transfer the data to another location once the PT has done its job, or is there another reason?

Sub GET_ALLOCATION_HISTORY()
Dim code As String, endd As String
Dim PT As PivotTable

Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActiveWorkbook.ShowPivotTableFieldList = False

Worksheets("ALLOC_HISTORY").Select
Cells.ClearContents
code = Worksheets("settings").Range("C3").Value
endd = Worksheets("settings").Range("C6").Value

Application.ScreenUpdating = False

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
    .Connection = Array(Array( _
    "ODBC;DSN=FUND_DATA_TEST;Description=dd;UID=jesab;APP=Microsoft Office XP;WSID=NWKR0033;DATABASE=FUND_DATA_TEST;Trusted_Connection=Ye" _
    ), Array("s"))
    .CommandType = xlCmdSql
    .CommandText = Array( _
    "SELECT VW_AssetType.Date, VW_AssetType.FundCode, VW_AssetType.Weight, VW_AssetType.AssetType" & Chr(13) & "" & Chr(10) & "FROM FUND_DATA_TEST.""CA-AM\jesab"".VW_AssetType VW_AssetType" & Chr(13) & "" & Chr(10) & "WHERE (VW_AssetType.FundCode='" & code & "') AND (VW_AssetType.Date<='" & endd & "')" & Chr(13) & "" & Chr(10) & "ORDER " _
    , "BY VW_AssetType.Date")
    .CreatePivotTable TableDestination:= _
    "ALLOC_HISTORY!R3C1", TableName:="PivotTable8", _
    DefaultVersion:=xlPivotTableVersion10
End With

Set PT = ActiveSheet.PivotTables("PivotTable8")
PT.ManualUpdate = True

With PT.PivotFields("Date")
    .Orientation = xlRowField
    .Position = 1
End With

With PT.PivotFields("AssetType")
    .Orientation = xlColumnField
    .Position = 1
End With

PT.AddDataField ActiveSheet.PivotTables( _
"PivotTable8").PivotFields("Weight"), "Sum of Weight", xlSum

With PT
    .ColumnGrand = False
    .DisplayErrorString = True
    .RowGrand = False
End With

PT.ManualUpdate = False

Cells.Select
Selection.Copy
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G13").Select
Application.CutCopyMode = False

Worksheets("settings").Select

End Sub

Hope this helps

Denis

 

[steackbrit]

Thanks!

Hey

thanks for this quick response; I'll try your code early next week, it's getting rather late in my timezone ^^

As for the Copy.paste bit of code, yep indeed, it's because I use this data range for other workbooks and find it easier to transfer and keep data in values (it's a rather huge workbook).

Thanks again!