Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
HowDoI Convert Excel to Tab Delim txt (File to long to open in Exc
Hi,
I get an excel file that may have more than the 65k line max in Excel03. I was wondering if there was a utility or plugin that would allow me to bring it into an array through vba so I can do all the calculations because the finished file would fall within the limit. I'm also open to a 3rd party file converter but I need to have it convert to Tab Deliminated Text because the data itself has commas and other special characters in them. Thanks for any help! MikeZz |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
HowDoI Convert Excel to Tab Delim txt (File to long to open in Exc
Have you considered a low tech solution like splitting the file up into
shorter files? -- Jim "MikeZz" wrote in message ... | Hi, | I get an excel file that may have more than the 65k line max in Excel03. | I was wondering if there was a utility or plugin that would allow me to | bring it into an array through vba so I can do all the calculations because | the finished file would fall within the limit. | | I'm also open to a 3rd party file converter but I need to have it convert to | Tab Deliminated Text because the data itself has commas and other special | characters in them. | | Thanks for any help! | | MikeZz |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
HowDoI Convert Excel to Tab Delim txt (File to long to open in
Thanks for the reply Jim.
It takes a long time to run the file and I would have to run it 4 or 5 times to get what I want (going from maybe 5 minutes to 25 minutes of my time). In addition, it would be a lot of extra programing and debugging just to get the multiple files into a single array. I know it sounds like an easy task but the entire code is probably over 6000 lines long and since I didn't develope it with this in mind, I have no idea how easy it would be. I could spend hours just trying to figure out why something doesn't come out right so I'm hesitant to start down that path if I can just get a converter to make it into a text file and then read it into the array. Make sense? "Jim Rech" wrote: Have you considered a low tech solution like splitting the file up into shorter files? -- Jim "MikeZz" wrote in message ... | Hi, | I get an excel file that may have more than the 65k line max in Excel03. | I was wondering if there was a utility or plugin that would allow me to | bring it into an array through vba so I can do all the calculations because | the finished file would fall within the limit. | | I'm also open to a 3rd party file converter but I need to have it convert to | Tab Deliminated Text because the data itself has commas and other special | characters in them. | | Thanks for any help! | | MikeZz |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
HowDoI Convert Excel to Tab Delim txt (File to long to open in
Sorry, was off yesterday.
I don't follow what you're doing exactly. It sounds as if you're only using Excel as a intermediate step to go from a text file to an array. And then doing "calculations" via VB instead of calcing in the sheet. No wonder it's slow<g. Certainly you can do from the text file directly to an array, bypassing the load into Excel step entirely. There are file i/o commands for this in VBA (Open, Read, Write, etc.) And then there's Exel 2007 which accommodates over a million rows.... -- Jim "MikeZz" wrote in message ... Thanks for the reply Jim. It takes a long time to run the file and I would have to run it 4 or 5 times to get what I want (going from maybe 5 minutes to 25 minutes of my time). In addition, it would be a lot of extra programing and debugging just to get the multiple files into a single array. I know it sounds like an easy task but the entire code is probably over 6000 lines long and since I didn't develope it with this in mind, I have no idea how easy it would be. I could spend hours just trying to figure out why something doesn't come out right so I'm hesitant to start down that path if I can just get a converter to make it into a text file and then read it into the array. Make sense? "Jim Rech" wrote: Have you considered a low tech solution like splitting the file up into shorter files? -- Jim "MikeZz" wrote in message ... | Hi, | I get an excel file that may have more than the 65k line max in Excel03. | I was wondering if there was a utility or plugin that would allow me to | bring it into an array through vba so I can do all the calculations because | the finished file would fall within the limit. | | I'm also open to a 3rd party file converter but I need to have it convert to | Tab Deliminated Text because the data itself has commas and other special | characters in them. | | Thanks for any help! | | MikeZz |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
HowDoI Convert Excel to Tab Delim txt (File to long to open in
Hi Jim,
Yes there is the wish-list item that our IT would actually give us software before it's 3 or 4 years old (Office 07) but that probably won't happen... already asked and they don't have any current plans on switching over. I'll try to explain my situtation a little better. We have a database that spits out data into an Excel file. This file can sometimes exceed 65k lines. My goal is to get all the data into a VBA array so I can manipulate/summarize it. If I open the file in excel and load it into an array, it can only read 65k lines. If there is a way to open and read it into an array directly with VBA that ignores the 65k limit, that would be Perfect! I just don't know the command/synatx structure to perform that directly through VBA. And up until you mentioned it, I didn't think it was possible to read past 65k lines in VBA since my VBA programing is still attached to Excel. If you can point me to a good source or give an example of how to read in a file, that would be helpful. The data is very simple, unknown number of rows by about 40 columns. As I mentioned, there could be special characters in the cells so I'd want something that can pull the text/date/value or whatever is in that column and put it into an array. Thanks for your help, "Jim Rech" wrote: Sorry, was off yesterday. I don't follow what you're doing exactly. It sounds as if you're only using Excel as a intermediate step to go from a text file to an array. And then doing "calculations" via VB instead of calcing in the sheet. No wonder it's slow<g. Certainly you can do from the text file directly to an array, bypassing the load into Excel step entirely. There are file i/o commands for this in VBA (Open, Read, Write, etc.) And then there's Exel 2007 which accommodates over a million rows.... -- Jim "MikeZz" wrote in message ... Thanks for the reply Jim. It takes a long time to run the file and I would have to run it 4 or 5 times to get what I want (going from maybe 5 minutes to 25 minutes of my time). In addition, it would be a lot of extra programing and debugging just to get the multiple files into a single array. I know it sounds like an easy task but the entire code is probably over 6000 lines long and since I didn't develope it with this in mind, I have no idea how easy it would be. I could spend hours just trying to figure out why something doesn't come out right so I'm hesitant to start down that path if I can just get a converter to make it into a text file and then read it into the array. Make sense? "Jim Rech" wrote: Have you considered a low tech solution like splitting the file up into shorter files? -- Jim "MikeZz" wrote in message ... | Hi, | I get an excel file that may have more than the 65k line max in Excel03. | I was wondering if there was a utility or plugin that would allow me to | bring it into an array through vba so I can do all the calculations because | the finished file would fall within the limit. | | I'm also open to a 3rd party file converter but I need to have it convert to | Tab Deliminated Text because the data itself has commas and other special | characters in them. | | Thanks for any help! | | MikeZz |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
From share drive particular excel file takes long time to open | Excel Discussion (Misc queries) | |||
Excel File with Numerous links takes a long time to open | Excel Discussion (Misc queries) | |||
Convert Long File Name to Short File Name | Excel Programming | |||
Excel file with hyperlinks takes a long time to open over the network | Links and Linking in Excel | |||
don't insert quotes in excel save as tab-delim txt | Excel Discussion (Misc queries) |