Solved

T

Posted on 2003-10-31
13
486 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 30

Expert Comment

by:nmcdermaid
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I am curious how ptani made out with this.
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
Comment Utility
PAQed - no points refunded (of 20)

Computer101
E-E Admin
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

743 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

16 Experts available now in Live!

Get 1:1 Help Now