I need to import text files into Excel without losing special characters.
I've tried several methods, but each time Excel imports in the file, ignoring
those characters. The following is an example line, but what you can't see
are the 6 special characters which appear between the $$158 and the 1 8!

$$158 1 8 4.50 1.0000 0.8000 3.0010 1.5740

I know they are there, however since I opened the document using Word,
which displays them as a y with 2 dots above them.

My Excel VBA code needs to import these characters so that it doesn't get
lost when extracting the data using MID(,,,) function. The text file were
generated using old FORTRAN programs, and there are thousands of them...my
VBA routines need to access these files in order to modernize our system.

Examples of what I've tried (all of these ignore the y characters)

Workbooks.OpenText Filename:=fname, Origin:=437, _
StartRow:=1, dataType:=xlFixedWidth, FieldInfo:=Array(0, 2)

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fname,
Destination:=Cells(2, Col))

Open FName For Input Access Read As #1
While Not EOF(1)
Line Input #1, WholeLine
Cells(RowNdx, ColNdx).Value = WholeLine
RowNdx = RowNdx + 1
Wend
Close #1

I would upload an example file showing the characters if someone tells me
how. I would also tell you what the characters are, again, if someone tells
me how.

Thanks,
Tony

put your string in cell A1. Then in B1 or another cell in the first row put
in this formula

=CODE(MID($A$1,ROW(),1))
Assume the above formula is in B1
in C1:
=CHAR(B1)

now select B1:C1 and drag fill down until the formula starts returning
#Value errors.

The only thing between the characters in your post are ascii code 32 which
is a space.

Possibly they didn't get carried forward in the email.

--
Regards,
Tom Ogilvy



"T_o_n_y" wrote in message
...
I need to import text files into Excel without losing special characters.
I've tried several methods, but each time Excel imports in the file,
ignoring
those characters. The following is an example line, but what you can't
see
are the 6 special characters which appear between the $$158 and the 1 8!

$$158 1 8 4.50 1.0000 0.8000 3.0010 1.5740

I know they are there, however since I opened the document using Word,
which displays them as a y with 2 dots above them.

My Excel VBA code needs to import these characters so that it doesn't get
lost when extracting the data using MID(,,,) function. The text file were
generated using old FORTRAN programs, and there are thousands of them...my
VBA routines need to access these files in order to modernize our system.

Examples of what I've tried (all of these ignore the y characters)

Workbooks.OpenText Filename:=fname, Origin:=437, _
StartRow:=1, dataType:=xlFixedWidth, FieldInfo:=Array(0, 2)

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fname,
Destination:=Cells(2, Col))

Open FName For Input Access Read As #1
While Not EOF(1)
Line Input #1, WholeLine
Cells(RowNdx, ColNdx).Value = WholeLine
RowNdx = RowNdx + 1
Wend
Close #1

I would upload an example file showing the characters if someone tells me
how. I would also tell you what the characters are, again, if someone
tells
me how.

Thanks,
Tony

Tom,

