Text file not getting imported to excel

[xsdip]

I have used the below code to import textfile to excel. But there is a line break in my 6th line of textfile because of which the data below the 6th line in textpad is not importing to excel file. Can anyone please help on this.

With application.filedialog(msofiledialogfolderpicker)
.title ="select folder"
.allowmultiselect=false
Selectfolder = .selecteditems(1)& "\"
Opentextfile = dir(select folder)

Do while opentextfile <>"
Workbooks.opentext filename:=selectfolder & "" & opentextfile
Importworkbook = activeworkbook
Set Wsheet = Thisworkbook.worksheets("sheet1")
Importworkbook.worksheets(1).range("A1).currentregion.copy Wsheet.range("A1")
Importworkbook.close true
...
...

Thank you in advance.

 

[Alex Blakenburg]

You just need to change how the macro is determining where the last row is.
Since the macro is assuming A1 has data in it anyway try using UsedRange.

Importworkbook.worksheets(1).UsedRange.copy Wsheet.range("A1")

 

[xsdip]

You just need to change how the macro is determining where the last row is.
Since the macro is assuming A1 has data in it anyway try using UsedRange.

Importworkbook.worksheets(1).UsedRange.copy Wsheet.range("A1")

That was of great help. Thank you so much

Could you please help me on one of my doubt??

How to import the textfile without a line break.

The 6th line is a blank line in my textfile. When I import it to excel, the 6th row is blank.

 

[Alex Blakenburg]

You can add a lot more code to filter out all blank rows but if you are pretty sure it is always just row 6 you need to delete just add the code below prior to your copy the Sheet line.

Dim blankRowNo As Long       ' <--- Can be moved to where your other Dim statements are
blankRowNo = 6                      ' <--- Change to blank row no
Importworkbook.Worksheets(1).Rows(blankRowNo).Delete

' Add before
Importworkbook.worksheets(1).UsedRange.copy Wsheet.range("A1")