Bypass Excel Rows limit and continue to copy data files to Excel

[Mach7]

Hi,

This script which i use to conduct data query works but the problem is when the database is huge and exceeds 65546 rows, the copy process will fail.

Is there a way to bypass the excel limitation and continue to copy data within the excel row limit?

Thank you very much.

=======================================================================================

Please Login or Register  to view this content.

=======================================================================================

[npamcpp]

Is there a way to bypass the excel limitation and continue to copy data within the excel row limit?

Can you see the contradiction? Excel 2003 and earlier has a row limit. It's 65546 rows, as you already know. That's all Excel can do.

If you need all the data in one Excel sheet, the only way to get around that is to upgrade to one of the newer versions. Avoid 2007. 2010 is much better.

If you cannot upgrade, you need to split the data across several sheets.

Why are you copying that much data into Excel in the first place? You can do analysis on external data sources (e.g. Access or SQL) from within Excel.

cheers, teylyn

[Mach7]

Hi teylyn,

Thanks for the advice.

My objective is to copy some data in excel for data crunching, at least in some level of statistics view.

Is there any area in the code that i can improvise?

Thank you.

[arlu1201]

Mach7,

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature.

[royUK]

Maybe you can adapt this

[Mach7]

Hi arlu1201,

Thank you.



Hi royUK,

Thanks for the tip.

Suppose if i use Excel 2010 which has a larger capacity, can i adopt other methods to the same objective?

Thank you.

[npamcpp]

Is there any area in the code that i can improvise?

I suppose you mean "improve"?

With Excel 2010 you have over a million rows to your service.

If you import the data to build a pivot table, for example, you can point the pivot table to an external data source that can exceed the Excel limits, like Access or SQL.

[Mach7]

Hi npamcpp,

I have just tested Excel 2010 with my external database and it too failed because the table has more than 7 million rows.....

The script i have attached are meant to extract the data and use some columns for my KPI. I will increase the formulation of KPI over time.

The major concern now is this script not dynamic enough to react when the database table has heaps of data.

For example if the data rows are within 1 million, that is fine but if the rows are > 1 million, i cannot work on my KPI.

I understand the use of Pivot table which is a great concept for KPI formulation but that will requires some form of manual work in the process. I would like to have a VB code to automate the process.

The code should

1) Query the database
2) Copy data rows in the table to excel where i can process my KPI
3) A VB script that will process my KPI

However, in step 2, error happens when the rows are > 1 million and i still want to use VB to copy whatever data it can handle and load in another sheet.

I am sorry for the inconvenience as i am new in VB scripting.

Thank you.

[npamcpp]

You may want to have a look into PowerPivot. It can process a lot more rows that a normal Excel sheet. And, as I said above, you don't need to import the data into Excel to analyse it with a pivot table.

And finally, manipulating 7 million rows with VBA is sheer folly. Get tools better suited for the job. You wouldn't want to travel from the Northpole to the Southpole on roller skates, either. Sure, it can be done, but it's tedious and time consuming and there are way more efficient tools to do it.

[Mach7]

Hi npamcpp,

I understand the limitations in Excel, even in the 2010 version.

I discovered a limitation in Pivot Table. If i am not wrong, Pivot Table cannot query a specific database.table_name_table as i can only see other table name.

Hence, i have decided to resort to use VBA for advanced KPI generation. I have use Pivot Table for other table that i can access.

Is there any less challenging way to code in VBA?

Thank you.

[AB33]

"If i am not wrong, Pivot Table cannot query a specific database.table_name_table as i can only see other table name"

The new Power Pivot version 2012 is much improved to the first one. You can now copy and paste a static data in to power pivot window with out importing. You can also create a table and a link to a source.

I am pretty sure you can import from almost any data base in to power pivot. If you are unable to do it, you can import your data in to some other forms of files: Text and CVS

As indicated by others, the best option for massive data is power pivot. Go to MS office site. There are hundreds of free videos on many topics. You are spoilled for choices

[npamcpp]

Mach7, try PowerPivot, not just the in-built Pivot Tables. There's a world of difference!!!