The code below errors because the file is not available or not found. I'm trying to insert a error handling before the line of code, but when the code does not error, it stills reads the error handler. What am I doing wrong??
txt = fs.OpenTextFile(txtFile).readall
Full code...
[vba]Private Sub cmdLImport_Click()
Dim FileName, txt, i As Long, n As Long, w(), X, Y, j As Long, lRow As Long
Dim fs As Object, r As Long, c As Long, rRows As Long
Dim Flg As Boolean, Delim, sSht As Worksheet, cFlg As Boolean, tmp
Dim UV
On Error GoTo EH
Set sSht = Sheets("Log")
lRow = sSht.Range("a" & Rows.Count).End(xlUp).Row
Set fs = CreateObject("scripting.filesystemobject")
txt = fs.OpenTextFile(txtFile).readall
X = Split(txt, vbCrLf)
r = UBound(X)
If r > 1 Then
If Right$(txtFile, 3) = "csv" Then
Delim = ",": cFlg = True
Else
Delim = vbTab
End If
rRows = 10009 - lRow
ReDim w(1 To rRows, 1 To 28)
For i = 0 To r
Y = Split(X(i), Delim)
If UBound(Y) > 0 Then
If UCase(Trim(Y(0))) = "DATE" Or IsDate(Trim(Y(0))) Then Flg = True
If Flg Then
If Len(Trim(Y(0))) > 0 Then
If UCase(Trim(Y(0))) <> "DATE" Then
n = n + 1
On Error Resume Next
For j = 0 To 27
If j = 3 Then
w(n, j + 1) = Left$(Trim(Replace(Y(j), Chr(34), "")), 27) 'fixed the length
ElseIf j = 27 Then
w(n, j + 1) = Left$(Trim(Replace(Y(j), Chr(34), "")), 50) 'fixed the length
Else
w(n, j + 1) = Trim(Replace(Y(j), Chr(34), ""))
End If
Next
On Error GoTo 0
If UBound(Y) > 27 Then
If cFlg Then
For c = 28 To UBound(Y)
If Len(Y(c)) > 0 Then tmp = tmp & ", " & Trim(Y(c))
Next
If Len(tmp) > 0 Then
w(n, j) = w(n, j) & ", " & Trim(Replace(Mid$(tmp, 2), Chr(34), ""))
tmp = ""
End If
End If
End If
If n = rRows Then
MsgBox "Your Log Has Reached Maximum Capacity." & vbCrLf & _
" Some Data Will Not Be Imported.", vbExclamation
Exit For
End If
End If
End If
End If
End If
Next
If n > 0 Then
With sSht.Range("a" & lRow + 1)
.Resize(n, 28).Value = w
.Resize(n).TextToColumns Destination:=.Cells(1, 1), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 3), TrailingMinusNumbers:=True
End With
lRow = Application.Max(8, sSht.Range("ad" & Rows.Count).End(xlUp).Row)
If lRow = 8 Then
UV = UNIQUE(sSht.Range("b9").Resize(n))
sSht.Range("ad9").Resize(UBound(UV) + 1).Value = Application.Transpose(UV)
Else
UV = UNIQUES(sSht.Range("ad9:ad" & lRow + 1).Value, sSht.Range("b9").Resize(n).Value)
If Not IsEmpty(UV) Then
sSht.Range("ad" & lRow + 1).Resize(UBound(UV) + 1).Value = Application.Transpose(UV)
End If
End If
Else
If MsgBox("The file being imported is not properly formatted." & vbCrLf & _
Space(12) & "Do you want to import another file?", vbYesNo + vbQuestion)= vbYes Then
Exit Sub
End If
End If
End If
Unload Me
Cells(Range("A10009").End(xlUp).Row + 1, 1).Activate
EH:
MsgBox "No File Found"
End Sub
[/vba]
Reply With Quote
