I have an Excel VBA app that automates reports on a data warehouse, sometimes pulling large amounts of data.
I pull data from SQL Server 2008, and I have found that the copyfromRecordset is much, much faster than looping through the recordset and pasting the data into the cell one by one. The latter approach offers much more flexibility to control what goes in there.
So i converted from the loop approach to the copyFromRecorset. It's great but I can't control the cell format. We have a much of numeric data that is stored in the database as VARCHAR, so it gets copied into Excel as text. This is bad because I need to run sum functions on the column but can't. If I try to convert text to numbers it takes FOREVER since it is a large data set.
Any way to have the copyFromRecordset NOT predetermine the format?