Quickest way to copy data from another workbook

[Bip]

Hi, I'm currently using the code below to retrieve rows of data (based on date) from another workbook.
It works well but after using it for a year the rows of data that it has to search has blown out to 30,000 rows, which has made it very slow.
I'm not familiar with using arrays or even if it would help here but is there a quicker way to search and copy data from another workbook?

Please Login or Register  to view this content.

Thanks
Pete

[Kenneth Hobson]

Besides the usual speedups like turning off calculation, events, and screen updating, here are a few improvements.
1. Filter by Date and remove the rows.
2. Do not copy the entirerows, copy the UsedRanges that are visible less row one which usually has the column headings.

If you can attach short sample obfuscated files, we can can more easily help. Select the Go Advanced button in lower right of a reply, and scroll down and click the Manage Attachments hyperlink, browse and upload the file(s).

[Bip]

I've attached a very simplified version of my Workbook and Database, as the original files were just too large to upload.
The only thing you will need to change is the Database location in the Workbook, which is on Sheet2 - Variables
You can also change the search date on this sheet. Although I've only provided data for dates in February this year.

This simplified version is quite fast due to me reducing the size of the database (for uploading purposes).
My database is significantly larger than this and since I'm reading up to 10 sheets of data at a time it has become very slow.
Just wondering if its possible to read each row from the database (if the date matches) into an array, then close the database and write the data into the workbook.
I'm not really sure how to do this or even if it would help reduce the time it takes.

[Bip]

Bump
Could anyone help me convert this to an array. I've had no experience with this.
Thanks
Pete

[mike7952]

Give this a try

Please Login or Register  to view this content.

[Bip]

Hi Mike,

Thank you for the effort, much appreciated.

I got your code working after a minor change(typo):

Please Login or Register  to view this content.

I then added a timer to my old "Get Data" sub and your new version but the unfortunately the overall improvement was negligible.
In the full version my of my workbook I'm running this routine on up to 10 sheets at once and my users get impatient if they have to wait more than a few seconds.
I've uploaded a larger database (the largest this forum will allow) and a new workbook with both the old and new versions with timers on both.
If anyone can think of any other improvements to speed it up it would be very much appreciated.

Thanks
Pete

[mike7952]

When running both codes on my computer the code I gave you is faster. I copied the database rows down to 101995. Your code ran about 11 seconds and the code I gave is under 5 seconds. I really don't think you will get any faster then that.

[Bip]

Wow! you had a significant difference.

I'm guessing that's the difference between Computer Specs, Windows and Office versions we are using.
I just tried again with 101995 rows in the database (like you did). I averaged just over 3 seconds on the old code and just under 3 seconds on your code.
I then doubled the rows to 203989 and re-tested. I averaged about 6.1 seconds on the old code and 5.9 seconds on your code.

After seeing the readings you got, I'm going to go with your code, simply because all the people using my workbook all have different computers.

Mike, thanks again for all your help. I really appreciate it.

Cheers
Pete

[mike7952]

With 203989 Rows of data in a workbook like you're saying you could have, just manually opening the workbook takes some seconds possibly near a minute to open. You could try saving the database workbook as a xlsb file.

[Kenneth Hobson]

Here is my ADO with speed tests. I copied the xlsx file's data down to 100001 rows for 100000 check.

Please Login or Register  to view this content.

[Logit]

Kenneth:

Should your ADO code run on Excel 2007 / Win 10 ?

It fails on

Please Login or Register  to view this content.

"Subscript out of range."

[Kenneth Hobson]

I would think so but I just tested it on win10 Excel 2016. I can test on win7pro Excel 2010 tomorrow. These things usually boil down to the connection string.

https://www.connectionstrings.com/excel/

Do you have a Reference for the Microsoft ActiveX Data Objects 2.8 Library, msado28.tlb? This uses late binding so you don't have to set the reference though it should be in your references.

[Kenneth Hobson]

Of course that error often happens when the ActiveWorkboook does not have the worksheet with the referenced name. You can make it more explicit with ThisWorkbook.

Please Login or Register  to view this content.

[Kenneth Hobson]

Another way you can get that error is when the file set in Worksheets("Variables").Range("A8").Value does not exist.

[Logit]

Its getting late. I'll look at those suggestions tomorrow. It's probably something stupid and silly that I've done. Let you know.

Thanks.

[Bip]

Hi Guys,

I did some more tests today using 203,989 rows in the database and got some major improvements using Mike's code.
I didn't realise until after the tests were completed that I was also running our surveillance system at the same time which is very graphic intensive and taxing on the CPU.
Kenneth, I tried your code (the ADO method) but couldn't achieve the improvements in speed you that did. It was faster than my original code but not always. There seemed to be quite a variation in times each time I ran it. I liked the fact that it didn't need to open the database to read the data though.

Here are the results:
Original code: 15.7 seconds
Mike's code: 4.5 seconds
Kenneth's code: 7.6 to 17.7 seconds

Then here are the results after I closed the surveillance program:
Original code: 11.7 seconds
Mike's code: 3.6 seconds
Kenneth's code: 6.38 to 12.74 seconds

Now the amazing part!
Mike I saw your post regarding saving my database in .xlsb format
I'd never heard of this format before and didn't realize how easy this was to do.
I opened my database, selected Save-as 'Excel Binary Workbook' and then change the variable in my Workbook to search for database.xlsb - too easy!
HOLY WEAPONS OF MASS DESTRUCTION!!! - Check this out!

Original code: 9.49 seconds
Mike's code: 1.45 seconds
Kenneth's code: 10.16 to 10.26 seconds

Based on this simple change and the extra increase in speed, I went straight to my full size Workbook (4MB) and my full size Database (14MB), which is now 6.6MB after saving it as .xlsb, and made the required changes. I can't believe what a difference it made. My workbook is zipping along, grabbing the data with minimal delay now. And that's without changing over to Mike's code.
When I have a spare afternoon in the coming weeks, I will convert all my code over to Mike's new code, which I'm sure will have the whole company dancing in the streets!
I'll report back when I've done this and mark this thread as solved

[Logit]

Added the "Microsoft ActiveX Data Objects 2.8 Library, msado28.tlb" which helped. Got further into the macro before it failed.

Commented out this line:

Please Login or Register  to view this content.

Now failing on this line with a 'Subscript Out Of Range' error:

Please Login or Register  to view this content.

???