Write VBA code to read text file

See if you can adapt this - you'll have to change the specifics (text file name and sheet name).

I somewhat understand what's going on with your code. I'm not sure what the X is though. Is X then the number of "strings" after the text line is split? What is the purpose of the I?

For simplicity, here is the code I am working with. There is much more to it, but this is where the text stream is being read in. It seems like you are writing the strings to a worksheet. I want to assign the strings to a variable while the loop is going through the line. See below. this is really the part where I want to make the code general to any text file. FYI, the number of spaces being used in this particular text file is 12, not 20.

------------------------

Set TSO_Connect = FSO_Connect.OpenTextFile(Connectivity)
'Opens the specified text file and returns a TextStream object that can be used to read lines from.


'This Do Loop reads in each line of the connectivity text file. Based on the centroid x,y, and z coordinates

Do Until TSO_Connect.AtEndOfStream

Line_Connect = TSO_Connect.ReadLine

Elm_Connect = Mid(Line_Connect, 1, 18)
Elm_Connect = Trim(Elm_Connect)

Joint1 = Mid(Line_Connect, 19, 12)
Joint1 = Trim(Joint1)

Joint2 = Mid(Line_Connect, 31, 12)
Joint2 = Trim(Joint2)

Joint3 = Mid(Line_Connect, 43, 12)
Joint3 = Trim(Joint3)

Joint4 = Mid(Line_Connect, 55, 12)
Joint4 = Trim(Joint4)

Elm_Xcentroid = Mid(Line_Connect, 67, 12)
Elm_Xcentroid = Trim(Elm_Xcentroid)

Elm_Ycentroid = Mid(Line_Connect, 79, 12)
Elm_Ycentroid = Trim(Elm_Ycentroid)

Elm_Zcentroid = Mid(Line_Connect, 91, 12)
Elm_Zcentroid = Trim(Elm_Zcentroid)

X is a 0 based array containing the data after splitting the line. You could just as well assign the elements of X to variables as write them to a sheet although I don't see the value of doing that as you already have the array (X).

OK. I modified my code to try and incorporate the split method. Regarding assigning the elements of the array to variables, I want to do this as I don't actually need the values written to cells; I need to values to determine some other information. I tried to run the macro and I got a "type mismatch error at the first line of the loop below (see below). I didn't mention that the text files I'm working with usually start with several spaces and then there is text. (Basically the text is right justified in each column). I'm wondering if this could be causing the error. When I put the cursor over the "Connectivity_Textline_Array(i)" it shows "". I'm thinking this means nothing is being read.

Here is the code:


Set TSO_Connect = FSO_Connect.OpenTextFile(Connectivity)
'Opens the specified text file and returns a TextStream object that can be used to read lines from.


'This Do Loop reads in each line of the connectivity text file. Based on the centroid x,y, and z coordinates

Dim Connectivity_Textline_Array 'This variable defines a 1 dimensional array where the strings from the current line are assigned.

Do Until TSO_Connect.AtEndOfStream

Line_Connect = TSO_Connect.ReadLine

Connectivity_Textline_Array = Split(Line_Connect)

For i = LBound(Connectivity_Textline_Array) To UBound(Connectivity_Textline_Array)

Elm_Connect = Connectivity_Textline_Array(i)

Joint1 = Connectivity_Textline_Array(i + 1)

Joint2 = Connectivity_Textline_Array(i + 2)

Joint3 = Connectivity_Textline_Array(i + 3)

Joint4 = Connectivity_Textline_Array(i + 4)

Elm_Xcentroid = Connectivity_Textline_Array(i + 5)

Elm_Ycentroid = Connectivity_Textline_Array(i + 6)

Elm_Zcentroid = Connectivity_Textline_Array(i + 7)

Next i

'All the values of the connectivity table text file are written to the array "Arr_Connect".
Arr_Connect(ElmGroupID_Index_i, 1) = Elm_Connect
Arr_Connect(ElmGroupID_Index_i, 2) = Joint1
Arr_Connect(ElmGroupID_Index_i, 3) = Joint2
Arr_Connect(ElmGroupID_Index_i, 4) = Joint3
Arr_Connect(ElmGroupID_Index_i, 5) = Joint4
Arr_Connect(ElmGroupID_Index_i, 6) = Elm_Xcentroid
Arr_Connect(ElmGroupID_Index_i, 7) = Elm_Ycentroid
Arr_Connect(ElmGroupID_Index_i, 8) = Elm_Zcentroid



'For each shell element, this for loop loops through each group range defined in array "Array_ElmMapInput".
'If the x, y, and z centroid coordinates of the shell element fall within the x, y, and z ranges
'defined for a group, then the element ID is added to the groups column in the array "Arr_ElmGroups".
'Also, the number of elements in each group is tracked in the array "Arr_ElmCount"

For i = 1 To N_Groups
If _
Elm_Xcentroid >= Array_ElmMapInput(i, 2) And Elm_Xcentroid <= Array_ElmMapInput(i, 3) And _
Elm_Ycentroid >= Array_ElmMapInput(i, 4) And Elm_Ycentroid <= Array_ElmMapInput(i, 5) And _
Elm_Zcentroid >= Array_ElmMapInput(i, 6) And Elm_Zcentroid <= Array_ElmMapInput(i, 7) Then

Arr_ElmGroups(Arr_ElmCount(1, i) + 1, i) = Elm_Connect

Oh, and also I forgot to mention that there is more than one space between strings in columns.

Ok,
So I figured out how to get this working. I used the split function in conjunction with the inStr, replace, and Trim methods. If I stop the program while it is running, and each line of text is being read correctly. For some reason though, the values I am printing to the worksheets are different (larger) than the ones printed using the mid function previously used. Is there something else about the split function I should know because I am getting frustrated and get figure out what is going on with this.