Converting tab table to array: vbCr problems

[christophercbrewster via OfficeKB.com]

I need to pass information from one thing to another as a tabbed table in a
TXT file, then convert that to an array. I have it working, but the vbCr
character caused (IMHO) more trouble than it should have:

1. I used SPLIT to convert the file's text content to a 1-D array, each cell
being a row of the table. Since the delimiter character is vbCr, I would
expect the array cells NOT to include the character, but they do. I tried
REPLACE(array(i), vbCr, "") in various places but couldn't get rid of it.

2. In a loop I used SPLIT again on each row cell, using vbTab as the
delimiter. The tab dropped out of the array, but of course the vbCr's were
still there. To make this worse, the array including vbCr's is treated as an
error-- any statement referring to it fails.

3. I'd expect the vbCr's to appear at the end of the last cell in each row,
but they're at the start of the first cell of each row-- EXCEPT the first row.
So I added code to find the length of each first cell and chop the first
character, with a special condition to exclude cell(0,0).

I assume many tabbed tables have needed to be converted to arrays. My
solution is klugy and seems likely to cause errors at some later date. Is
there a way around the vbCr problem?

 

[Doug Robbins - Word MVP]

I am not quite sure what you mean by a "tabbed table in a TXT file", but the
following piece of code dumps an array into a table, then sorts it (I since
come up with better ways to sort an array), then creates a new array from
the data in the sorted table.

Set target = Documents.Add
Set newtable = target.Tables.Add(Range:=target.Range(0, 0),
numrows:=cboContactList.ListCount, NumColumns:=5)
' Populate the cells of the table with the contents of the array
For i = 1 To cboContactList.ListCount
For j = 1 To 4
newtable.Cell(i, j).Range.InsertBefore MyArray(i - 1, j - 1)
Next j
Next i
' sort the table
newtable.Sort ExcludeHeader:=False ', FieldNumber:="Column 1",
SortFieldType:=wdSortFieldText, SortOrder:=wdSortOrderAscending
i = newtable.Rows.Count
' Get the number of columns in the table of client details
j = 4
' Set the number of columns in the Listbox to match
' the number of columns in the table of client details
cboContactList.ColumnCount = 4
' Define an array to be loaded with the client data
Dim NewArray() As Variant
'Load client data into MyArray
ReDim NewArray(i, j)
For n = 0 To j - 1
For m = 0 To i - 1
Set myitem = newtable.Cell(m + 1, n + 1).Range
myitem.End = myitem.End - 1
NewArray(m, n) = myitem.Text
Next m
Next n


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[christophercbrewster via OfficeKB.com]

Your code goes in the opposite direction of what I'm doing: I'm going from a
TXT file to an array. I should have described the file better. It's like this:


string <tab> string <tab> string <tab> string <return>
string <tab> string <tab> string <tab> string <return>
string <tab> string <tab> string <tab> string <return>
...

I use a Filesystem operation to read the file contents to a string variable,
and then process the variable. The SPLIT function works great, first on vbCr
and then on vbTab, except that the vbCr's get left in. REPLACE doesn't remove
them, and the array is useless until I delete them.

 

[Doug Robbins - Word MVP]

Chris,

When I take

string <tab> string <tab> string <tab> string <return>
string <tab> string <tab> string <tab> string <return>
string <tab> string <tab> string <tab> string <return>

and use edit replace to replace the " <tab> " with an actual tab and "
<return >" with an actual return so that I have three paragraphs each
containing the words string separated by tabs, and then run the following
macro

Dim myarray As Variant
Dim mystr As String
mystr = ActiveDocument.Range.Text
myarray = Split(mystr, vbCr)
MsgBox myarray(1) & " - " & myarray(2)

the message box displays

string string string string - string string string string

indicating that there is no vbCr contained in the elements of the array.
--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[christophercbrewster via OfficeKB.com]

I still haven't explained this right. I'm reading the content of a TXT file
into a string variable using a Filesystem operation. I don't open the file
within Word. The code converts the string variable to a string array. It
actually works fine once I solved the vbCr problem.

(I originally did this by opening the file, converting to a table, then
assigning each table cell to an array cell. Not opening in Word makes the
process cleaner and faster, even with the extra code I described.)

--
Christopher Brewster
Lockheed Martin, Eagan MN

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/word-programming/200902/1

 

[Peter Jamieson]

If I do this here using a file that only has CR end of line separators,
I don't see any CRs in my split strings (I haven't split by tabs yet).
How about you?

