+ Start a Discussion

Updating Field Value to a null value instead of $0

Hey Excel Connector Peoples,
Can someone tell me how I can have the Excel Connector update a salesforce field (such as "Actual Cost") to be blank instead of "$0".
When I attempt to update the cell with a space, I get an error, and if I merely update a cell with the contents deleted, it ends up getting updated as "$0".
Since I know that I can update the records with these field values to be blank from within Salesforce, I know that it must be possible. I just can't manage to make the Excel Connector do it.
Other things that I have tried:
-Updating with "" in the cell, which yields a data type mismatch error
-Change the cell format from currency to "general" and to "text" which still yields no positive result
-Updating with "(null)" in the cell which yields a type mismatch
Well hello collegue,

The only way I've found around this problem is to use the DataLoader... works like a charm.  I would really like to see excel be able to perform this task also.

Just wanted to confirm that this problem raised THREE YEARS AGO, is still an issue. It would be one 'if' statement that would solve this.

Seems like it's still an issue for us :\




I've created a solution in the Excel code:


Press ALT+F11 to go into vba editor

Search for sforce_connector -> modules -> utils and look up the function "toVBtype"

Replace the code part under ' Case "double", "currency" ' with the following:

    Case "double", "currency"
        If (IsEmpty(value) Or value = "") Then
            Dim unInitVariantNum As Variant
            toVBtype = unInitVariantNum ' handle empty numbers
            ' val() does not use i18n conventions, use CDbl instead, 6.08
            toVBtype = CDbl(value)  ' normal case
            ' 6.01 truncate to the number of digits, Field3 likes it's numbers formated
            If (field.Scale = 0) Then
                toVBtype = Int(toVBtype)
            ElseIf field.Scale > 0 Then
                Dim z: z = InStr(value, Application.International(xlDecimalSeparator))
                If (z > 0) Then  ' need to remove any extra decimal places
                    toVBtype = CDbl(Left(value, z + field.Scale))
                End If
            End If
        End If


The first block handles the empty and/or "" values in Excel.


I've also added this to my improved Excel Connector here: http://code.google.com/p/improved-excel-addin/


Good luck!