Thank you for your reply. I followed your procedure but only got four "32"
s in that blank section; that is, there are only 4 spaces there. This
confirms what I've suspected, namely, that Excel is simply not importing
those characters. I've also tried using C. Pearson's Cell View Add-in with
the same result (http://www.cpearson.com/excel/CellView.htm).

As you point out, the characters also get stripped when I cut and paste into
this forum. Therefore, I've emailed you separately the file I referred to as
an attachment (it's a text document called W158.DAT) sent from myother_acct.
If I knew how to post it to this forum, I would.

I appreaciate your help...this is a frustrating problem for me. Is there a
way to import the text file character by character?

-Tony



"Tom Ogilvy" wrote:

put your string in cell A1. Then in B1 or another cell in the first row put
in this formula

=CODE(MID($A$1,ROW(),1))
Assume the above formula is in B1
in C1:
=CHAR(B1)

now select B1:C1 and drag fill down until the formula starts returning
#Value errors.

The only thing between the characters in your post are ascii code 32 which
is a space.

Possibly they didn't get carried forward in the email.

--
Regards,
Tom Ogilvy



"T_o_n_y" wrote in message
...
I need to import text files into Excel without losing special characters.
I've tried several methods, but each time Excel imports in the file,
ignoring
those characters. The following is an example line, but what you can't
see
are the 6 special characters which appear between the $$158 and the 1 8!

$$158 1 8 4.50 1.0000 0.8000 3.0010 1.5740

I know they are there, however since I opened the document using Word,
which displays them as a y with 2 dots above them.

My Excel VBA code needs to import these characters so that it doesn't get
lost when extracting the data using MID(,,,) function. The text file were
generated using old FORTRAN programs, and there are thousands of them...my
VBA routines need to access these files in order to modernize our system.

Examples of what I've tried (all of these ignore the y characters)

Workbooks.OpenText Filename:=fname, Origin:=437, _
StartRow:=1, dataType:=xlFixedWidth, FieldInfo:=Array(0, 2)

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fname,
Destination:=Cells(2, Col))

Open FName For Input Access Read As #1
While Not EOF(1)
Line Input #1, WholeLine
Cells(RowNdx, ColNdx).Value = WholeLine
RowNdx = RowNdx + 1
Wend
Close #1

I would upload an example file showing the characters if someone tells me
how. I would also tell you what the characters are, again, if someone
tells
me how.

Thanks,
Tony

put this in a workbook. Change the path to point to your file:

Sub ReadStraightTextFile()
Dim strTest As String
Dim bytArray() As Byte
Dim intcount As Integer
Dim col As Long
Open "E:\Data1\W158.DAT" For Input As #1
col = 0
Do While Not EOF(1)
Line Input #1, strTest
col = col + 1
bytArray = strTest
i = 0
For intcount = LBound(bytArray) To UBound(bytArray)
i = i + 1
Cells(i, col) = bytArray(intcount) & " = " &
Chr(bytArray(intcount))
Next

Loop 'Close the file

Close #1
End Sub


Have blank sheet as the activesheet. Run the macro.

It appears to me that the file is UNICODE. unlike an ascii file that has
one byte per character, a unicode file has two bytes per character.

there are 8 bits to a byte, so an ascii file can have 8^2 = 256
different/unique character codes. In a unicode file, 2 bytes is 16 bits,
so 2^16 = 65536 possible unique characters.

I didn't see any actual characters that couldn't be represented by Ascii, so
you could read every Odd character .


It appears that opening it in Excel automatically converts it to Ascii, so
you haven't lost any information, but if you want to edit it and write it
back out, you would need to save it as Unicode Text. I know that is an
option in at least xl2000 and I assume later.


--
Regards,
Tom Ogilvy

"T_o_n_y" wrote in message
...
Tom,

Thank you for your reply. I followed your procedure but only got four
"32"
s in that blank section; that is, there are only 4 spaces there. This
confirms what I've suspected, namely, that Excel is simply not importing
those characters. I've also tried using C. Pearson's Cell View Add-in
with
the same result (http://www.cpearson.com/excel/CellView.htm).

As you point out, the characters also get stripped when I cut and paste
into
this forum. Therefore, I've emailed you separately the file I referred to
as
an attachment (it's a text document called W158.DAT) sent from
myother_acct.
If I knew how to post it to this forum, I would.

I appreaciate your help...this is a frustrating problem for me. Is there
a
way to import the text file character by character?

-Tony



"Tom Ogilvy" wrote:

put your string in cell A1. Then in B1 or another cell in the first row
put
in this formula

=CODE(MID($A$1,ROW(),1))
Assume the above formula is in B1
in C1:
=CHAR(B1)

now select B1:C1 and drag fill down until the formula starts returning
#Value errors.

The only thing between the characters in your post are ascii code 32
which
is a space.

Possibly they didn't get carried forward in the email.

--
Regards,
Tom Ogilvy



"T_o_n_y" wrote in message
...
I need to import text files into Excel without losing special
characters.
I've tried several methods, but each time Excel imports in the file,
ignoring
those characters. The following is an example line, but what you can't
see
are the 6 special characters which appear between the $$158 and the 1
8!

$$158 1 8 4.50 1.0000 0.8000 3.0010 1.5740

I know they are there, however since I opened the document using Word,
which displays them as a y with 2 dots above them.

My Excel VBA code needs to import these characters so that it doesn't
get
lost when extracting the data using MID(,,,) function. The text file
were
generated using old FORTRAN programs, and there are thousands of
them...my
VBA routines need to access these files in order to modernize our
system.

Examples of what I've tried (all of these ignore the y characters)

Workbooks.OpenText Filename:=fname, Origin:=437, _
StartRow:=1, dataType:=xlFixedWidth, FieldInfo:=Array(0, 2)

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fname,
Destination:=Cells(2, Col))

Open FName For Input Access Read As #1
While Not EOF(1)
Line Input #1, WholeLine
Cells(RowNdx, ColNdx).Value = WholeLine
RowNdx = RowNdx + 1
Wend
Close #1

I would upload an example file showing the characters if someone tells
me
how. I would also tell you what the characters are, again, if someone
tells
me how.

Thanks,
Tony

I tried your macro, but unfortunately Excel still did not import the special
characters. Recall that there are 6 special characters between the $$158 and
the 1 8 in the first line of the file:
$$158 1 8 4.50 1.0000 0.8000 3.0010 1.5740

For that section, the output from your macro looked like this:
36 = $
0 =
36 = $
0 =
49 = 1
0 =
53 = 5
0 =
56 = 8
0 =
32 =
0 =
32 =
0 =
32 =
0 =
32 =
0 =
49 = 1
0 =
32 =
0 =
32 =
0 =
56 = 8

In other words, the 6 characters got stripped away again so that all you see
are the 2 spaces which appear on either side of the 6 special characters.

The only way I've found for Excel to even recognize that those characters
exist is to use the "Delimited" option during text import and specify
"spaces" as the delimiting character with the "Treat consecutive delimiters
as one" feature unchecked. Unfortunately, that method of importing would
mean a huge rework of my existing code.

I spent another few hours trying to research the UNICODE possibilty you
mentioned, but still was unable to come up with anything.

At a loss...
-Tony

"Tom Ogilvy" wrote:

put this in a workbook. Change the path to point to your file:

Sub ReadStraightTextFile()
Dim strTest As String
Dim bytArray() As Byte
Dim intcount As Integer
Dim col As Long
Open "E:\Data1\W158.DAT" For Input As #1
col = 0
Do While Not EOF(1)
Line Input #1, strTest
col = col + 1
bytArray = strTest
i = 0
For intcount = LBound(bytArray) To UBound(bytArray)
i = i + 1
Cells(i, col) = bytArray(intcount) & " = " &
Chr(bytArray(intcount))
Next

Loop 'Close the file

Close #1
End Sub


Have blank sheet as the activesheet. Run the macro.

It appears to me that the file is UNICODE. unlike an ascii file that has
one byte per character, a unicode file has two bytes per character.

there are 8 bits to a byte, so an ascii file can have 8^2 = 256
different/unique character codes. In a unicode file, 2 bytes is 16 bits,
so 2^16 = 65536 possible unique characters.

I didn't see any actual characters that couldn't be represented by Ascii, so
you could read every Odd character .


It appears that opening it in Excel automatically converts it to Ascii, so
you haven't lost any information, but if you want to edit it and write it
back out, you would need to save it as Unicode Text. I know that is an
option in at least xl2000 and I assume later.


--
Regards,
Tom Ogilvy

"T_o_n_y" wrote in message
...
Tom,

Thank you for your reply. I followed your procedure but only got four
"32"
s in that blank section; that is, there are only 4 spaces there. This
confirms what I've suspected, namely, that Excel is simply not importing
those characters. I've also tried using C. Pearson's Cell View Add-in
with
the same result (http://www.cpearson.com/excel/CellView.htm).

As you point out, the characters also get stripped when I cut and paste
into
this forum. Therefore, I've emailed you separately the file I referred to
as
an attachment (it's a text document called W158.DAT) sent from
myother_acct.
If I knew how to post it to this forum, I would.

I appreaciate your help...this is a frustrating problem for me. Is there
a
way to import the text file character by character?

-Tony



"Tom Ogilvy" wrote:

put your string in cell A1. Then in B1 or another cell in the first row
put
in this formula

=CODE(MID($A$1,ROW(),1))
Assume the above formula is in B1
in C1:
=CHAR(B1)

now select B1:C1 and drag fill down until the formula starts returning
#Value errors.

The only thing between the characters in your post are ascii code 32
which
is a space.

Possibly they didn't get carried forward in the email.

--
Regards,
Tom Ogilvy



"T_o_n_y" wrote in message
...
I need to import text files into Excel without losing special
characters.
I've tried several methods, but each time Excel imports in the file,
ignoring
those characters. The following is an example line, but what you can't
see
are the 6 special characters which appear between the $$158 and the 1
8!

$$158 1 8 4.50 1.0000 0.8000 3.0010 1.5740

I know they are there, however since I opened the document using Word,
which displays them as a y with 2 dots above them.

My Excel VBA code needs to import these characters so that it doesn't
get
lost when extracting the data using MID(,,,) function. The text file
were
generated using old FORTRAN programs, and there are thousands of
them...my
VBA routines need to access these files in order to modernize our
system.

Examples of what I've tried (all of these ignore the y characters)

Workbooks.OpenText Filename:=fname, Origin:=437, _
StartRow:=1, dataType:=xlFixedWidth, FieldInfo:=Array(0, 2)

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fname,
Destination:=Cells(2, Col))

Open FName For Input Access Read As #1
While Not EOF(1)
Line Input #1, WholeLine
Cells(RowNdx, ColNdx).Value = WholeLine
RowNdx = RowNdx + 1
Wend
Close #1

I would upload an example file showing the characters if someone tells
me
how. I would also tell you what the characters are, again, if someone
tells
me how.

Thanks,
Tony

Tony,

Please send your file directly to me via an attachment. I think I can
help.

T_o_n_y wrote:
I tried your macro, but unfortunately Excel still did not import the special
characters. Recall that there are 6 special characters between the $$158 and
the 1 8 in the first line of the file:
$$158 1 8 4.50 1.0000 0.8000 3.0010 1.5740

For that section, the output from your macro looked like this:
36 = $
0 =
36 = $
0 =
49 = 1
0 =
53 = 5
0 =
56 = 8
0 =
32 =
0 =
32 =
0 =
32 =
0 =
32 =
0 =
49 = 1
0 =
32 =
0 =
32 =
0 =
56 = 8

In other words, the 6 characters got stripped away again so that all you see
are the 2 spaces which appear on either side of the 6 special characters.

The only way I've found for Excel to even recognize that those characters
exist is to use the "Delimited" option during text import and specify
"spaces" as the delimiting character with the "Treat consecutive delimiters
as one" feature unchecked. Unfortunately, that method of importing would
mean a huge rework of my existing code.

I spent another few hours trying to research the UNICODE possibilty you
mentioned, but still was unable to come up with anything.

At a loss...
-Tony

"Tom Ogilvy" wrote:

put this in a workbook. Change the path to point to your file:

Sub ReadStraightTextFile()
Dim strTest As String
Dim bytArray() As Byte
Dim intcount As Integer
Dim col As Long
Open "E:\Data1\W158.DAT" For Input As #1
col = 0
Do While Not EOF(1)
Line Input #1, strTest
col = col + 1
bytArray = strTest
i = 0
For intcount = LBound(bytArray) To UBound(bytArray)
i = i + 1
Cells(i, col) = bytArray(intcount) & " = " &
Chr(bytArray(intcount))
Next

Loop 'Close the file

Close #1
End Sub


Have blank sheet as the activesheet. Run the macro.

It appears to me that the file is UNICODE. unlike an ascii file that has
one byte per character, a unicode file has two bytes per character.

there are 8 bits to a byte, so an ascii file can have 8^2 = 256
different/unique character codes. In a unicode file, 2 bytes is 16 bits,
so 2^16 = 65536 possible unique characters.

I didn't see any actual characters that couldn't be represented by Ascii, so
you could read every Odd character .


It appears that opening it in Excel automatically converts it to Ascii, so
you haven't lost any information, but if you want to edit it and write it
back out, you would need to save it as Unicode Text. I know that is an
option in at least xl2000 and I assume later.


--
Regards,
Tom Ogilvy

"T_o_n_y" wrote in message
...
Tom,

Thank you for your reply. I followed your procedure but only got four
"32"
s in that blank section; that is, there are only 4 spaces there. This
confirms what I've suspected, namely, that Excel is simply not importing
those characters. I've also tried using C. Pearson's Cell View Add-in
with
the same result (http://www.cpearson.com/excel/CellView.htm).

As you point out, the characters also get stripped when I cut and paste
into
this forum. Therefore, I've emailed you separately the file I referred to
as
an attachment (it's a text document called W158.DAT) sent from
myother_acct.
If I knew how to post it to this forum, I would.

I appreaciate your help...this is a frustrating problem for me. Is there
a
way to import the text file character by character?

-Tony



"Tom Ogilvy" wrote:

put your string in cell A1. Then in B1 or another cell in the first row
put
in this formula

=CODE(MID($A$1,ROW(),1))
Assume the above formula is in B1
in C1:
=CHAR(B1)

now select B1:C1 and drag fill down until the formula starts returning
#Value errors.

The only thing between the characters in your post are ascii code 32
which
is a space.

Possibly they didn't get carried forward in the email.

--
Regards,
Tom Ogilvy



"T_o_n_y" wrote in message
...
I need to import text files into Excel without losing special
characters.
I've tried several methods, but each time Excel imports in the file,
ignoring
those characters. The following is an example line, but what you can't
see
are the 6 special characters which appear between the $$158 and the 1
8!

$$158 1 8 4.50 1.0000 0.8000 3.0010 1.5740

I know they are there, however since I opened the document using Word,
which displays them as a y with 2 dots above them.

My Excel VBA code needs to import these characters so that it doesn't
get
lost when extracting the data using MID(,,,) function. The text file
were
generated using old FORTRAN programs, and there are thousands of
them...my
VBA routines need to access these files in order to modernize our
system.

Examples of what I've tried (all of these ignore the y characters)

Workbooks.OpenText Filename:=fname, Origin:=437, _
StartRow:=1, dataType:=xlFixedWidth, FieldInfo:=Array(0, 2)

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fname,
Destination:=Cells(2, Col))

Open FName For Input Access Read As #1
While Not EOF(1)
Line Input #1, WholeLine
Cells(RowNdx, ColNdx).Value = WholeLine
RowNdx = RowNdx + 1
Wend
Close #1

I would upload an example file showing the characters if someone tells
me
how. I would also tell you what the characters are, again, if someone
tells
me how.

Thanks,
Tony

Guess it was a waste of time trying to explain it to you. Did you bother to
read it?

--
Regards,
Tom Ogilvy


"T_o_n_y" wrote in message
...
I tried your macro, but unfortunately Excel still did not import the
special
characters. Recall that there are 6 special characters between the $$158
and
the 1 8 in the first line of the file:
$$158 1 8 4.50 1.0000 0.8000 3.0010 1.5740

For that section, the output from your macro looked like this:
36 = $
0 =
36 = $
0 =
49 = 1
0 =
53 = 5
0 =
56 = 8
0 =
32 =
0 =
32 =
0 =
32 =
0 =
32 =
0 =
49 = 1
0 =
32 =
0 =
32 =
0 =
56 = 8

In other words, the 6 characters got stripped away again so that all you
see
are the 2 spaces which appear on either side of the 6 special characters.

The only way I've found for Excel to even recognize that those characters
exist is to use the "Delimited" option during text import and specify
"spaces" as the delimiting character with the "Treat consecutive
delimiters
as one" feature unchecked. Unfortunately, that method of importing would
mean a huge rework of my existing code.

I spent another few hours trying to research the UNICODE possibilty you
mentioned, but still was unable to come up with anything.

At a loss...
-Tony

"Tom Ogilvy" wrote:

put this in a workbook. Change the path to point to your file:

Sub ReadStraightTextFile()
Dim strTest As String
Dim bytArray() As Byte
Dim intcount As Integer
Dim col As Long
Open "E:\Data1\W158.DAT" For Input As #1
col = 0
Do While Not EOF(1)
Line Input #1, strTest
col = col + 1
bytArray = strTest
i = 0
For intcount = LBound(bytArray) To UBound(bytArray)
i = i + 1
Cells(i, col) = bytArray(intcount) & " = " &
Chr(bytArray(intcount))
Next

Loop 'Close the file

Close #1
End Sub


Have blank sheet as the activesheet. Run the macro.

It appears to me that the file is UNICODE. unlike an ascii file that has
one byte per character, a unicode file has two bytes per character.

there are 8 bits to a byte, so an ascii file can have 8^2 = 256
different/unique character codes. In a unicode file, 2 bytes is 16
bits,
so 2^16 = 65536 possible unique characters.

I didn't see any actual characters that couldn't be represented by Ascii,
so
you could read every Odd character .


It appears that opening it in Excel automatically converts it to Ascii,
so
you haven't lost any information, but if you want to edit it and write it
back out, you would need to save it as Unicode Text. I know that is an
option in at least xl2000 and I assume later.


--
Regards,
Tom Ogilvy

"T_o_n_y" wrote in message
...
Tom,

Thank you for your reply. I followed your procedure but only got four
"32"
s in that blank section; that is, there are only 4 spaces there. This
confirms what I've suspected, namely, that Excel is simply not
importing
those characters. I've also tried using C. Pearson's Cell View Add-in
with
the same result (http://www.cpearson.com/excel/CellView.htm).

As you point out, the characters also get stripped when I cut and paste
into
this forum. Therefore, I've emailed you separately the file I referred
to
as
an attachment (it's a text document called W158.DAT) sent from
myother_acct.
If I knew how to post it to this forum, I would.

I appreaciate your help...this is a frustrating problem for me. Is
there
a
way to import the text file character by character?

-Tony



"Tom Ogilvy" wrote:

put your string in cell A1. Then in B1 or another cell in the first
row
put
in this formula

=CODE(MID($A$1,ROW(),1))
Assume the above formula is in B1
in C1:
=CHAR(B1)

now select B1:C1 and drag fill down until the formula starts returning
#Value errors.

The only thing between the characters in your post are ascii code 32
which
is a space.

Possibly they didn't get carried forward in the email.

--
Regards,
Tom Ogilvy



"T_o_n_y" wrote in message
...
I need to import text files into Excel without losing special
characters.
I've tried several methods, but each time Excel imports in the file,
ignoring
those characters. The following is an example line, but what you
can't
see
are the 6 special characters which appear between the $$158 and the
1
8!

$$158 1 8 4.50 1.0000 0.8000 3.0010
1.5740

I know they are there, however since I opened the document using
Word,
which displays them as a y with 2 dots above them.

My Excel VBA code needs to import these characters so that it
doesn't
get
lost when extracting the data using MID(,,,) function. The text
file
were
generated using old FORTRAN programs, and there are thousands of
them...my
VBA routines need to access these files in order to modernize our
system.

Examples of what I've tried (all of these ignore the y characters)

Workbooks.OpenText Filename:=fname, Origin:=437, _
StartRow:=1, dataType:=xlFixedWidth, FieldInfo:=Array(0, 2)

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fname,
Destination:=Cells(2, Col))

Open FName For Input Access Read As #1
While Not EOF(1)
Line Input #1, WholeLine
Cells(RowNdx, ColNdx).Value = WholeLine
RowNdx = RowNdx + 1
Wend
Close #1

I would upload an example file showing the characters if someone
tells
me
how. I would also tell you what the characters are, again, if
someone
tells
me how.

Thanks,
Tony

Tony,
Are the columns fixed width, as the parameter dataType:=xlFixedWidth
suggests ?
Are these special characters, ASCII value 255 ?

As for uploading a samle of the data,
http://savefile.com/

NickHK

"T_o_n_y" wrote in message
...
I need to import text files into Excel without losing special characters.
I've tried several methods, but each time Excel imports in the file,
ignoring
those characters. The following is an example line, but what you can't
see
are the 6 special characters which appear between the $$158 and the 1 8!

$$158 1 8 4.50 1.0000 0.8000 3.0010 1.5740

I know they are there, however since I opened the document using Word,
which displays them as a y with 2 dots above them.

My Excel VBA code needs to import these characters so that it doesn't get
lost when extracting the data using MID(,,,) function. The text file were
generated using old FORTRAN programs, and there are thousands of them...my
VBA routines need to access these files in order to modernize our system.

Examples of what I've tried (all of these ignore the y characters)

Workbooks.OpenText Filename:=fname, Origin:=437, _
StartRow:=1, dataType:=xlFixedWidth, FieldInfo:=Array(0, 2)

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fname,
Destination:=Cells(2, Col))

Open FName For Input Access Read As #1
While Not EOF(1)
Line Input #1, WholeLine
Cells(RowNdx, ColNdx).Value = WholeLine
RowNdx = RowNdx + 1
Wend
Close #1

I would upload an example file showing the characters if someone tells me
how. I would also tell you what the characters are, again, if someone
tells
me how.

Thanks,
Tony

Thank you, Nick, for showing me how to upload the file. Here is the link for
the file:
http://www.savefile.com/files/150039
To answer your question, the text file data is not fixed with but I've tried
fixed width as well as delimited...nothing works for me...the characters are
always stripped away.

"NickHK" wrote:

Tony,
Are the columns fixed width, as the parameter dataType:=xlFixedWidth
suggests ?
Are these special characters, ASCII value 255 ?

As for uploading a samle of the data,
http://savefile.com/

NickHK

"T_o_n_y" wrote in message
...
I need to import text files into Excel without losing special characters.
I've tried several methods, but each time Excel imports in the file,
ignoring
those characters. The following is an example line, but what you can't
see
are the 6 special characters which appear between the $$158 and the 1 8!

$$158 1 8 4.50 1.0000 0.8000 3.0010 1.5740

I know they are there, however since I opened the document using Word,
which displays them as a y with 2 dots above them.

My Excel VBA code needs to import these characters so that it doesn't get
lost when extracting the data using MID(,,,) function. The text file were
generated using old FORTRAN programs, and there are thousands of them...my
VBA routines need to access these files in order to modernize our system.

Examples of what I've tried (all of these ignore the y characters)

Workbooks.OpenText Filename:=fname, Origin:=437, _
StartRow:=1, dataType:=xlFixedWidth, FieldInfo:=Array(0, 2)

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fname,
Destination:=Cells(2, Col))

Open FName For Input Access Read As #1
While Not EOF(1)
Line Input #1, WholeLine
Cells(RowNdx, ColNdx).Value = WholeLine
RowNdx = RowNdx + 1
Wend
Close #1

I would upload an example file showing the characters if someone tells me
how. I would also tell you what the characters are, again, if someone
tells
me how.

Thanks,
Tony

Tony,
Looking the first 16 bytes of your file in a Hex editor, you have:
24 24 31 35 38 20 20 00 00 00 00 00 00 20 20 31

The "20"=decimal 32, which is a normal space.
The "00" are = vbNullChar and are not printable, I get a "" in Word. Not
sure where your "y with 2 dots above them" came from.
As such, do they actually mean anything ?

Your data seems such a mix of formats, I doubt Excel would be able to make
much sense of it.

You would be better writing your own parsing routine :
<Air Code
Open "the file" For Input
Do until EndOfFile
LineInput ToAVariable
Call DecideWhatThisLineMeans
'Process the data
Loop
Close file

NickHK

"T_o_n_y" wrote in message
...
Thank you, Nick, for showing me how to upload the file. Here is the link
for
the file:
http://www.savefile.com/files/150039
To answer your question, the text file data is not fixed with but I've
tried
fixed width as well as delimited...nothing works for me...the characters
are
always stripped away.

"NickHK" wrote:

Tony,
Are the columns fixed width, as the parameter dataType:=xlFixedWidth
suggests ?
Are these special characters, ASCII value 255 ?

As for uploading a samle of the data,
http://savefile.com/

NickHK

"T_o_n_y" wrote in message
...
I need to import text files into Excel without losing special
characters.
I've tried several methods, but each time Excel imports in the file,
ignoring
those characters. The following is an example line, but what you
can't
see
are the 6 special characters which appear between the $$158 and the 1
8!

$$158 1 8 4.50 1.0000 0.8000 3.0010 1.5740

I know they are there, however since I opened the document using Word,
which displays them as a y with 2 dots above them.

My Excel VBA code needs to import these characters so that it doesn't
get
lost when extracting the data using MID(,,,) function. The text file
were
generated using old FORTRAN programs, and there are thousands of
them...my
VBA routines need to access these files in order to modernize our
system.

Examples of what I've tried (all of these ignore the y characters)

Workbooks.OpenText Filename:=fname, Origin:=437, _
StartRow:=1, dataType:=xlFixedWidth, FieldInfo:=Array(0, 2)

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fname,
Destination:=Cells(2, Col))

Open FName For Input Access Read As #1
While Not EOF(1)
Line Input #1, WholeLine
Cells(RowNdx, ColNdx).Value = WholeLine
RowNdx = RowNdx + 1
Wend
Close #1

I would upload an example file showing the characters if someone tells
me
how. I would also tell you what the characters are, again, if someone
tells
me how.

Thanks,
Tony

Thank you for showing me how to upload the file.

Here is the link:
http://www.savefile.com/files/150039

To answer your question, the files are not fixed width, although I have
tried that option my attempts to import. No matter what I try, fixed width,
delimited, etc... Excel always seems to strip those characters away and all
I'm left with are the spaces around the special characters...but as I said,
my code needs to count those special characters in order for the string
manipulations to properly gather data.

Thanks again,
Tony
"NickHK" wrote:

Tony,
Are the columns fixed width, as the parameter dataType:=xlFixedWidth
suggests ?
Are these special characters, ASCII value 255 ?

As for uploading a samle of the data,
http://savefile.com/

NickHK

"T_o_n_y" wrote in message
...
I need to import text files into Excel without losing special characters.
I've tried several methods, but each time Excel imports in the file,
ignoring
those characters. The following is an example line, but what you can't
see
are the 6 special characters which appear between the $$158 and the 1 8!

$$158 1 8 4.50 1.0000 0.8000 3.0010 1.5740

I know they are there, however since I opened the document using Word,
which displays them as a y with 2 dots above them.

My Excel VBA code needs to import these characters so that it doesn't get
lost when extracting the data using MID(,,,) function. The text file were
generated using old FORTRAN programs, and there are thousands of them...my
VBA routines need to access these files in order to modernize our system.

Examples of what I've tried (all of these ignore the y characters)

Workbooks.OpenText Filename:=fname, Origin:=437, _
StartRow:=1, dataType:=xlFixedWidth, FieldInfo:=Array(0, 2)

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fname,
Destination:=Cells(2, Col))

Open FName For Input Access Read As #1
While Not EOF(1)
Line Input #1, WholeLine
Cells(RowNdx, ColNdx).Value = WholeLine
RowNdx = RowNdx + 1
Wend
Close #1

I would upload an example file showing the characters if someone tells me
how. I would also tell you what the characters are, again, if someone
tells
me how.

Thanks,
Tony