Open a text file in a template

[Jac Tremblay]

Hi,
When I open a text file with Excel 2000 I have to format the whole content
and I think that this is extra work not needed.
I would like to open the text file in a template (.xlt) I have created before.
Is that possible? If so, it must be simple but I cannot figure it out
because there is no Template property in the OpenText Method.
Here is the code I use to open the text file.
' ***
ChDir strPath
Workbooks.OpenText Filename:=strTextFile, _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=True, Semicolon:=False, Comma:=True, Space:=False, _
Other:=False, FieldInfo:=Array( _
Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), _
Array(5, 2), Array(6, 2), Array(7, 2), Array(8, 2), _
Array(9, 2), Array(10, 2), Array(11, 2))
' ***
Thanks.

 

[Joel]

Try reading the file as a query. Your field Into parameter 2 is forcing
everything as text.



With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & strTextFile, _
Destination:=Range("A1"))
.Name = strTextFile
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

 

[Jac Tremblay]

Hi Joel,
Sorry for the delay. I took some time off.
I tried your solution and it works fine except for a few points.
First, the
.TextFileTrailingMinusNumbers = True
had to be commented out. It bugs. I use Excel 2000 at the office.
Second, the text that I import looses its trailing 0's so that one cannot
count on the data length which becomes variable. That happens even though all
the destination cells are formated in text.
Third, some text like "816970030423005" appear as "8,1697E+14" which is not
acceptable.
So for the time being, I will chose my new solution (the best so far) to do
the job. Here's how it works.
I create a new workbook from my template and save it. After that, I import
the text data in second new workbook and paste the values in the 1st
workbook, in a sheet formated in text. I can then close the 2nd workbook
without saving the changes.
It is simple and does the job very well.
Thank you for your answer. I remain open to other solutions.
Have a nice day.

 

[Joel]

I'm using Excel 2003 so there may be differences. I think this accounts for
..TextFileTrailingMinusNumbers = True not working.

You methods simply preserves the formating from you templete. You could
accomplish the same thing you arre doing by opening up a templette,
Perfroming the Query, and then do a SAVVEAS to a new filename.

The trailing zeroes being dropped is due to the number formating having a
different number of decimal places. I don't understand the Scientific
Notation (E+14) issue. Numbers will get converted to Scientific Notation if
you exceed the accuracy of your PC which is a function of the Microprocessor
that is install in you PC. It shouldn't depend on the formating unless the
formating is set to TEXT and not a number format. (maybe one was set to
general and the other as a number????)

I would check the formating of one of the cells that got changed from
Scientific notation to a number. The simple way of doing this is the select
the cell and then go to worksheet menu and go to Fomat - Cells - Number and
check which format is selected.

A new worksheet will automatically have all cells formated as General.

 

[Jac Tremblay]

Hi Joel,
I tried your code with Excel 2007 and it does not bug on:
..TextFileTrailingMinusNumbers = True
But the trailing 0's are still lost even though all the destination cells
are formated as text before the query is applied. So that is not the problem.
So for the time being, i will have to stick with my homemade solution.
Thanks again for your answer and your time.
I will keep on trying to find an answer.