Apex Code Development You need to sign in to do that
Don't have an account?
deepbluesea Excel PlugIn: transforming 15-digit to 18-digit case-insensitive IDs
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
<darn, I hit the submit button by mistake>
...the 15-digit IDs and transforming them to their case-insensitive versions BEFORE the are updated in Excel? I was hoping that the code would be open-source and could be modified accordingly, but I guess this is asking too much.
Does anybody know if there are plans to output 18-digit IDs?
Any advice is appreciated :-)
Erik Mittmeyer
Software AG
Germany
Erik,
The version of the connector on sforge is open source, and can therefore be modified. (The open source version does require Enterprise Edition to execute).
There are two functions in the "util" module which can help automate conversions:
Though you have an equivalent already,
FixID(InID) -- transforms an ID to the 18 character version.
For an entire column,
fixidcol() -- transforms all the ID's in the active column to 18 character versions
Scot
Scot,
extremely cool :-) Thanks for the hint.
Cheers, Erik
Scot,
you just made my day. I checked out and thoroughly tested Ron's open source version and have found that it's about 60 to 70% faster than mine, which can really make a difference when updating 100.000 rows. Then again, he thanked the community in general and YOU in particular in his comments.
Keep up the excellent work,
Erik
Hi guys,
I was wondering why my original version was so much slower than Ron's. So I removed the stupid IIf() function. Guess what? I've just hit the mark and beat my own GetID18() by 95:215 and even Ron's FixID() by 95:125. Not that it's a big deal, but considering my current 100,000 record updates...
Here's the new code, this time with some comments:
Public Function GetID18b(ByVal strID15 As String) As String
If Len(strID15) = 18 Then
GetID18b = strID15
Else
Dim i As Integer, j As Integer, n As Integer
Dim c As String, alpha As String, transcr As String
alpha$ = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
transcr$ = "AQIYEUM2CSK0GWO4BRJZFVN3DTL1HXP5"
'** NOTE: transcr$ is an alphabet transcription based on reversed 5-bit numerics,
'** with 00000=NUM("A")
'** Ex.: NUM("B")=1=00001-->10000=16=NUM("Q"); NUM("C")=2=00010-->01000=2=NUM("I")
'** devide 15-digit string into 3 chunks and investigate these individually
For i = 1 To 3
n = 0
'** check the case for each digit from right to left, store the result in binary
'** format (ex: 00001=1;00010=2;01000=8; etc.) and set all individual bits using
'** the OR operator (ex: 00010 OR 01000 --> 01010=10)
For j = 0 To 4
If InStr(1, alpha$, Mid$(strID15, i * 5 - j, 1), vbBinaryCompare) > 0 Then
n = n Or 2 ^ j '** set bit at position j+1 (from right)
End If
Next j
'** transcribe the right-to-left binary into it's left-to-right representation
'** and pull the resulting character in a single step
c = c & Mid$(transcr$, n + 1, 1)
Next i
GetID18b = strID15 & c
End If
End Function
Special thanks go to Scot and Sean for putting me on track, and to Ron for making such a wonderful tool.
Cheers, Erik
Hi RRES,
the sforce connector plug-in includes code that can create an 18-digit ID for an individual cell (function fixid()) or an entire column (sub fixidcol()), both located in the util module of the sforce_connector.xla file. If you don't use the sforce connector (which you actually should ;-), you can just as well use the code provided in this post:
- click on Extras-Macros-Visual Basic Editor
- create a new module in your xls file (if neccessary)
- paste the code into the new module (or into any existing module)
- insert a new column into your xl sheet. Create the following formula for the first cell in this column: "=GetID18([RefCell])". Replace "RefCell" with the appropriate cell reference, e.g. B3 (see warning below if you also use the sforce office edition excel pulg-in)
- copy the cell down as far as neccessary.
WARNING: be carefull with this approach when using the Excel plug-in provided by the sforce office edition to download reports. When the report's region is updated, formerly existing rows may be deleted or additional rows may be inserted, thereby breaking the cell references or having them point to the wrong row. In this case you should use the Excel "Indirect()" Method to access the correct cell, rather than the cell reference as described above. I am using german excel, so I don't know the exact english function names (I still don't understand why MS localized VBA in this manner :-(, but the cell reference in the new cells should be something like
"=GetID18(INDIRECT(ADDRESS(ROW(),COLUMN(B$2))))" (if column "B" has the 15-digit ID). Please look up the correct function names, if this example doesn't work
Please let me know if this helps.
deepbluesea