Xtreme Visual Basic Talk

Xtreme Visual Basic Talk (http://www.xtremevbtalk.com/)
-   .NET Office Automation (http://www.xtremevbtalk.com/-net-office-automation/)
-   -   Guidance on how to handle large data in excel using vb.net (http://www.xtremevbtalk.com/-net-office-automation/326783-guidance-handle-data-excel-using-vb-net.html)

bellaelysium 01-23-2014 04:18 AM

Guidance on how to handle large data in excel using vb.net
1 Attachment(s)

I have a question regarding manipulating data in Excel using vb.net.

I actually have massive of data in a workbook...so here's the workflow of what I am going to do.

I want to do many possible divisions of the cells in that column. So as shown in the picture, the light blue means [All cell]/[first cell], and dark blue means [all cells]/[second cell] and continue on until the last cell in column E. Then from there, it will be inverse division e.g.[first cell]/[all cells], [second cell]/[all cells] and so on. So it is like doing all possible combination of division.

So next column will be the same as the previous column, where you do all possible division in Column F.

So once that is done, I want to do some filtering to filter most data based on certain conditions so that I will end up with only a very small bunch of data.

However, the problem here is that since my data is huge, it will cause excel to slow down and I am not sure if it will hit the limit of its no. of rows and columns. So is it possible to actually do the calculations and filtering "behind the scene" before displaying it in Excel as final outcomes?

Pls give me some guidance on how to start on it. So far, I have manage to open excel file using openfiledialog but is unsure of how to proceed on.

PS: I have done manually (tedious!) and it cause Excel to lag or even hang before I am halfway through the data. Hence, I wanted to do it in automated way.

VBobCat 03-10-2014 08:54 AM

There are many ways you could do this, but the choice depends on how experienced you are in VBA.
Regardless of that, you can considerably reduce lag and crashing in excel by disabling automatic recalculation. Have you already tried that?

bellaelysium 03-10-2014 07:34 PM

I have switch to using PERL instead and so far so good. Thanks by the way :)

All times are GMT -6. The time now is 02:35 PM.

Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2018, vBulletin Solutions, Inc.
Search Engine Optimisation provided by DragonByte SEO v2.0.15 (Lite) - vBulletin Mods & Addons Copyright © 2018 DragonByte Technologies Ltd.
All site content is protected by the Digital Millenium Act of 1998. Copyright©2001-2011 MAS Media Inc. and Extreme Visual Basic Forum. All rights reserved.
You may not copy or reproduce any portion of this site without written consent.