Hi Ron,

in your util module you asked for suggestions as how to pass a localized date format. If you don't want to use WINAPI32 calls, then using DateSerial() and modifying the result is probably the easiest way to do it. This way, you don't have to worry about the order of years/months/days, 2- or 4-digit years, leading zeroes in days/months or separators, which can be any of the following /.'- (and maybe more). All you have to do is take the result and replace all numeric occurrences with their respective format characters, and leave everything else as is. This can be done inside a For-Loop with Mid$() function calls, with subsequent MID$() method calls or with the Replace() function. I prefer the latter, because it's easiest to understand and very straightforward, but other approaches may be faster or more appealing to the eye. Here's my suggestion:

Function typeToFormat(sfType As String)
    typeToFormat = "General" ' default
    Select Case sfType
    Case "date", "datetime"
        Dim dateTemp As String
      dateTemp = DateSerial(2134, 6, 5)
      dateTemp = Replace(dateTemp, "2134", "yyyy")
      dateTemp = Replace(dateTemp, "34", "yy")      'year may be 2 characters only
      dateTemp = Replace(dateTemp, "06", "mm")
      dateTemp = Replace(dateTemp, "6", "m")        'month format may not have leading zero
      dateTemp = Replace(dateTemp, "05", "dd")
      dateTemp = Replace(dateTemp, "5", "d")        'day format may not have leading zero
      If sfType = "datetime" Then
        dateTemp = dateTemp & " h:mm"
      End If
      typeToFormat = dateTemp
    'Case "date"
      'typeToFormat = "m/d/yyyy" ' wow, cant figure out how to pass a localized format, bummer
    'Case "datetime"
      'typeToFormat = "m/d/yyyy h:mm" ' 5.15
    Case "string", "picklist", "phone" ' , "textarea"
      typeToFormat = "@"
    Case "currency"
      typeToFormat = "$#,##0_);($#,##0)" ' format as currency, no cents (added in 5.15)
    End Select
   
End Function

Since VLookup() and HLookup() won't work with case-sensitive IDs, I currently have to transform them using some binary arithmetics in VBA code (thanks to Sean O'Conner for providing valuable insights):

Function GetID18(ByVal strID15 As String) As String
    Dim i%, j%, n%, c$
    For i = 1 To 3
        n = 0
        For j = 0 To 4
            n = n Or IIf(InStr(1, "ABCDEFGHIJKLMNOPQRSTUVWXYZ", _
                Mid$(strID15, i * 5 - j, 1), vbBinaryCompare) > 0, 2 ^ j, 0)
        Next j
   
        c = c & Mid$("AQIYEUM2CSK0GWO4BRJZFVN3DTL1HXP5", n + 1, 1)
    Next i
    GetID18 = strID15 & c
End Function

Is there any way of intercepting

Simply put the following formula into a formula field of type "Text":

 

