Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open a text file in a template
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. -- Jac Tremblay |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open a text file in a template
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" wrote: 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. -- Jac Tremblay |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open a text file in a template
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. -- Jac Tremblay "Joel" wrote: 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" wrote: 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. -- Jac Tremblay |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open a text file in a template
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" wrote: 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. -- Jac Tremblay "Joel" wrote: 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" wrote: 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. -- Jac Tremblay |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open a text file in a template
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. -- Jac Tremblay "Joel" wrote: 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" wrote: 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. -- Jac Tremblay "Joel" wrote: 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" wrote: 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. -- Jac Tremblay |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i get a template to open as the default excel file? | Excel Discussion (Misc queries) | |||
Workbook Open Procedure in Custom Template File | Excel Programming | |||
Excel VBA - open text file, replace text, save file? | Excel Programming | |||
Open a Template/CSV-File from the command line | Excel Programming |