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]

After refresh

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

 

[rbnaik]

I am getting error message "Object does not support this property or method" .
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
.Visible = False
.ClearAllFilters
End With

 

[CSmith]

That was from your original code...

 

[rbnaik]

Dear CSmith,
I cannot thank you enough for your support. I could correct all the problems with your help.
My data had some cleaning to do and then today I tested it. It worked like a charm..
Once again.. thanks..

 

[CSmith]

Glad to have helped! Thank you for the follow up!