HowDoI Convert Excel to Tab Delim txt (File to long to open in Exc

[MikeZz]

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

 

[Jim Rech]

Have you considered a low tech solution like splitting the file up into
shorter files?

--
Jim
| 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

 

[MikeZz]

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]

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

 

[MikeZz]

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,