Import text file VBA not working

ChanL

New Member
Joined
Apr 8, 2021
Messages
45
Office Version
  1. 2019
Platform
  1. Windows
Hi, I have a task which I need to write a VBA code to import text file to a sheet name "Text File". Then, I need to perform some autofilter and text to column to format the copied data into the format I wanted. The problem is everytime after I imported my text file and perform the next step (which is the autofilter and text to column), the autofilter VBA seems cannot detect the blanks row and hence I can't delete the blanks row. Below is my code :

Import text file :
VBA Code:
Sub importtxt()

Dim txtfile As Integer
Dim filename As String
Dim arrforline() As String
Dim arrfordata() As String
Dim rowcount As Long
Dim colcount As Integer
Dim content As String
Dim delimiter As String

filename = Sheets("Main").Cells(2, 2).Value + "\" + "filename.txt"
delimiter = vbTab
txtfile = FreeFile
Open filename For Input As txtfile
content = Input(LOF(txtfile), txtfile)
Close txtfile
arrforline() = Split(content, vbLf)

For rowcount = LBound(arrfordata) To UBound(arrfordata)
Sheets("Text file").Cells(rowcount + 1, colcount + 1) = arrfordata(colcount)
Next colcount
End If
Next rowcount
End Sub

autofilter and text to column code:
VBA Code:
Sub preparetxt()

Sheets("Text File").Columns(1).Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
    Fieldinfo:=Array(Array(0, 1), Array(5, 1), Array(23, 1), Array(42, 1), Array(54, 1), _
    Array(69, 1), Array(79, 1), Array(97, 1), Array(109, 1), Array(123, 1)), _
    trailingminusnumbers:=True
    
Application.CutCopyMode = False

'autofilter
Sheets("Text File").Columns(1).AutoFilter field:=1, criteria:=Array("CODE", "DATE", "="), _
Operator:=xlFilterValues

'delete rows
Dim lastrow As Long, rng As Range

With Sheets("Text File")
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
Set rng = .Range(.Cells(9, "A"), .Cells(lastrow, "A")) 'delete rows from A9 to the last row of the autofiltered data
rng.Select
rng.EntireRow.Delete

End With

Selection.AutoFilter

End Sub
    

End Sub
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

HaHoBe

Active Member
Joined
Jan 24, 2003
Messages
474
Office Version
  1. 2013
Platform
  1. Windows
Hi ChanL

in the first code these lines such be the troublemakers
VBA Code:
arrforline() = Split(content, vbLf)

For rowcount = LBound(arrfordata) To UBound(arrfordata)  'differnet name here, array isn´t filled
  Sheets("Text file").Cells(rowcount + 1, colcount + 1) = arrfordata(colcount)  'colcount isn´t filled
Next colcount 'no For colcount...
End If 'no starting If-Clause
Next rowcount
Maybe try this code instead
VBA Code:
Sub importtxt_210512()

Dim txtfile As Integer
Dim filename As String
Dim arrforline As Variant
Dim rowCount As Long
Dim content As String
Dim delimiter As String

filename = Sheets("Main").Cells(2, 2).Value & "\" & "filename.txt"
delimiter = vbTab
txtfile = FreeFile
Open filename For Input As txtfile
content = Input(LOF(txtfile), txtfile)
Close txtfile
arrforline = Split(content, vbLf)

For rowCount = LBound(arrforline) To UBound(arrforline)
  Sheets("Text file").Cells(rowCount + 1, 1) = arrforline(rowCount)
Next rowCount
End Sub
Ciao,
Holger
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,147
Office Version
  1. 365
Platform
  1. Windows
In terms of the AutoFilter part.
Your filter seems to be working for me.
If you put a breakpoint after the Autofilter line, does it look right ?
If that looks right then:
• Are you taking into account that your select range is only starting from Row 9 ?
If you have blanks not being picked up by the autofilter can you identify one of those rows and apply these formulas to Column A of that row.
eg if Row 10
=Len(A10) and
=Code(A10)
 

ChanL

New Member
Joined
Apr 8, 2021
Messages
45
Office Version
  1. 2019
Platform
  1. Windows
Hi there, thanks for reply. I tried this code, and it can import the text file. But it still can't solve my second problem which is the autofilter part. It still can detect the blanks row and delete it. But, if I manually auto filter instead of using VBA, it works. So, I guess maybe there is something wrong with the code. But I not sure what is the problem.
Hi ChanL

in the first code these lines such be the troublemakers
VBA Code:
arrforline() = Split(content, vbLf)

For rowcount = LBound(arrfordata) To UBound(arrfordata)  'differnet name here, array isn´t filled
  Sheets("Text file").Cells(rowcount + 1, colcount + 1) = arrfordata(colcount)  'colcount isn´t filled
