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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MikeZz - 20 Mar 2008 18:18 GMT

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 to this Message

Jim Rech - 20 Mar 2008 20:18 GMT

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

Signature

Jim

| Hi,
| I get an excel file that may have more than the 65k line max in Excel03.
[quoted text clipped - 9 lines]
|
| MikeZz

Reply to this Message

MikeZz - 20 Mar 2008 22:29 GMT

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?

> Have you considered a low tech solution like splitting the file up into
> shorter files?
[quoted text clipped - 14 lines]
> |
> | MikeZz

Reply to this Message

Jim Rech - 22 Mar 2008 15:50 GMT

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

Signature

Jim

> Thanks for the reply Jim.
> It takes a long time to run the file and I would have to run it 4 or 5
[quoted text clipped - 37 lines]
>> |
>> | MikeZz

Reply to this Message

MikeZz - 25 Mar 2008 15:42 GMT

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,

> Sorry, was off yesterday.
>
[quoted text clipped - 50 lines]
> >> |
> >> | MikeZz

Reply to this Message