Open text files in a 65536 row file instead of 1048576?

[AverageAmy]

Is there a way to open a text file from Excel 2010 and specify that I want it in .xls format?

I am working in compatibility mode, and expected that when I opened a text file from code within an xls file, the text file would have 65,536 rows, but it has 1,048,576. This causes a problem when we try to copy the sheet with the data from the text file, and insert the sheet into our xls workbook. See code below. The error is: Run-time error ‘1004’: Excel cannot insert the sheets into the destination workbook, because it contains fewer rows and columns than the source workbook…..

I know I can get the data other ways (such as copying and pasting only the cells containing data) but I was hoping to make minimal changes to the code below as I will have to make it across several templates. Specifically, I was hoping that there was a qualifier I could add to the Workbooks.OpenText statement after “Tab:=True” – Perhaps something about opening the text file in File Format 56. However I have not been able to find out how to do that. Any thoughts?

.....[INDENT]FileToOpen = Application.GetOpenFilename("All Files (*.*),*.*,GeoTAC Files (*.ctf),*.ctf, ­_
Text Files (*.txt),*.txt,Excel  Files (*.xls),*.xls", 1, , , False)
 
Workbooks.OpenText Filename:=FileToOpen, _
    DataType:=xlDelimited, Tab:=True
ActiveSheet.Name = "Junk1" ' renamed the sheet to "Junk1"[/INDENT]
 
'Extracting source File Name without path[INDENT]For islash = Len(FileToOpen) To 1 Step -1
If Mid(FileToOpen, islash, 1) = "\" Then
AFileName = Right(FileToOpen, Len(FileToOpen) - islash)
Exit For
End If
Next islash[/INDENT]
 
'Deleting old sheets
    Workbooks(This).Activate
     For Each sht In ActiveWorkbook.Worksheets
     If sht.Name = "Unfiltered" Then
      sht.Delete
     End If
    Next sht
 
'Copying the Tab delimitted file to main Excel file
    Windows(AFileName).Activate
    Sheets("Junk1").Select
    Sheets("Junk1").Copy After:=Workbooks(This).Sheets("XXXPlot") ‘This is where the error occurs
    Sheets("Junk1").Name = "Unfiltered"
    Workbooks(AFileName).Close SaveChanges:=False 'Closing the tab delimited file
 ....

 

[sijpie]

Save the file first (as xls) then close it and reopen it. Now it will have the required 65k rows

 

[AverageAmy]

Save the file first (as xls) then close it and reopen it. Now it will have the required 65k rows

Thank you for the response. I was hoping to avoid saving the file somewhere.

 

[sijpie]

but if you do it through code with
application.enablescreenupdate=false
and with your internally supplied name the user won't even notice. It'll be a small file