I have problem with exporting data from grid(True DB grid) to excel from a VB application. Here the catch is, if grid has more than 65,500 records (max row for an excel sheet), the records in excess of 65,500 should be dumped into next sheet of the same xls file. Similarly, if limit of the second sheet is exhausted, the control should be transferred to the third sheet and so on and so forth. Again we want to provide cancel export option to halt the export mid way through if required. We, have tried Vaspread, but in this case control cannot be passed on to next sheet on exceeding the threshold limit of 65,500 , on using tidestone control cancel functionality could not be implemented as "writeRange" function was not working for xls.

         Can anyone provide a solution to this problem. A third party control or custom code to accompolish this seemingly daunting task..;).
We used Spread Control to achieve the functionality but it requires excel to be installed on the machine. The client requirement is that it should work with out having excel installed on the machine. It would be great, if anyone can provide us a solution wherein without installing excel on the machine, we can still use the spread contolr.
2) We also used Tidestone control but here we are unable to write "Cancel" functionality and while exporting to tab delimited file, we have to export unlimited records. Currently it is accepting 65k rows and above that it writes into another file. Client doesn't want the data in multiple files.

Need solution ASAP.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You could export the data programatically from the grid as CSV. However this does not have any formatting and will not split into different sheets.

If you want to save a file, without Excel installed, that Excel will open, that splits data over sheets, it will have to be an XML file. (Maybe others too)

So it is concievable that you could write some code to expor the data grid data as XML in a format that Excel will recognise.

There may be a solution outside your requirements though..... why not just export it directly from the database rather than a grid control? Why does it have to be in Excel... no user can concievably go through that many rows of data.

If you want more details, let me know.
Giving it more thought... you can save a recordset as XML. Then you could concievably use XSLT to convert your recordsets XML to Excel XML.

You might even be able to download the XSLT from Microsoft.
You could try looping through your data and use ADO to insert your data, controling the sheet limit yourself.

This may help you get started:

How to update an Excel spreadsheet using ADO

Copy data from an Access database into an Excel spreadsheet

Copy data from an Excel spreadsheet into an Access database

Using the Excel Object Library
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ptaniAuthor Commented:
Thanks all of you for the support. The customer doesn't want to install excel on their server.
Now  i have a new query for you guys..
When I click on the export button, it writes the data to text file and then I save it as .xls. Here the long number is written in exponential format in excel file. Is there any solution to store the number as string in the excel file as is. For eg if the acct # is 111111133333344, then in the excel file it is stored as 1.23 +E1.2 instead of 111111133333344. I want to store it as it is and not in the exponential form.
Any quick solutions will be appreciated.
You should really close this question and ask a new one.

but anyway...

None of my solutions require Excel on the server.

As for your second problem, it's just the formatting in the sheet. If you manually format it as a number it will come through OK.

Also if you precede your value with a single quote, it will appear exactly as you require. However you won't be able to add the figures up.

ie if you export 1000000000000000000 to an Excel sheet it will autoamtically be formatted as 1E+10 (or whatever)

if you export this:


it will appear as this:


but you won't be able to use it in numeric formulas such as SUM or AVG, or be able to add it up.

The only other option is to manually format it, but the only way I know how to do this is write VBA to do it.
And upon reading your question again, you are never going to add up accounts so the single quote should do the trick.
ptaniAuthor Commented:
Hello nmcdermaid,
Should I write the formatting code i.e adding single-quote in the VB code?
Again excel is not installed on the machine. Only after save as .xls the customer will be able to see the data.
ptaniAuthor Commented:
Hello nmcdermaid,
Should I write the formatting code i.e adding single-quote in the VB code?
Again excel is not installed on the machine. Only after save as .xls the customer will be able to see the data.
If you have a sample VB code, pls. send it to me.
I'm not sure I understand what you're getting at about installing Excel.... the server doesn't have it installed, the client machines do, the server will be creating the Excel file. Is that right?

Yep that is where you would put it.. in your VB export routine.

I don't have any sample code for you because I don't have a 'True DB Grid'.

But it really is a simple matter to loop through the rows and save each row to a text file

The other option as Dang123 suggested is to use the Excel provider, which comes with MDAC, to export the data. I've never done this but it sounds like a good idea.

I am curious how ptani made out with this.
PAQed - no points refunded (of 20)

E-E Admin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.