function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
IWyattIWyatt 

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
 
Thanks!
 
IW
shellyginsbshellyginsb
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.

-Shelly
NonickSMSNonickSMS
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.
bvdbbvdb

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

bump
GuyClairboisGuyClairbois

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
        Else
            
            ' 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!

Guy