Sorting database and export it to excel in many sheets

[cynicon]

I'm using Access and Excel 2003

Every month I receive a database with a column that has multiple dates, so, I need to group them and export every "group of same date", to a new sheet in excel in the same workbook

The problem are:
1Every month dates change
2When a "group of same date" have more than 65,536 lines I need to export it into a new sheet with the same name but adding a counter, example;
(01-01-2010 (1), 01-01-2010 (2), 01-01-2010(03), etc

I'm Know Vba, I have made already a macro that imports the Database to Acess as a table, next? I dont know

[Guido Geurs]

dear,

Some questions:
Why via access ?
What type of file is the source ?
Why not import it in Excel and sort it there ?
Is it possible to attach an example of the source file?

br,

[cynicon]

Thanks for your Reply

Why via access ?
Because access is faster than excel when you manage files of about 200,000 records

What type of file is the source ?
txt

Why not import it in Excel and sort it there ?
I have already made a macro that does it in excel, but it is quite slow, it last about an hour running

Is it possible to attach an example of the source file?
Sure, this is a samble of 25 records, but original source file is about 200,000 records, the column I need to sort is entitled "Fecha de Aplicación Cuota Aceptada"

Thank you so much

[Guido Geurs]

dear,

I have received you demo file.

When you run your macro in Excel, is it like this (very slow)=

- reed all data
- check row 1 and sort it to the exact sheet
- check row 2 and sort it to the exact sheet
- check row 3 and sort it to the exact sheet
- check row 4 and sort it to the exact sheet
.....

or are you working in the memory (much faster)?
like:
- reed all data to an array
- sort the data in other array's
- dump all the data from the array's in the sheets

Because I think (I have no experience with such big files) that working in the memory with array's is much faster than jumping from sheet to sheet in Excel.

br,

[cynicon]

Thanks four your answer, so this is my excel vba code, Im pretty sure its not an array.

[Guido Geurs]

dear,

I have prepared a code with array's (see attachment).

To run : click the command button.

There are some checking in the code: if you want to use them, just uncomment them (delete the ' in front if it)

If you want to change the number if rows in each sheet, delete the ' in the line=

'      ROWS_IN_SH = 4

and change the number.

Please test this with a file with more lines than the one you have send me.
I hope the program is OK.

Later on I can make some modifications like sorting the DATES so the sheets are created in ascending order.

br,

[cynicon]

Thank you so much, I have already use it, however it only works with the 25 records sample file, when I try to run it with the bigger one it doesnt make anything, so in the next link you can find the full archive, so you could figure out the problem, thank you so much for your answer

http://rcpt.yousendit.com/820139764/...1ac057a6c44e6b

[Guido Geurs]

dear,

The reading of the large file is OK but the splitting and finding the dates wont work because the TXT file you have send me has a "COMMA" as delimiter between the values (like the one you have send me as example) !

And the decimal numbers have also a comma in it so the splitting is NOT Ok.

Please can you try with a TXT file with ";" as delimiter between the values ?

If it not works please send me an ewample so I can test it with the program.

br,

[cynicon]

Oops!! Sorry, you are right

So here you can find the correct file, thank you so much


https://www.yousendit.com/download/R...UzhiV3gzZUE9PQ

[Guido Geurs]

dear,

Sorry for this late response but I think you can already guess it "Huston, we have a problem !".
The code I have is OK (see attachment) because it works fine for data files with 20.000 lines .
BUT: when I want to process files bigger than 50.000 lines, Excel blocks.
I have try-ed it on a PC Pentium4 2,6 MHz with 750 Mb of memory.

You will also see in the VBA that I have build in some control's to watch the evolution during the process.

It shows that for your file (with 192.000 lines) there is no problem to read it into the array.
Only when the filter starts, Excel stops at line 12 and the sheet is selected (not in the code).

I will see if I can find the cause fast.
If not, I will try to write a tool in VB6 which does the filtering and saves the data for each sheet in TXT files.
These TXT files can then be read into Excel (I hope) (maybe it's even faster than filtering in Excel).

br,

[Guido Geurs]

dear,

I think thisone will work. (see attachment)
One of the counters was dementioned as Integer and must be Long !
On my PC (Pentium 4 2.6 GHz 750 Mb memory) the File with 192.450 lines is imported and split in 40 min..

br,

[cynicon]

Ggeu,
Thank you so much, it works, perfect, I have no words to express how much I appreciate your help, Ill use it, thank you so much