I have read in a CSV file. I want to take the fifth element of each line and
put it in an array. I tried the code below. arrWeight has been defined as
Variant.

When I run this, it comes back with type mismatch error.

Do While Not EOF(intFileNum)
Line Input #intFileNum, strWholeLine
arrLine = Split(strWholeLine, ",")
arrWeight(i) = arrLine(5)
i = i + 1
Loop

Diana,

The code below worked for me on a dummy .csv file I have on my hard drive.
I tried to comment the code so that you can follow along. It's hard to tell
what the problem might be exactly without knowing which line caused the type
mismatch error. (My general though is that it has to do with the Split
function). Anyhow, see my comments below.

Let me know if this helps.

Best,

Matthew Herbert

Sub TestIt()
Dim strFileName As String
Dim intFileNum As Integer
Dim lngCnt As Long
Dim intPos As Integer
Dim intCommas As Integer
Dim varArr() As Variant
Dim strWholeLine As String

strFileName = Application.GetOpenFilename("CSV (Comma delimited) (*.csv),
*.csv")
If strFileName = "False" Then Exit Sub

intFileNum = FreeFile()
'You can read all the text into a String variable, which can then be
' Split by the line feed character. The UBound of the Split
' array can then be used to size your array prior to loading
' the 5th comma delimited item into the array. If you don't use
' the Split by line feed approach, then the only other way I know of
' is to use ReDim Preserve statement to properly size the array.
' If you use the "read all the text approach", then you can Close
' the file once the text is read into the String variable, and then
' loop through the array created by the Split function using the line
' feed character as the argument for delimiter. (Using the Split
' function approach means you won't have to use the Do While Not
' EOF loop).
'strText = Input(FileLen(strFileName), intFileNum)

lngCnt = 0
intPos = 5
Open strFileName For Input As #intFileNum

Do While Not EOF(intFileNum)
Line Input #intFileNum, strWholeLine

'sample of Split
'Items : 1 2 3 4 5 6
'String : A,B,C,D,E,F
'Split Position: 0 1 2 3 4 5
'So, if you have a string with LESS THAN 5
' commas the Split function won't be able
' to find the "5th" position (which is really the
' "6th" location b/c Split creates a zero-based
' array)
intCommas = Len(strWholeLine) - _
Len(Replace(strWholeLine, ",", ""))

If intCommas = intPos Then
'size the array
ReDim Preserve varArr(lngCnt)
'the syntax to the right of the = is the same as the two
' lines from your code (i.e. arrLine and arrWeight)
varArr(lngCnt) = Split(strWholeLine, ",")(intPos)
lngCnt = lngCnt + 1
End If
Loop

Close #intFileNum

End Sub


"Diana" wrote:

I have read in a CSV file. I want to take the fifth element of each line and
put it in an array. I tried the code below. arrWeight has been defined as
Variant.

When I run this, it comes back with type mismatch error.

Do While Not EOF(intFileNum)
Line Input #intFileNum, strWholeLine
arrLine = Split(strWholeLine, ",")
arrWeight(i) = arrLine(5)
i = i + 1
Loop