VBA code to convert hex to string

sxxgupta
sxxgupta used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Based on the data submitted, please post a sample of the expected output.

Author

Commented:
Please see attached the expected output.
expectedoutput.xls
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Thank you for the link.  I am copying and pasting but certain portions of the code lines in my VB window appear in red...................????

Author

Commented:

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("{0:x2}", Hex(arrBytes(i))))
Next
Return sb.ToString()
End Function
Everthing is in red in the vba window except for Public function...,Next, and end function........

Author

Commented:
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............?
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
checking now...

Author

Commented:
Thank you very much!  I will follow up with you tomorrow.  10:08PM here in the U.S.......
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
First if you were able to get all the values in ExpectedOutput.xls, then why not use whatever method you used to create that???
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Sorry, the code in the link was for vb.net

Researching VBA/VB6 functions now.

Author

Commented:
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

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

Open in new window

Author

Commented:
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
 

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

Open in new window

MIS Liason
Most Valuable Expert 2012
Commented:
Yes, all functions I found that do this are basically the same.

I used this:
See Query, the formula in the Calculated field of the query, and the function.
Access-EEQ24767115-ConvertHexToT.mdb

Author

Commented:
Thanks for your time.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
;-)

Thanks for an interesting and thought provoking question

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial