ADO Recordset or other method?

[JonoB]

I have a pretty complex Excel model that has about 25 sheets. The model captures about 300 variables, spread over a few of these sheets. Each of these 300 variables has been named in Excel.

We run this model for different scenarios a couple of times a day and each time we save-as with a new name. As the model is quite large (6 MB's), this is starting to take up quite a bit of space. Another downside, is that it becomes quite difficult to compare different models against eachother.

What I have done is created an access database, and used ADO to pull out all 300 input's from excel and save it to the database. The named range in excel matches exactly to the field name in Access. This works exceptionally well - it only takes about 2-3 seconds to save each model, which I was very happy with. It also makes comparing different scnearios a breeze.

The downside is pulling the data back into Excel - this takes MUCH longer - probably in the region of 40 seconds. The code that I have used to populate excel with the recordset is as follows:

'Open recordset
With rsSite
Set .ActiveConnection = cnn
.Source = "SELECT * from tblSite WHERE [SiteID]=1"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
.MoveFirst
End With

'Populate Excel
Range("Postcode") = rsSite![ProjectName]
Range("Developer") = rsSite![Developer]
Range("RSL") = rsSite![RSL]
Range("LADistrict") = rsSite![LA]
Range("Postcode") = rsSite![PostCode]
Range("ShortAddress") = rsSite![ShortAddress]

I suspect that the issue is that excel has to find the correct field in the recordset AND find the named range and that this is what is taking so long.

Any ideas on how to speed this up, or another approach that I may take?

Thanks for any advice that you can give.

 

[JonoB]

By the way, I only showed a sample of the recordset in my cde example above. Obviously, there would be about 300 fields for the full recordset.

 

[JonoB]

OK, solved this one.

As my spreadsheet was so big, Excel was recalculating everytime a new input was populated from the recordset....thereby slowing the population down significantly.

So, what I did was force excel to stop calculating before getting the recordset, and then switching it back on after population. Populating now takes 3 seconds!

 

[fredericofonseca]

To add to this if you use positional fields instead of named ones it will be faster.

e.g. Range("Developer") = rsSite.field(1).value or similar

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[JonoB]

Frederico,
Thanks for that tip.

Only problem is that with 300 variables, it gets quite tricky making sure that I map the correct excel range to the correct recordset field.....

To be honest, it only takes about 2-3 seconds to get all 300 records, which is just fine.

Thanks again.