opening text file as a .xls file and not a .xlsx

[Russ]

I am working in Excel 2007. In a file of type .xls I have recorded a macro
to open a text file as shown below:

Workbooks.OpenText Filename:="D:\MVCC\activity.dnl", Origin:=437, StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2,
1), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1),
Array(8, 1), Array(9, 1), _
Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1)),
TrailingMinusNumbers:=True

The code executes properly however the file that is opened is a .xlsx file
type. I would like the file type to be a .xls file type. Is there a way to
do this

 

[Ron de Bruin]

Hi Russ

Not test it but i think it use the format of the default save format
Office Button > Excel Options > Save

You can use code to open a xls workbook and use
ActiveSheet.QueryTables.Add to add the txt file if you not want to change the default
save format

If you need help post back

 

[Russ]

Hi Ron,
I had already tried your first suggestion and it did not solve the problem.
I like your second suggestion as it removes the step where I copy the
resulting sheet in the new file back to the workbook with the code. I had
not thought of this approach.
Thanks for you help

 

[Ron de Bruin]

See this page for code
http://www.rondebruin.nl/txtcsv.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Hi Ron,
I had already tried your first suggestion and it did not solve the problem.
I like your second suggestion as it removes the step where I copy the
resulting sheet in the new file back to the workbook with the code. I had
not thought of this approach.
Thanks for you help

 

[Russ]

Hi Ron,
Thanks again. Your code is far superior to mine.