Cannot SUM the value if connection by external source

whoiswct

New member
Joined
Nov 24, 2012
Messages
57
Reaction score
0
Points
0
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.
 

Attachments

  • data1.xlsx
    9.1 KB · Views: 25
  • PV1_error.xlsx
    12.1 KB · Views: 29
  • sum_of_month_no_calc.jpg
    sum_of_month_no_calc.jpg
    87 KB · Views: 52
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?
 
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?
 
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
 
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.
 
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:

Code:
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...
 
Back
Top