New data added to external source is not automatically visible in pivot table

[rbnaik]

Hi,
I am using Excel 2019. I am connecting to an external data source which is a text file. The data is stored date wise and is imported into pivot table where date is shown as a column field. The data gets added daily to the source. Whenever new data is added, I refresh PivotCache. My problem is after PivotCache refreshing, I can see the new dates in drop-down menu of pivot table, but the new data is not visible at the end of Pivot table (dates are displayed in columns) in the worksheet automatically. I have to manually add these dates.
The apparent problem is that one column is shown as blank (no date is available). I am attaching the code for your ready reference.

Sub DataConnectionCreation()
ActiveWorkbook.Queries("ImprovedTest").Delete
    ActiveWorkbook.Queries.Add Name:="ImprovedTest", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""E:\Market\Combine Test\ImprovedTest.txt""),[Delimiter="","", Columns=16, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""SC_CODE"", Int64.Type}, {""SC_NAME"", ty" & _
        "pe text}, {""SC_GROUP"", type text}, {""SC_TYPE"", type text}, {""OPEN"", type number}, {""HIGH"", type number}, {""LOW"", type number}, {""CLOSE"", type number}, {""LAST"", type number}, {""PREVCLOSE"", type number}, {""NO_TRADES"", Int64.Type}, {""NO_OF_SHRS"", Int64.Type}, {""NET_TURNOV"", Int64.Type}, {""TDCLOINDI"", type text}, {""Date"", type date}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""

 myworkbook = ActiveWorkbook.Name
 myworksheet = ActiveWorkbook.ActiveSheet.Name

    Workbooks(myworkbook).Connections.Add2 "Query - ImprovedTest", _
        "Connection to the 'ImprovedTest' query in the workbook.", _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=ImprovedTest;Extended Properties=""""" _
        , "SELECT * FROM [ImprovedTest]", 2

If Worksheets(myworksheet).PivotTables.Count > 0 Then
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
        .Visible = False
    End With

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
        .ShowAllItems = True ' Displays all the items in pivotfield "Date"
    End With

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
        .PivotItems("(blank)").Visible = False ' Hides records where "Date" is (Blank)
    End With

Else
.....
Endif
End Sub

 

[CSmith]

Private Sub Worksheet_Change(ByVal Target As Range)
  ThisWorkbook.RefreshAll
End Sub

 

[rbnaik]

Hi CSmith,
Thanks for the reply. I have done the changes, but the problem is not yet resolved. Additionally, now, the moment I move cursor even a single cell in any direction in the sheet, the pivotcache is getting refreshed.

 

[CSmith]

Hi CSmith,
Thanks for the reply. I have done the changes, but the problem is not yet resolved. Additionally, now, the moment I move cursor even a single cell in any direction in the sheet, the pivotcache is getting refreshed.

Sounds as if your code is attached to Worksheet_SelectionChanged and not Worksheet_Change

 

[rbnaik]

Yes, you are absolutely right.
Now there is another problem, it is looping in the private function continuously and not exiting it. Also, new date are not still added as columns. It has become just like fan trap.
Is there any other mistake I am committing ?

 

[CSmith]

I would use

Application.EnableEvents = False

before any updates then

Application.EnableEvents = True

after this will keep it from running in continuous loop as your code is in the change event and you are going to cause even more cascades doing the refresh causing a change and refresh and so on

 

[rbnaik]

Boss, simply great!!.
However, the original problem of new dates are not adding as columns in pivot table not yet resolved..

 

[CSmith]

Are you wanting the filter to go away? or ? Not sure I understand where your problem is exactly. Are you talking about the dates not having a check mark?

 

[rbnaik]

Yes, I want the filter to go away as well as newly added data (daily) should be visible as last column (newly added dates should have check marks).
For that I have to check the dates manually

Sub Macro1()
'
' Macro1 Macro
'

'
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
        .PivotItems("(blank)").Visible = True
        .PivotItems("1/13/2020").Visible = True
        .PivotItems("1/14/2020").Visible = True
        .PivotItems("1/15/2020").Visible = True
        .PivotItems("1/16/2020").Visible = True
        .PivotItems("1/17/2020").Visible = True
        .PivotItems("1/20/2020").Visible = True
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
        .PivotItems("(blank)").Visible = False
    End With
    ActiveWorkbook.Save

Alternatively, can you help me to delete "Blank" item from pivotchache ?

 

[CSmith]

Add this here

  With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
        .Visible = False
        .ClearAllFilters 
    End With

After that add a filter for blank items