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

AverageAmy

New Member
Joined
Nov 26, 2011
Messages
25
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?

Code:
.....[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
 ....
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
Save the file first (as xls) then close it and reopen it. Now it will have the required 65k rows
 

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,143
Messages
5,599,975
Members
414,354
Latest member
Flaxarn

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