Reading dirty cells from Excel using ACE driver

[mSolver]

Greetings all,
Not sure if I have the best forum here as this is an Office development question. I have created a shared add-in for Excel which uses the MS ACE driver to read data from Excel and store back to SQL Server. I am using the ACE driver because it seems it is the fastest method of reading a large range of data within Excel. I would like to improve the performance by only reading “Dirty cells” within the range. In other words, instead of reading and then deleting and inserting every cell within the range into the SQL table, I would like to only read and update the cells that have changed. I have found very little information on this topic.

Any thoughts on how I can achieve this or any other recommended approaches?

Regards
Mike

 

[xlbo]

AFAIK excel does not have a concept of storing changes to cells automatically.

The only way I can think of to do this would be to use the worksheet chnage event and store the references to cells that have changed on a separate hidden sheet

This would work for cells which are changed manually but would not capture those which have changed as a result of a calculation however (cells with formulae)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

And an additional note that such an analysis cannot be done without VBA in some way, as already mentioned by Geoff. SQL alone could not accomplish.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mSolver]

Thanks for the feedback. I was hoping to use the "Dirty" method within Excel to determine which cells need to be stored back to the DB. Excel uses this method to determine which cells require calculation so I am trying to find a way to use this to my advantage.

This is a rather advanced COM add-in that we have built within VB but reading Excel cells was slow so we switched to the JET /ACE engine to read the ranges. However, my hunch is that there is not a way to expose the dirty method to the ACE reader.

Thanks again for taking the time to reply.
Regards
Mike

 

[xlbo]

You can use the Dirty method to mark a cell as needing recalc (or I guess for any other purpose) but it doesn't look like there is an "IsDirty" test for a range so it wouldn't actually give you any benefit.

Also, Dirty != Changed

A cell may be changed but if it is not a dependant of any calc it will not be marked as dirty



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

A simply way would be to insert a 'phantom' hidden sheet that would reflect the table structure in the workbook. Use the Worksheet_Change event to mark any cell as changed on the phantom sheet. Then using SQL, you could join the two tables to only return changed rows/fields.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mSolver]

Thanks for the feedback guys. We are going to try the worksheet_change event to see how that pans out.

Thx again,
Mike

 

[GlennUK]

One query ... will your users ever be deleting entire rows or columns? I don't know if WorkSheet_Change will be any use to you in those situations. Maybe I'm wrong though?!?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[mSolver]

No the worksheets will be protected so that only entry in specific cells is possible. So I hope this is not an issue. I am going to try to write changed cell references in memory instead of a hidden sheet. I just dont want to end up with something slower than what I have now.
Thx,
M

 

[xlbo]

Maybe have a look at using custom properties. A worksheet / cell can have a custom property which you could generate on change and then read from them - would need to check all cells though...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[xlbo]

...unless of course you just create a worksheet property that is a text string of all cells that have had a change. then simply split that string into an array and use that to identify changed cells

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question