Home > Uncategorized > Excel Sheets being ‘Half’ Active

Excel Sheets being ‘Half’ Active

I encountered a very interesting bug this week. The initial symptom was:

When any values in a SQL Server 2005 stored procedure result that I’m using in an Add-In are of type DATETIME or SMALLDATETIME then the corresponding cells (i.e. same sheet address) in the previously active worksheet (?!) are formatted to DATETIME, even when the data in those cells is formatted in Excel as Number or Currency.

CopyFromRecordset was being used to populate the currently active worksheet.

The answer ended up coming from harfang on Experts-Exchange.com:

Even though the following SHOULD give you a reference to a newly added, active worksheet:

‘Put results to new sheet

Dim NewSheet As Worksheet

Worksheets.Add

Set NewSheet = ActiveSheet

it only ‘half’ recognizes the new sheet as active. The implication being that NewSheet.CopyRecordset DOES copy values into the new sheet, but the formatting subroutines somehow link to the previously active sheet. More annoyingly, this only occurs for DATETIME field types, because any currency or numeric formatting wasn’t applied to the previously active sheet. This can be solved by adding one line:

NewSheet.Activate

which ‘fully’ registers NewSheet as active.

How frustrating! I’d be interested to see any Microsoft articles documenting this.

For more code samples and details on the solution check: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_24856799.html

-Dave

P.S. I’d love to get comments on good / free screencasting / screen recording software. This bug was on the more tricky side to explain and a short video showing symptoms would be really helpful.

Advertisements
Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: