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
