Ronald: A CSV file is a text file. If your vendor can write directly to the
CSV file there is no limit on the number of rows when generating the CSV.
VBA can open a CSV file as text file. Again a text file will not have any
limit on the number of rows. VBA can read the text file one line at a time
and place each row in a worksheet. When the count exceeds 65536 the VBA code
can create a new worksheet and start placing the data in the new worksheet.

I have written some code for a posting about a month ago where the input
data was extremely large and I had to do something similar to what I
described above.

"Ronald R. Dodge, Jr." wrote:

My bad, CVS should be CSV.


"Ronald R. Dodge, Jr." wrote in message
...
I just been posed a question by one of the tech support people of a vendor
of ours. They come to me every now and then when it comes to technical
things that they aren't sure of as they know my knowledge is pretty high.

Anyhow, last I known, all you could do is to be able to dump data into a
single worksheet for as long as it doesn't involved going past the last
row (65536). However, this person is claiming when the data exceeds
65,500 rows, the remaining data gets dumped into additional worksheets.

I don't know of any such feature in Excel, but then I'm only using Excel
2002, which I'm not sure if they are refering to a later version of Excel
or not.

Here's some things that I do know how their data dumping works going from
their product to Excel.

First, their product does use range names when using their add-in, but
when doing the "Save as Data" menu option within their stand alone query
program, it is saving as a CVS type file, which it also does the same
thing as their add-in query program does, as far as dumping the carry over
data onto other worksheets. Obviously, the CVS file would not use range
names in the manner as their Add-In program would.

Using the Add-In program, the program first inserts or deletes the number
of rows between the first row and the second row within their named ranges
of Linked Data ranges (Their Linked and Extended Data ranges are at the
minimal required to be 2 rows each), depending on the number or rows of
data being dumped and the number of rows the Data Ranges are just prior to
being modified. Once the data is dumped, it then uses the FillDown method
within their data ranges for the Extended Data links as these ranges are
typically calculations of some sort. I do know this, given Excel
calculates every single time a little change is made, and provided it's
calculation mode is set to automatic, Excel is calculating each and every
time when a single cell of data is put in, so I also use their events to
make sure the calculation mode is set to "Manual", so as the refreshing of
the data is so much faster than it would be otherwise.

Typically, I would have responded to have their developers look at their
program, but given this is also happening with the Save as Data within the
Query program, and it's saving the information as CVS files, it seems as
though it may be some sort of setting within Excel. However, I can't say
for sure as there's still a chance it could be something else within their
program.

Does anyone know of this sort of behavior or know of some setting
available in Excel? I only ask this as some of the things that takes
place, they do rely on Excel settings while other things they have their
own codes within the Add-In program.

Sincerely,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000