rookie777
07-27-2015, 08:16 AM
Hello I'm trying to read HUGE text file and copy it to excel. For some reason I got "Type mismatch" error
in
Set Rng = WS_This.Range(Rng_Fi).Find(T_Str, , , xlWhole) line.
Any idea how to fix it?
Thank you John
Sub T_Import()
Dim Fi_Dlg As FileDialog
Dim Txt_File As String
Set Fi_Dlg = Application.FileDialog(msoFileDialogFilePicker)
Fi_Dlg.Filters.Add "Text Files", "*.txt"
If Fi_Dlg.Show = -1 Then
Txt_File = Fi_Dlg.SelectedItems(1)
Else
Set Fi_Dlg = Nothing
MsgBox "Please Select text file to import"
Exit Sub
End If
Set Fi_Dlg = Nothing
Dim FSO As Object
Dim Fi_Ob As Object
Dim WS_This As Worksheet
Dim Last_Rw As Long
Dim T_Str As String, Rng_Fi As String
Dim Rng As Range
Dim ii As Integer
Dim T_Lng As Long
Set WS_This = ThisWorkbook.Sheets("Sheet1")
Last_Rw = WS_This.Cells(Rows.Count, 1).End(xlUp).Row
Rng_Fi = "A2:A" & Last_Rw
Set FSO = CreateObject("Scripting.FileSystemObject")
Set Fi_Ob = FSO.opentextfile(Txt_File)
With Fi_Ob
Do Until .AtEndOfStream = True
T_Str = .ReadLine
Set Rng = WS_This.Range(Rng_Fi).Find(T_Str, , , xlWhole) '#### ERROR IN THIS LINE ####
If Not Rng Is Nothing Then
T_Lng = Rng.Row
For ii = 1 To 14
WS_This.Cells(T_Lng, 1 + ii).Value = .ReadLine
Next ii
Else
For ii = 1 To 14
.ReadLine
Next ii
End If
Set Rng = Nothing
Loop
.Close
End With
Set Fi_Ob = Nothing
Set FSO = Nothing
Set WS_This = Nothing
End Sub
in
Set Rng = WS_This.Range(Rng_Fi).Find(T_Str, , , xlWhole) line.
Any idea how to fix it?
Thank you John
Sub T_Import()
Dim Fi_Dlg As FileDialog
Dim Txt_File As String
Set Fi_Dlg = Application.FileDialog(msoFileDialogFilePicker)
Fi_Dlg.Filters.Add "Text Files", "*.txt"
If Fi_Dlg.Show = -1 Then
Txt_File = Fi_Dlg.SelectedItems(1)
Else
Set Fi_Dlg = Nothing
MsgBox "Please Select text file to import"
Exit Sub
End If
Set Fi_Dlg = Nothing
Dim FSO As Object
Dim Fi_Ob As Object
Dim WS_This As Worksheet
Dim Last_Rw As Long
Dim T_Str As String, Rng_Fi As String
Dim Rng As Range
Dim ii As Integer
Dim T_Lng As Long
Set WS_This = ThisWorkbook.Sheets("Sheet1")
Last_Rw = WS_This.Cells(Rows.Count, 1).End(xlUp).Row
Rng_Fi = "A2:A" & Last_Rw
Set FSO = CreateObject("Scripting.FileSystemObject")
Set Fi_Ob = FSO.opentextfile(Txt_File)
With Fi_Ob
Do Until .AtEndOfStream = True
T_Str = .ReadLine
Set Rng = WS_This.Range(Rng_Fi).Find(T_Str, , , xlWhole) '#### ERROR IN THIS LINE ####
If Not Rng Is Nothing Then
T_Lng = Rng.Row
For ii = 1 To 14
WS_This.Cells(T_Lng, 1 + ii).Value = .ReadLine
Next ii
Else
For ii = 1 To 14
.ReadLine
Next ii
End If
Set Rng = Nothing
Loop
.Close
End With
Set Fi_Ob = Nothing
Set FSO = Nothing
Set WS_This = Nothing
End Sub