Next colcount 'no For colcount...
End If 'no starting If-Clause
Next rowcount
Maybe try this code instead
VBA Code:
Sub importtxt_210512()

Dim txtfile As Integer
Dim filename As String
Dim arrforline As Variant
Dim rowCount As Long
Dim content As String
Dim delimiter As String

filename = Sheets("Main").Cells(2, 2).Value & "\" & "filename.txt"
delimiter = vbTab
txtfile = FreeFile
Open filename For Input As txtfile
content = Input(LOF(txtfile), txtfile)
Close txtfile
arrforline = Split(content, vbLf)

For rowCount = LBound(arrforline) To UBound(arrforline)
  Sheets("Text file").Cells(rowCount + 1, 1) = arrforline(rowCount)
Next rowCount
End Sub
Ciao,
Holger
 

ChanL

New Member
Joined
Apr 8, 2021
Messages
45
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

In terms of the AutoFilter part.
Your filter seems to be working for me.
If you put a breakpoint after the Autofilter line, does it look right ?
If that looks right then:
• Are you taking into account that your select range is only starting from Row 9 ?
If you have blanks not being picked up by the autofilter can you identify one of those rows and apply these formulas to Column A of that row.
eg if Row 10
=Len(A10) and
=Code(A10)
Hi there, I tried to fix my first code which is the import text file part and yes it works. But still the autofilter part doesn't works well, it still can't detect all the blanks row. FYI, the reason I choose row 9 is because the range (after auto filter) that I want to delete is start from row 9. If possible can you advice which part of the codes gone wrong?
 

HaHoBe

Active Member
Joined
Jan 24, 2003
Messages
474
Office Version
  1. 2013
Platform
  1. Windows
Hi ChanL,

wouldn´t some data from your side (maybe a sample text file or sample workbook) make it easier to track down where the error might be? Do you want to to delete all rows starting at row 9 or just those autofiltered? Your code should delete all rows starting at row 9.

Maybe this small alteration may help:
VBA Code:
With Sheets("Text File")
  lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
  If lastrow > 9 Then
    Set rng = .Range(.Cells(9, "A"), .Cells(lastrow, "A")) 'delete rows from A9 to the last row of the autofiltered data
    rng.EntireRow.Delete
  End If
End With
Ciao,
Holger
 
Last edited:

HaHoBe

Active Member
Joined
Jan 24, 2003
Messages
474
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Hi ChanL,

for deleting the autofiltered rows from row 9 on you may try this:
VBA Code:
With Sheets("Text File")
  lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
  If lastrow >= 9 Then
    On Error Resume Next
    Set rng = .Range(.Cells(9, "A"), .Cells(lastrow, "A")).SpecialCells(xlCellTypeVisible)
    If Not rng Is Nothing Then
      rng.EntireRow.Delete
      Set rng = Nothing
    End If
    On Error GoTo 0
  End If
End With
Ciao,
Holger
 
Last edited:

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,147
Office Version
  1. 365
Platform
  1. Windows
Thanks for the clarification regarding only deleting from Row 9

However regarding -
it still can't detect all the blanks row

Did you try to do the Len and Code checks ?
Since it is picking up blanks for me, it is worth checking if the blanks are really blanks or whether they are some other non-printing character which present as a blank.
Note: Code will return #Value! for an empty cell

If you have blanks not being picked up by the autofilter can you identify one of those rows and apply these formulas to Column A of that row.
eg if Row 10
=Len(A10) and
=Code(A10)
 

ChanL

New Member
Joined
Apr 8, 2021
Messages
45
Office Version
  1. 2019
Platform
  1. Windows
Hi ChanL,

wouldn´t some data from your side (maybe a sample text file or sample workbook) make it easier to track down where the error might be? Do you want to to delete all rows starting at row 9 or just those autofiltered? Your code should delete all rows starting at row 9.

Maybe this small alteration may help:
VBA Code:
With Sheets("Text File")
  lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
  If lastrow > 9 Then
    Set rng = .Range(.Cells(9, "A"), .Cells(lastrow, "A")) 'delete rows from A9 to the last row of the autofiltered data
    rng.EntireRow.Delete
  End If
End With
Ciao,
Holger
Hi, I want to delete all those autofiltered starting from row 9.
 

ChanL

New Member
Joined
Apr 8, 2021
Messages
45
Office Version
  1. 2019
Platform
  1. Windows
Thanks for the clarification regarding only deleting from Row 9

However regarding -


Did you try to do the Len and Code checks ?
Since it is picking up blanks for me, it is worth checking if the blanks are really blanks or whether they are some other non-printing character which present as a blank.
Note: Code will return #Value! for an empty cell
Hi, regarding the Len and Code checks, do i include it in my autofiltering VBA code or ?
 

Forum statistics

Threads
1,136,735
Messages
5,677,462
Members
419,693
Latest member
divtjd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top