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?
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
....