Error 13 Type Mismatch while looping through text file

S

Steve Roberts

I have a text file that I need to import into a table. I have it working
fine except that I get an error 13 type mismatch error on the last line of
each of my text files. The rest of the lines in the text file seem to import
just fine. I assume it is because of how I am looping through the lines in
the text file but I can't seem to figure out where the problem is.

The code I am using is below.

Thanks in advance for your suggestions.

Steve


Sub ReadTextFile(strFile As String)

Dim intF As Integer
Dim I As Integer
Dim lngLines As Long
Dim lngBlank As Long
Dim arrLineParts() As String
Dim arrFixedFeatures() As String
Dim Color, ComputerName, Copies, Duplex, MediaType, strDate, strTime,
SquareFT, Username, DocumentName, PrinterName, ClientCode As String
Dim SubCode, PaperSize, Features, PageCount, Cost, AccountBalance, Sets,
Originals, strLineBuf As String

intF = FreeFile()
Open strFile For Input As #intF

Do While EOF(intF) = False
Line Input #intF, strLineBuf

arrLineParts = Split(strLineBuf, ",")
arrLineParts(9) = Replace(arrLineParts(9), "/", ",")
arrFixedFeatures = Split(arrLineParts(9), ",")


Username = Replace(arrLineParts(0), "MARTIN\", "")
DocumentName = arrLineParts(1)
PrinterName = Replace(arrLineParts(2), "\\PRINTSVR\", "")

strDate = arrLineParts(3)
strTime = arrLineParts(4)
ComputerName = arrLineParts(5)
ClientCode = arrLineParts(6)
SubCode = arrLineParts(7)
PaperSize = arrLineParts(8)
Features = arrLineParts(9)
PageCount = arrLineParts(11)
Cost = arrLineParts(12)
AccountBalance = arrLineParts(13)

For I = 1 To UBound(arrFixedFeatures())

If (arrFixedFeatures(I) = "C") Then Color = "Y"
If arrFixedFeatures(I) = "D" Then Duplex = "Y"
If Left(arrFixedFeatures(I), 3) = "CP=" Then Sets =
Replace(arrFixedFeatures(I), "CP=", "")
If Left(arrFixedFeatures(I), 3) = "MT=" Then MediaType =
Replace(arrFixedFeatures(I), "MT=", "")

Next

Originals = (PageCount / Sets)
Copies = (PageCount - Originals)

If PrinterName = "Xerox510" Or PrinterName = "Xerox8830" Then
SquareFT = Left(PaperSize, 5) * Right(PaperSize, 5)
SquareFT = SquareFT / 144
Else
SquareFT = ""
End If

Select Case MediaType
Case Is = "Paper"
MediaType = "Bond"
Case Is = "Plain"
MediaType = "Bond"
Case Is = "PLAINORRECYCLED"
MediaType = "Bond"
Case Is = ""
MediaType = "Bond"
Case Is = "Film"
MediaType = "Mylar"
End Select

DoCmd.RunSQL ("INSERT INTO PCOUNTER_Logs(UserName, DocumentName,
PrinterName, dtDate, dtTime, ComputerName, ClientCode, PaperSize, Features,
PageCount, Cost, Color, Duplex, Originals, Sets, Copies, MediaType,SquareFt)
" _
& " VALUES('" & Username & "','" & DocumentName &
"','" & PrinterName & "','" & strDate & "','" & strTime & "','" &
ComputerName & "','" & ClientCode & "','" & PaperSize & "','" & Features &
"','" & PageCount & "','" & Cost & "" _
& "','" & Color & "','" & Duplex & "','" & Originals &
"','" & Sets & "','" & Copies & "','" & MediaType & "','" & SquareFT & "')")

Color = ""
Duplex = ""
Originals = ""
Copies = ""
MediaType = ""

Loop


End Sub
 
D

Douglas J Steele

You're splitting each line into a number of fields. Is there a difference in
what's being parsed for that last line?
 
D

Dirk Goldgar

Steve Roberts said:
I have a text file that I need to import into a table. I have it
working fine except that I get an error 13 type mismatch error on the
last line of each of my text files. The rest of the lines in the text
file seem to import just fine. I assume it is because of how I am
looping through the lines in the text file but I can't seem to figure
out where the problem is.

The code I am using is below.

Thanks in advance for your suggestions.

Steve


Sub ReadTextFile(strFile As String)

Dim intF As Integer
Dim I As Integer
Dim lngLines As Long
Dim lngBlank As Long
Dim arrLineParts() As String
Dim arrFixedFeatures() As String
Dim Color, ComputerName, Copies, Duplex, MediaType, strDate,
strTime, SquareFT, Username, DocumentName, PrinterName, ClientCode As
String Dim SubCode, PaperSize, Features, PageCount, Cost,
AccountBalance, Sets, Originals, strLineBuf As String

intF = FreeFile()
Open strFile For Input As #intF

Do While EOF(intF) = False
Line Input #intF, strLineBuf

arrLineParts = Split(strLineBuf, ",")
arrLineParts(9) = Replace(arrLineParts(9), "/", ",")
arrFixedFeatures = Split(arrLineParts(9), ",")


Username = Replace(arrLineParts(0), "MARTIN\", "")
DocumentName = arrLineParts(1)
PrinterName = Replace(arrLineParts(2), "\\PRINTSVR\", "")

strDate = arrLineParts(3)
strTime = arrLineParts(4)
ComputerName = arrLineParts(5)
ClientCode = arrLineParts(6)
SubCode = arrLineParts(7)
PaperSize = arrLineParts(8)
Features = arrLineParts(9)
PageCount = arrLineParts(11)
Cost = arrLineParts(12)
AccountBalance = arrLineParts(13)

For I = 1 To UBound(arrFixedFeatures())

If (arrFixedFeatures(I) = "C") Then Color = "Y"
If arrFixedFeatures(I) = "D" Then Duplex = "Y"
If Left(arrFixedFeatures(I), 3) = "CP=" Then Sets =
Replace(arrFixedFeatures(I), "CP=", "")
If Left(arrFixedFeatures(I), 3) = "MT=" Then MediaType =
Replace(arrFixedFeatures(I), "MT=", "")

Next

Originals = (PageCount / Sets)
Copies = (PageCount - Originals)

If PrinterName = "Xerox510" Or PrinterName = "Xerox8830" Then
SquareFT = Left(PaperSize, 5) * Right(PaperSize, 5)
SquareFT = SquareFT / 144
Else
SquareFT = ""
End If

Select Case MediaType
Case Is = "Paper"
MediaType = "Bond"
Case Is = "Plain"
MediaType = "Bond"
Case Is = "PLAINORRECYCLED"
MediaType = "Bond"
Case Is = ""
MediaType = "Bond"
Case Is = "Film"
MediaType = "Mylar"
End Select

DoCmd.RunSQL ("INSERT INTO PCOUNTER_Logs(UserName,
DocumentName, PrinterName, dtDate, dtTime, ComputerName, ClientCode,
PaperSize, Features, PageCount, Cost, Color, Duplex, Originals, Sets,
Copies, MediaType,SquareFt) " _
& " VALUES('" & Username & "','" & DocumentName
& "','" & PrinterName & "','" & strDate & "','" & strTime & "','" &
ComputerName & "','" & ClientCode & "','" & PaperSize & "','" &
Features & "','" & PageCount & "','" & Cost & "" _
& "','" & Color & "','" & Duplex & "','" &
Originals & "','" & Sets & "','" & Copies & "','" & MediaType & "','"
& SquareFT & "')")

Color = ""
Duplex = ""
Originals = ""
Copies = ""
MediaType = ""

Loop


End Sub
Click to expand...

Which line of code is raising the error? That would be a clue. Do your
text files by any chance end with an empty line?

Note, by the way, that these declarations ...
Dim Color, ComputerName, Copies, Duplex, MediaType, strDate,
strTime, SquareFT, Username, DocumentName, PrinterName, ClientCode As
String
Dim SubCode, PaperSize, Features, PageCount, Cost,
AccountBalance, Sets, Originals, strLineBuf As String
Click to expand...

.... declare only ClientCode and strLineBuf as String. All the other
variables mentioned are implicitly declared as Variant.
 
S

Steve Roberts

There is no difference in what's in the last line. If I cut and paste a line
from another place in the file and put it as the last line that's where is
fails as far as I can tell. It's hard to trap exactly where it is failing. I
put the date and time variables in the error routine so I can tell what is
in those when the error occurs. That's what lead me to the fact that it is
the last line of each file.
 
S

Steve Roberts

Dirk Goldgar said:
Which line of code is raising the error? That would be a clue. Do your
text files by any chance end with an empty line?

Note, by the way, that these declarations ...


... declare only ClientCode and strLineBuf as String. All the other
variables mentioned are implicitly declared as Variant.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...

Dirk,
I am not sure exactly which line is causing the error. The text files
did have a carrage return after the last line. I deleted that and it still
produces the error. If I copy a line from elsewhere and insert it as the
last line I get the same message.

Thanks

Steve
 
R

Rob Oldfield

What error routine? Is it the presence of that error routine that's getting
in the way of the code stopping immediately on the line that's causing the
error and so allowing you to see what the problem is?
 
D

Dirk Goldgar

Steve Roberts said:
Dirk,
I am not sure exactly which line is causing the error. The text
files did have a carrage return after the last line. I deleted that
and it still produces the error. If I copy a line from elsewhere and
insert it as the last line I get the same message.
Click to expand...

Put a breakpoint on the routine and then run it with a one-line test
file. Step through the code line by line until you see where it fails.
It begins to look as though your error may be occurring after this
procedure has finished, but that could be a mistaken impression.
 
Top