• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1787
  • Last Modified:

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?
  • 2
1 Solution
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
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.
Steven HarrisPresidentCommented:
Search the web for "CopyFromRecordset corrupts cell format", this is a very common issue and not many workarounds.
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now