VBA script that generates text file with a hex value

[amjohnson]

I've got a little script that will create a text file from data that has been inputted into a spreadsheet. Basically, you click a button and it produces a text file.


This is work fine expect I need to have it create something that is a hex value. When trying to do this multiple ways, all I'm seeing in the text file in the number 1. I'd assume that if it were a non-displayable hex character it wouldn't just show a 1.


Hex values I need to generate are '01' '02' '03' '04' '08' '13' '14'.

Desired output format is:

$_0123456789 where _ will be one of the above hex characters.


Current section of code attempting to handle this is here:

variable1 is defined as string

variable2 has been defined as integer and as byte (neither worked as expected)

value_X are simple strings "XYZ"

[pre]

Select Case variable1
Case value_1
variable2 = Hex(1)
Case value_2
variable2 = Hex(2)
Case value_3
variable2 = Hex(3)
Case value_4
variable2 = Hex(4)
Case value_5
variable2 = Hex(8)
Case value_6
variable2 = Hex(13)
Case value_7
variable2 = Hex(14)
End Select

[/pre]
I've also tried to do variable2 = &H1 etc. without luck.


Thank you in advance for any help you can offer.

 

[NARAYANK991]

Hi Johnson ,


It would help if you could copy + paste even one row of input data , and its converted output ( after addition of the hex value ; I am not able to understand your definition of HEX ; a string such as 01 is a string of two characters.


To label it as HEX is material only if you are starting off with a decimal value such as 113 , which you then convert to HEX form before outputting , so that instead of outputting the 3 characters 1 , 1 and 3 , you will output the two characters 7 and 1.


Again , suppose you are outputting the 3 characters B , E and E ; it really should not matter whether you are referring to the insect or the decimal number 3054.


If you are talking of non-printable ASCII characters , that is a different matter altogether ; are you ?


Narayan

 

[amjohnson]

Sorry for the confusion. Yes, I am referring to non-printable ASCII characters. Actually EBCDIC, but I think if I get the hex value correctly than it will convert for where I need to use it.


Input: (2 columns)

CMS 011332451A


In my program I'm just planning to match "CMS" with Hex"02" for example.


Desired Output: (1 line in text file)

$_011332451A where _ equals hex'02' or "Start of Text" non-printable character.


These are the characters that I want to print in the 2 position of the 12 character output

[pre]

Binary	        Oct	Dec	Hex	Abbr	Name
000 0001	001	1	01	SOH	Start of Header
000 0010	002	2	02	STX	Start of Text
000 0011	003	3	03	ETX	End of Text
000 0100	004	4	04	EOT	End of Transmission
000 1000	010	8	08	BS      Backspace
000 1101	015	13	0D	CR	Carriage return
000 1110	016	14	0E	SO	Shift Out

[/pre]
Let me know if this helps. Thank you.

 

[SirJB7]

Hi, amjohnson!


Give a look at this code:

-----

[pre]

Option Explicit

Sub CreateTextFileWithNonTextContent()
' constants
Const ksFileName = "<fullpath>AwfulFile.txt"
' declarations
Dim I As Integer, J As Integer, A As String
' start
I = FreeFile()
Open ksFileName For Output As #I
' process
A = ""
For J = 0 To 255
A = A & Chr(J)
Print #I, Chr(J)
Next J
Print #I, A
' end
Close #I
Beep
End Sub

[/pre]
-----


It uses the Chr() function to generate each character. Theoretically it should create a 257 records text file, with the 1st 256 of length 1 with each ASCII character and the last one of length 256 with all the characters. But it shifts 2 times splitting records after the following:

Chr(10), LF, Line feed

Chr(14), CR, Carriage return

that are the <end of line> control characters for Windows based text files.


That's to say you could embed any character in your file with those 2 exceptions.


Just advise if any issue.


Regards!