Solved

T

Posted on 2003-10-31
13
487 Views
Last Modified: 2013-12-25
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.

         
0
Comment
Question by:ptani
  • 5
  • 3
  • 2
  • +1
13 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 9657334
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.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 9657345
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.
0
 
LVL 9

Expert Comment

by:Dang123
ID: 9657373
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
http://www.vbusers.com/code/codeget.asp?ThreadID=368&PostID=1&NumReplies=0

Copy data from an Access database into an Excel spreadsheet
http://www.vb-helper.com/howto_access_to_excel.html

Copy data from an Excel spreadsheet into an Access database
http://www.vb-helper.com/howto_excel_to_access.html

Using the Excel Object Library
http://www.thescarms.com/vbasic/ExcelExport.asp
0
 

Author Comment

by:ptani
ID: 9677016
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.
Thanks,
Ptani
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 9677067
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:

'100000000000000000000000

it will appear as this:

100000000000000000000

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.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 30

Expert Comment

by:nmcdermaid
ID: 9677071
And upon reading your question again, you are never going to add up accounts so the single quote should do the trick.
0
 

Author Comment

by:ptani
ID: 9677251
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.
0
 

Author Comment

by:ptani
ID: 9677255
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.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 9677458
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.



0
 
LVL 9

Expert Comment

by:Dang123
ID: 10383471
I am curious how ptani made out with this.
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 10490962
PAQed - no points refunded (of 20)

Computer101
E-E Admin
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now