excel vba copyfromrecordset copies numbers as text

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?
dougfosterNYCAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QuinnDexCommented:
the best way forward would be to convert the database columns to the appropriate data type in SQL Server.

The copyFromRecordset is not predetermining the data type, this is being forwarded from the database, only options are do a convert or change the data type in the database
0
dougfosterNYCAuthor Commented:
well, considering this is a production environment for a large company, changing data types isn't an option.  

I may try converting the recordset field type after I pull in the data, but that will be ugly even if it is feasible.  

no other options?  That is a bummer.  It does seem to be the fastest way to pull data in by far.
0
Steven HarrisPresidentCommented:
Search the web for "CopyFromRecordset corrupts cell format", this is a very common issue and not many workarounds.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dougfosterNYCAuthor Commented:
Yea, ok.... I did but was hopeful there was something I didn't find...

In the end, what I'm going to do is make a proc, and cast those fields as integers and decimals, and pull the proc selection into the recordset.....

That struck me just now as a good work around.

Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.