Sanjay
asked on
VBA code to convert hex to string
Can someone please help me in converting the 'arg1' field from hex to ascii or simple text in an Access database query. I have attached the Excel file that contains the data output from a MySQL ODBC database. I want to make sure no text is truncated in the conversion process and that all "carriage return" are eliminated. I have no idea how to accomplish this conversion.
ActionListtest.xls
ActionListtest.xls
Based on the data submitted, please post a sample of the expected output.
ASKER
Please see attached the expected output.
expectedoutput.xls
expectedoutput.xls
See here for a function that will do tghis:
http://bytes.com/topic/visual-basic-net/answers/381413-how-convert-hex-string
http://bytes.com/topic/visual-basic-net/answers/381413-how-convert-hex-string
ASKER
Thank you for the link. I am copying and pasting but certain portions of the code lines in my VB window appear in red...................????
ASKER
Public Function ToHexString(ByVal sText As String) As String
Dim arrBytes As Integer() = CharsToBytes(sText)
Dim sb As StringBuilder = New StringBuilder
For i As Integer = 0 To arrBytes.Length - 1
sb.Append(String.Format("{
Next
Return sb.ToString()
End Function
Everthing is in red in the vba window except for Public function...,Next, and end function........
ASKER
Boag2000
I've tried several examples from the link, but certain portions of the code get highlighted in red. I am not good at VB and so do not know what is going on.............sorry. Perhaps you can help me............?
I've tried several examples from the link, but certain portions of the code get highlighted in red. I am not good at VB and so do not know what is going on.............sorry. Perhaps you can help me............?
checking now...
ASKER
Thank you very much! I will follow up with you tomorrow. 10:08PM here in the U.S.......
First if you were able to get all the values in ExpectedOutput.xls, then why not use whatever method you used to create that???
Sorry, the code in the link was for vb.net
Researching VBA/VB6 functions now.
Researching VBA/VB6 functions now.
ASKER
Hi Boag2000:
I got this code from bytes.com. I do not get an error when I use the code as a function called in my query. However, I do not see any string or text either....
Sanj
I got this code from bytes.com. I do not get an error when I use the code as a function called in my query. However, I do not see any string or text either....
Sanj
Public Function Converthex(strHexString As String) As String
Dim intLenOfString As Integer
Dim intCounter As Integer
Dim strBuild As String
'Hex String must have a valid length, and it must be an even length
If Len(strHexString) = 0 Or Len(strHexString) Mod 2 <> 0 Then Exit Function
intLenOfString = Len(strHexString)
For intCounter = 1 To Len(strHexString)
If intCounter Mod 2 <> 0 Then 'need Hex pairs
'Retrieve the Value of the Hex Pair, then Convert to a Character,
'then Append to a Base String
strBuild = strBuild & Chr$(Val("&H" & Mid$(strHexString, intCounter, 2)))
End If
Next
End Function
ASKER
Hi Boag2000:
So I added an extra code line called Converthex = strBuild. It seems to be working. I realized I have an additional problem. Some of the hex text length are 255 characters (an odd number that cannot be converted to hex because conversion occurs in even pairs correct?). So what I want to do is modify the attached code to first check to see if the hex integer length is 255. If it is 255, I want to drop the first two characters and the last character. Then, I want to perform the conversion.....Could you help me with this modification?
sanj
So I added an extra code line called Converthex = strBuild. It seems to be working. I realized I have an additional problem. Some of the hex text length are 255 characters (an odd number that cannot be converted to hex because conversion occurs in even pairs correct?). So what I want to do is modify the attached code to first check to see if the hex integer length is 255. If it is 255, I want to drop the first two characters and the last character. Then, I want to perform the conversion.....Could you help me with this modification?
sanj
Public Function Converthex(strHexString As String) As String
Dim intLenOfString As Integer
Dim intCounter As Integer
Dim strBuild As String
'Hex String must have a valid length, and it must be an even length
If Len(strHexString) = 0 Or Len(strHexString) Mod 2 <> 0 Then Exit Function
intLenOfString = Len(strHexString)
For intCounter = 1 To Len(strHexString)
If intCounter Mod 2 <> 0 Then 'need Hex pairs
'Retrieve the Value of the Hex Pair, then Convert to a Character,
'then Append to a Base String
strBuild = strBuild & Chr$(Val("&H" & Mid$(strHexString, intCounter, 2)))
Converthex = strBuild
End If
Next
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your time.
;-)
Thanks for an interesting and thought provoking question
Thanks for an interesting and thought provoking question