Formatting from CopyFromRecordset



I have a strange problem. I am using CopyFromRecordset to paste some
data from a database, then formatting the data. The first column
includes a date, so it comes in as a Julian value, then I format it to
mm/dd/yyyy. This is on Sheet1. Then, I use CopyFromRecordset for
another select statement and put it in Sheet2. It also has a date in
the first column. At this point, the first column of Sheet1, only
where the range overlaps with Sheet2, changes format to m/d/yyyy. The
header row and unpopulated rows still have the mm/dd/yyyy format.

Searching forums, I found this problem that sounds similar. Any ideas
as to why using CopyFromRecordset on one sheet would affect a
different sheet in the same workbook?

A snippet of the code looks like this: (a different sheet is activated
during the process)
Set rsResult = cmDB.Execute
shtHours.Range("A2").CopyFromRecordset rsResult
shtHours.Range("A:A").NumberFormat = "mm/dd/yyyy"
cmDB.CommandText = sql
Set rsResult = cmDB.Execute
shtGas.Range("A2").CopyFromRecordset rsResult
shtGas.Range("A:A").NumberFormat = "mm/dd/yyyy"

shtHours is the sheet that gets the "m/d/yyyy" format in column A.


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question