Id
& MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",(
    IF(FIND(MID(Id,1,1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,1,0)
    +IF(FIND(MID(Id,2,1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,2,0)
    +IF(FIND(MID(Id,3,1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,4,0)
    +IF(FIND(MID(Id,4,1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,8,0)
    +IF(FIND(MID(Id,5,1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,16,0)
    )+1,1)
& MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",(
    IF(FIND(MID(Id,6,1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,1,0)
    +IF(FIND(MID(Id,7,1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,2,0)
    +IF(FIND(MID(Id,8,1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,4,0)
    +IF(FIND(MID(Id,9,1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,8,0)
    +IF(FIND(MID(Id,10,1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,16,0)
    )+1,1)
& MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",(
    IF(FIND(MID(Id,11,1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,1,0)
    +IF(FIND(MID(Id,12,1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,2,0)
    +IF(FIND(MID(Id,13,1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,4,0)
    +IF(FIND(MID(Id,14,1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,8,0)
    +IF(FIND(MID(Id,15,1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,16,0)
    )+1,1)

 

This is an optimized version of the code example "BPS: 18 digit external ID within the UI" you can find on the Online Help, but unlike the example provided there, it is small enough to fit into a formula field. Also, this code doesn't contain the error found in the online help example, where the second character byte equivalent of the first 5-character chunk was "4" instead of "2". I'd be more than happy if someone could come up with an even shorter version of this formula...Still, I hope you can use this.

 

Cheers,

Erik Mittmeyer

Download Version 6.13

requires this Outlook Edition for the new COM object (thanks foghorn..)

guess it should be AppExchange connector...

my favorite new feature is the live clickable hyperlink created in your worksheet when the conenctor reads a HYPRELINK() formula field in your sforce database.

have fun, report issues,
share & enjoy

Message Edited by Ron Hess on 01-21-2006 11:05 PM

I've got an Excel workbook with 2 imported SFDC reports that the end user needs to refresh at will. A number of functions need to run after the RefreshAll is complete to calculate, format, archive on so on. Based on some helpful postings on this site I've set a reference to the SFDC.xla to call the RefreshAll from within a module. My problem is that while the RefreshAll method is running, control is passed back to the function. I'm looking for a technique to:

Cause Excel to wait for the RefreshAll to complete before moving on
or
A way to test that the RefreshAll is complete
or
specify the order in which the reports refresh. I could add a 3rd bogus report to run last and use the sheetchange event to proceed. The order in which the reports are listed on the hidden worksheet "SalesForceReportData" seems to have no significance.

Any suggested techniques would be greatly appreciated.
I'm pretty much a VB, VBA guy with little to no coding in SOAP/web services so hooking into the SFDC_xla is ideal. I'd love to know what some of the other methods do or what the parameters are. Where we not intended to program with OfficeEdition?

thanks in advance
D Farns

Can you access the salesforce.com plug from VBA Macros? If so, what are the subroutine/function names in the project and what are their parameters...

I would like (from a macro):

1) Login to sfdc
2) Refresh ALL reports (or even use Refresh Selected Reports and select the reports by name)

Richard

Can you access the salesforce.com plug from VBA Macros? If so, what are the subroutine/function names in the project and what are their parameters...

I would like (from a macro):

1) Login to sfdc
2) Refresh ALL reports (or even use Refresh Selected Reports and select the reports by name)

Richard

Hi Ron,

in your util module you asked for suggestions as how to pass a localized date format. If you don't want to use WINAPI32 calls, then using DateSerial() and modifying the result is probably the easiest way to do it. This way, you don't have to worry about the order of years/months/days, 2- or 4-digit years, leading zeroes in days/months or separators, which can be any of the following /.'- (and maybe more). All you have to do is take the result and replace all numeric occurrences with their respective format characters, and leave everything else as is. This can be done inside a For-Loop with Mid$() function calls, with subsequent MID$() method calls or with the Replace() function. I prefer the latter, because it's easiest to understand and very straightforward, but other approaches may be faster or more appealing to the eye. Here's my suggestion:

Function typeToFormat(sfType As String)
    typeToFormat = "General" ' default
    Select Case sfType
    Case "date", "datetime"
        Dim dateTemp As String
      dateTemp = DateSerial(2134, 6, 5)
      dateTemp = Replace(dateTemp, "2134", "yyyy")
      dateTemp = Replace(dateTemp, "34", "yy")      'year may be 2 characters only
      dateTemp = Replace(dateTemp, "06", "mm")
      dateTemp = Replace(dateTemp, "6", "m")        'month format may not have leading zero
      dateTemp = Replace(dateTemp, "05", "dd")
      dateTemp = Replace(dateTemp, "5", "d")        'day format may not have leading zero
      If sfType = "datetime" Then
        dateTemp = dateTemp & " h:mm"
      End If
      typeToFormat = dateTemp
    'Case "date"
      'typeToFormat = "m/d/yyyy" ' wow, cant figure out how to pass a localized format, bummer
    'Case "datetime"
      'typeToFormat = "m/d/yyyy h:mm" ' 5.15
    Case "string", "picklist", "phone" ' , "textarea"
      typeToFormat = "@"
    Case "currency"
      typeToFormat = "$#,##0_);($#,##0)" ' format as currency, no cents (added in 5.15)
    End Select
   
End Function

Since VLookup() and HLookup() won't work with case-sensitive IDs, I currently have to transform them using some binary arithmetics in VBA code (thanks to Sean O'Conner for providing valuable insights):

Function GetID18(ByVal strID15 As String) As String
    Dim i%, j%, n%, c$
    For i = 1 To 3
        n = 0
        For j = 0 To 4
            n = n Or IIf(InStr(1, "ABCDEFGHIJKLMNOPQRSTUVWXYZ", _
                Mid$(strID15, i * 5 - j, 1), vbBinaryCompare) > 0, 2 ^ j, 0)
        Next j
   
        c = c & Mid$("AQIYEUM2CSK0GWO4BRJZFVN3DTL1HXP5", n + 1, 1)
    Next i
    GetID18 = strID15 & c
End Function

Is there any way of intercepting

I've used the SforceOfficeToolkit library in a VBA application that read data in Excel sheet and update fields in Salesforce. 

Field type string and integer are updated corretly but double updated doesn't work: this is the error message returned by the object SForceSession:  "java.lang.NumberFormatException: For input string:...."

this is my code:

    Dim sobj As SForceOfficeToolkitLib.SObject
       
    Set sobj = sf.CreateObject("Account")
       
    sobj.Item("ID").Value = "00130000005T3f7AAC"
    sobj.Item("NumberOfEmployees").Value = 20
    sobj.Item("doublefield__c").Value = 0,34         (for xample)
     sobj.Update

What is the problem?

Thanks in advance