Writing Hex character delimiters from an excel file

[geraldkentschmeer]

Maybe I'm trying to do too much with Excel, but I really like it. So here goes...

I'm receiving data in a 2010 excel file. I have to write this data into a data file using a Hex 1F field delimiter. Each record must end with a CRLF.

What I have done is using VBA, concatenated a record's data fields into as a single string containing the Hex character and put it into a cell. Here's a shortened, simple example:

Cells(1, 1).Value = field1 & Chr(31) & field2 & Chr(31) & field3 & Chr(31) & field4 & Chr(31)

Then I write that worksheet to a dos text file (to my understanding dos contains the CRLF at the end of each record).

When I open the resulting file with Notepad, it looks like the delimiters are there (visible squares).
When the third party vendor tries loading the file their system doesn't recognize the field delimiters.

Is it even possible to create a datafile with hex character delimiters from an excel file? If so, what might I be doing wrong?

Thanks in advance.

[protonLeah]

I tested your code and looked at the txt file, it seems ok.

[geraldkentschmeer]

I learn so much from observing fixes here. So... I went out and got a hex editor so that I could look for myself at an original data file and my data file. I do see a difference. I can't seem to find the "Post Reply" to be able to attach my findings, so I'll be typing them in for now.

Here is my code for the Header record:

Cells(1, 1).Value = "HDR" & Chr(31) & "2.0" & Chr(31) & _
DataSetID & Chr(31) & hDate & Chr(31) & _
hTime & Chr(31) & "R" & Chr(31) & Chr(31)

Saving the results as a DOS text file, here are the results via a hex editor:

FF FE 48 00 44 00 52 00 1F 00 32 00 2E 00 30 00 xxH.D.R...2...0. (NOTE: the "xx" are actually unprintable chars)
1F 00 31 00 30 00 30 00 30 00 37 00 36 00 31 00 ..1.0.0.0.7.6.1.
30 00 30 00 1F 00 32 00 30 00 31 00 33 00 30 00 0.0...2.0.1.3.0.
32 00 31 00 31 00 1F 00 31 00 32 00 33 00 31 00 2.1.1...1.2.3.1.


Here's what the original data file header record looks like:

48 44 52 1F 32 2E 30 1F 31 30 30 30 37 36 31 30 HDR.2.0.10007610
31 1F 32 30 31 31 30 38 32 36 1F 31 33 33 30 33 0.20110826.13303
30 1F 52 1F 20 1F 20 20 20 20 20 20 20 20 20 20 0.R. .

So for some reason, my code/process is:
1. creating 2 unprintable chars (hex FF and FE) before the first record, and
2. place a hex 00 between every char

So trying new things, I saved the results as a PRN file and wha laa, it looks like the original file via the hex editor. I will have the 3rd party vendor try this one.

I added this to the thread incase anyone else happens to end up with the same issues as me.