Solved

T

Posted on 2003-10-31
13
488 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

773 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