I NEED HELP To convert Excel Macro (VB) code to SQL statements...
I would usually copy the 3 Values (ID1, ID2 and ID3) into an excel worksheet and run the Macro which creates the "Output" (single digit between 0-9) and "OCR Code" (which is a concatenated value of ID1, ID2, ID3 and Output columns). Below are what these are:
1. ID1 can be anywhere from 3 digits upto 8 digits and also can start with a # or letter with a "-" as 2 character, therefore format is required in the OCR Code output.
2. ID2 is always a big number only, no characters, and prefixed with zeros to output a total of 11 digits.
3. ID3 is a mix of letters and is formatted to have an output of 5 characters.
4. Output column is generated by the code that runs in the Macro (VB) attached within the excel.
5. OCR Code - This is the final output which is a concatenated version of all the 4 columns of data.
ID1 ID2 ID3 Output OCR Code
14368 123262891 E0473 6 0 00014368 00123262891 E0473 6
8-10704535 123214785 E0473 7 8 10704535 00123214785 E0473 7
231640 123258733 T571 3 0 00231640 00123258733 1T571 3
Below is the Macro code attached to the excel worksheet. I don't know how to convert this into T-SQL (basically SQL statements) so that it works as if I had loaded the 3 values in a table and when the SQL runs, I get the OCR Code output.
Sub Check_Digits()
Dim iRow As Long
Dim strID1 As String
Dim strID2 As String
Dim strID3 As String
Dim TitleLetter As String
Dim ColumnLN As Long
Dim strDigit As Integer
Dim STRnUM As String
Dim i As Integer
Dim iEven As Integer
Dim iOdd As Integer
Dim iTotal As Integer
Dim strOneChar As String
Dim iTemp As Integer
Dim strID1OUT As String
Dim strID2OUT As String
Dim strID3OUT As String
Dim strID3OUT2 As String
strDigit = 0
iRow = 2: ColumnLN = 1
strID1 = UCase(Trim(CStr(Worksheets(1).Cells(iRow, ColumnLN))))
strID2 = UCase(Trim(CStr(Worksheets(1).Cells(iRow, ColumnLN + 1))))
strID3 = UCase(Trim(CStr(Worksheets(1).Cells(iRow, ColumnLN + 2))))
Do While (Trim(strID1) <> "")
iEven = 0: iOdd = 0: iTotal = 0: strOneChar = "": iTemp = 0
If Len(strID3) = 1 Then
STRnUM = Right(("0000000000" & Trim(Replace(strID1, "-", ""))), 10) & _
Right("00000000000" & strID2, 11) & _
Right("00000" & strID3, 5)
ElseIf Len(strID3) = 4 Then
STRnUM = Right(("0000000000" & Trim(Replace(strID1, "-", ""))), 10) & _
Right("00000000000" & strID2, 11) & _
Right("11111" & strID3, 5)
Else
STRnUM = Right(("0000000000" & Trim(Replace(strID1, "-", ""))), 10) & _
Right("00000000000" & strID2, 11) & _
Right("11111" & strID3, 5)
End If
Debug.Print STRnUM & " " & Len(STRnUM)
For i = Len(STRnUM) - 1 To 2 Step -2
strOneChar = Mid$(STRnUM, i, 1)
If IsNumeric(strOneChar) Then
iEven = iEven + CInt(strOneChar)
Else
Select Case UCase(strOneChar)
Case "A", "K", "U"
iEven = iEven + 0
Case "B", "L", "V"
iEven = iEven + 1
Case "C", "M", "W"
iEven = iEven + 2
Case "D", "N", "X"
iEven = iEven + 3
Case "E", "O", "Y"
iEven = iEven + 4
Case "F", "P", "Z"
iEven = iEven + 5
Case "G", "Q"
iEven = iEven + 6
Case "H", "R"
iEven = iEven + 7
Case "I", "S"
iEven = iEven + 8
Case "J", "T"
iEven = iEven + 9
End Select
End If
Debug.Print (strOneChar)
Next i
strOneChar = "": i = 0
For i = Len(STRnUM) To 1 Step -2
strOneChar = Mid$(STRnUM, i, 1)
If IsNumeric(strOneChar) Then
iTemp = CInt(strOneChar) * 2
If iTemp > 9 Then
iOdd = iOdd + (iTemp \ 10) + (iTemp - 10)
Else
iOdd = iOdd + iTemp
End If
Else
Select Case UCase(strOneChar)
Case "A", "K", "U"
iTemp = 0
Case "B", "L", "V"
iTemp = 1
Case "C", "M", "W"
iTemp = 2
Case "D", "N", "X"
iTemp = 3
Case "E", "O", "Y"
iTemp = 4
Case "F", "P", "Z"
iTemp = 5
Case "G", "Q"
iTemp = 6
Case "H", "R"
iTemp = 7
Case "I", "S"
iTemp = 8
Case "J", "T"
iTemp = 9
End Select
iTemp = iTemp * 2
If iTemp > 9 Then
iOdd = iOdd + (iTemp \ 10) + (iTemp - 10)
Else
iOdd = iOdd + iTemp
End If
End If
Debug.Print iTemp & " "; iOdd
Next i
iTotal = iEven + iOdd
If iTotal Mod 10 = 0 Then
CheckDigit = 0
Else
CheckDigit = 10 - (iTotal Mod 10)
End If
Sheet1.Cells(iRow, ColumnLN + 3) = CheckDigit
If InStr(1, strID1, "-") > 0 Then
strID1OUT = Right(("0000000000" & Trim(Replace(strID1, "-", " "))), 10)
Else
If InStr(1, strID1, "C") > 0 Then
strID1OUT = "C " & Right(("00000000" & Trim(strID1)), 8)
Else
If InStr(1, strID1, "S") > 0 Then
strID1OUT = "S " & Right(("00000000" & Trim(strID1)), 8)
Else
strID1OUT = "0 " & Right(("00000000" & Trim(strID1)), 8)
End If
End If
End If
strID2OUT = Right("00000000000" & strID2, 11)
If Len(strID3) = 1 Then
strID3OUT = Right("00000" & strID3, 5)
strID3OUT2 = Right("0000" & strID3, 5)
ElseIf Len(strID3) = 4 Then
strID3OUT = Right("11111" & strID3, 5)
strID3OUT2 = strID3
Else
strID3OUT = strID3
strID3OUT2 = strID3
End If
Sheet1.Cells(iRow, ColumnLN + 5) = strID1OUT & " " & strID2OUT & " " & strID3OUT & " " & CheckDigit
Sheet1.Cells(iRow, ColumnLN + 6) = strID1OUT & " " & strID2OUT & " " & strID3OUT2 & " " & CheckDigit
iRow = iRow + 1
strID1 = UCase(Trim(CStr(Worksheets(1).Cells(iRow, ColumnLN))))
strID2 = UCase(Trim(CStr(Worksheets(1).Cells(iRow, ColumnLN + 1))))
strID3 = UCase(Trim(CStr(Worksheets(1).Cells(iRow, ColumnLN + 2))))
Loop
Sheet1.Cells(1, ColumnLN + 3) = "Check_Digit"
Sheet1.Cells(1, ColumnLN + 5) = "Output"
MsgBox "Finished!", vbInformation + vbOKOnly
End Sub
What I have tried:
Tried to understand but could not .... HELP !!!
Submit an article or tip