Sub readtext()
Dim i As Integer
Dim j As Integer
Dim objFSO As FileSystemObject
Dim objTS As TextStream
Dim strFileContents As String
Dim strFileRows() As String
Set objFSO = New FileSystemObject
' substitute your own file name here
Set objTS = objFSO.OpenTextFile("c:\a\texttabcr.txt")
strFileContents = objTS.ReadAll
objTS.Close
Set objTS = Nothing
Set objFSO = Nothing
Debug.Print strFileContents
For i = 1 To Len(strFileContents)
Debug.Print i, Asc(Mid(strFileContents, i, 1))
Next
strFileRows = Split(strFileContents, vbCr)
For i = LBound(strFileRows) To UBound(strFileRows)
For j = 1 To Len(strFileRows(i))
Debug.Print i, j, Asc(Mid(strFileRows(i), j, 1))
If Mid(strFileRows(i), j, 1) = vbCr Then MsgBox (CStr(i) & " " &
CStr(j))
Next
Next

End Sub



Peter Jamieson

http://tips.pjmsn.me.uk

 

[christophercbrewster via OfficeKB.com]

Thank you for posting this code. I haven't had a chance to investigate why
your code doesn't encounter a problem with the vbCr character while mine does
(without the modification that I described). But here is a similar vbCr
problem in another context, reinforcing my feeling that this character is
treated differently:

This problem is the opposite of my original question, which was how to read a
tabbed table into an array. Now I'm trying to write an array to a TXT file:
convert the array to a single string divided by vbCr and vbTab characters,
and write the string. I make each row of the array into a tab-divided string
ending with vbCr, then add each of these row strings to a "master" string.
When I write this string to a TXT file, where there should be a CR character
there's a small rectangle indicating an unusable character, and the line
doesn't end there. I'd like to read and write vbCr within strings without the
need for excessive extra code. Is there a reason why it doesn't get
transferred cleanly when the other characters do?

If I do this here using a file that only has CR end of line separators,
I don't see any CRs in my split strings (I haven't split by tabs yet).
How about you?

Sub readtext()
Dim i As Integer
Dim j As Integer
Dim objFSO As FileSystemObject
Dim objTS As TextStream
Dim strFileContents As String
Dim strFileRows() As String
Set objFSO = New FileSystemObject
' substitute your own file name here
Set objTS = objFSO.OpenTextFile("c:\a\texttabcr.txt")
strFileContents = objTS.ReadAll
objTS.Close
Set objTS = Nothing
Set objFSO = Nothing
Debug.Print strFileContents
For i = 1 To Len(strFileContents)
Debug.Print i, Asc(Mid(strFileContents, i, 1))
Next
strFileRows = Split(strFileContents, vbCr)
For i = LBound(strFileRows) To UBound(strFileRows)
For j = 1 To Len(strFileRows(i))
Debug.Print i, j, Asc(Mid(strFileRows(i), j, 1))
If Mid(strFileRows(i), j, 1) = vbCr Then MsgBox (CStr(i) & " " &
CStr(j))
Next
Next

End Sub

Peter Jamieson

http://tips.pjmsn.me.uk

I still haven't explained this right. I'm reading the content of a TXT file
into a string variable using a Filesystem operation. I don't open the file

[quoted text clipped - 4 lines]

assigning each table cell to an array cell. Not opening in Word makes the
process cleaner and faster, even with the extra code I described.)

--
Christopher Brewster
Lockheed Martin, Eagan MN

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/word-programming/200902/1

 

[christophercbrewster via OfficeKB.com]

I found the problem-- it was the vbCr-vbCrLf distinction. I've seen it
mentioned but never had to deal with it. When I used vbCrLf as the delimiter
for SPLIT and also to write the table back to the file, everything was fine.
Maybe you thought no one would be this dumb, but anyway I learned something.

--
Christopher Brewster
Lockheed Martin, Eagan MN

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/word-programming/200902/1

 

[Peter Jamieson]

Maybe you thought no one would be this dumb

Certainly not, but for a description of the stuff you /might/ have to
deal with if your text file contents could arrive from anywhere, try

http://en.wikipedia.org/wiki/Newline

(Although even that coud do with some clarifications)

Peter Jamieson

http://tips.pjmsn.me.uk

 

[christophercbrewster via OfficeKB.com]

... a description of the stuff you /might/ have to

deal with if your text file contents could arrive from anywhere, try

In my case the type of file is very restricted, so I won't encounter those
issues. But I'll keep your code to show how to handle more general cases.
Thanks.