Cannot SUM the value if connection by external source

[whoiswct]

If I create Pivot table direct in the data1 excel file, the sum of value is correct.
However, if I create connection by external source to the data1 excel file, it cannot sum the value.
What's wrong? Pls kindly help.

[whoiswct]

The problem seems due to the null value in the beginning of the source data.
If I cannot modify the data source and only can use connection by external data, how can I fix it?

[Ken Puls]

I've got to be honest here. There should be a way to make this work, but I haven't been able to crack it.

Personally, at this point, I'd be extracting the data from the other file using VBA, pulling it into a table in your workbook, and summarzing it from there. This gives you the added bonus that you know have a localized table that you can manipulate to work around many of the other issues you've brought up.

Are you interested in looking at that route, or is VBA a no-go?

[whoiswct]

Hi Ken,

I'd like to know more how to extract the data from external excel file to workbook?
Provided that it can refresh the data every time I open the workbook.

Thanks.

Sent from my phone using Tapatalk

[Ken Puls]

Here's what I'm thinking would need to happen:
-Create an Excel table in your report file, and base your PivotTable off that
-Built a macro that would:
--> Clear all records in the table
--> Open the other file
--> Copy all records (which are not NULL) into your report table
--> Close the other file
--> Update your Pivot

I can't look at it right now, but I may be able to spend some time on it tomorrow evening.

[Ken Puls]

Okay, so assuming the following:

• The "remote" workbook, which holds your original data has the data on "Sheet1" starting in A1
• The "report" workbook has:
  
  • A worksheet called "Data" to store the data table
  • A table on the worksheet starting in cell A1
  • The table is named "tblCurrent"

Then you should be able to use this to update it:

Sub RetrieveList()
    Dim wbSource As Workbook
    Dim wsTarget As Worksheet
    Dim sTargetSheet As String
    Dim sFileName As String
    Dim sTable As String
    Dim lstTable As ListObject
    
    Application.ScreenUpdating = False
    
    'Set name of data table and worksheet that holds it here
    sTable = "tblCurrent"
    Set wsTarget = ActiveWorkbook.Worksheets("Data")
    Set lstTable = wsTarget.ListObjects(sTable)
    
    'Set name of the worksheet that holds the data in the remote file
    sTargetSheet = "Sheet1"
    
    'Clear the data in the table
    If lstTable.ListRows.Count > 0 Then lstTable.DataBodyRange.Delete
    
    'Open remote file
    sFileName = CStr(Application.GetOpenFilename)
    If sFileName = "Cancel" Then Exit Sub
    Set wbSource = Workbooks.Open(sFileName)
    
    'Copy data from remote to local file
    With wbSource.Worksheets(sTargetSheet)
        .Range("A1:A" & .Range("A" & .Rows.Count).End(xlUp).Row).Copy
    End With
    lstTable.Range.Cells(1, 1).PasteSpecial xlPasteValues
    
    'Close the remote workbook
    wbSource.Close savechanges:=False
    
    'Sort the table to group blank rows
    With lstTable
        With .Sort
            .SortFields.Clear
            .SortFields.Add Key:=Range(sTable & "[[#All],[Current Month Amount]]"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .Apply
        End With
    End With
 
    'Delete blank rows
    lstTable.Range.AutoFilter Field:=1, Criteria1:="="
    With wsTarget
        .Rows("2:" & .Range("A" & .Rows.Count).End(xlUp).Row).Delete
    End With
    lstTable.Range.AutoFilter Field:=1
End Sub

The variables for the worksheet and table names are at the top of the code, so you can update those to suit.

Let me know if that works...