Trapping Events generated by a Worksheet

[debartsa]

Hi Everybody,

I'm looking for a way to accomplish the following:

I have an Excel Worksheet that gets populated with data using VBA which gets
data from an ADO 2.5 Recordset object in memory on the user's workstation.

My goal is to bind the Worksheet to the ADO Recordset i.e. as the user
updates, deletes or creates data existing on the worksheet, the ADO
Recordset gets updated accordingly.

I'm guessing that if I could capture Worksheet events such as an event
firing when a row on the Worksheet is deleted, updated, etc... I could use
this opportunity to sync the Worksheets data to ADO?

I haven't been able to find any events on the Worksheet for Row deletions,
updates or insertions, Do they exist? I'm using Excel 97.

Thanks for any help!
Sam

 

[Bill Manville]

I haven't been able to find any events on the Worksheet for Row deletions,
updates or insertions, Do they exist? I'm using Excel 97.

Updates by user entry: yes - Worksheet_Change

Deletions and Insertions: no specific events. You could try using the
Worksheet_Calculate event if you have a dummy formula referencing the cells
in the range you are interested in.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

 

[debartsa]

Thanks Bill

Updates by user entry: yes - Worksheet_Change

Deletions and Insertions: no specific events. You could try using the
Worksheet_Calculate event if you have a dummy formula referencing the cells
in the range you are interested in.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

 

[onedaywhen]

I would suggest a 'disconnected' approach, instead of trying to trap a
user's changes when they make them. Rather, give the user the data,
let them do whatever they like and tell you when they've finished.
Then you compare the amended data with the recordset and update the
source.

Think of all the things the user can do to the worksheet. They may
change the sort order of the rows so you should loop through the
worksheet row by row and compare them to the recordset, using key
values in its Filter method perhaps. For this reason you'll want to
prevent the user from deleting an Excel column or column heading.

For the recordset, I'd suggest a client side static cursor and batch
optimistic locking. Disconnect the recordset while the user is
changing the data and while you're changing the recordset. When done,
re-connect the recordset and invoke its UpdateBatch method.

 

[debartsa]

Thanks onedaywhen,

I'm actually doing exactly that i.e. using a disconnected recordset. My goal
is to find a painless way to match the users changes on the worksheet to the
disconnected recordset, I figured if a user added or deleted a row of data,
I could trap the event and flag the record as "new" or "deleted" that way
when it came time to sync the recordset to the worksheet it wouldn't be as
messy. Kind of how the disconnect recordset uses a Value, Original Value and
Underlying Value to represent the users changes, the original data first
populated in the recordset, and the snapshot of data from the database once
the query was executed.

If I could extend the three recordset variables to include the Excel Values
as a fourth I'd be set.

Your response looks like the only way to go, I'm only dreading having to
loop through a Range of rows while comparing each to the recordset as I try
to determine if the row exists (than update recordset), doesn't exist (than
add new record to recordset). Than I have to check to see if each of the
records in the recordset exist on the worksheet, if not than that means it
was deleted from the worksheet (than delete from recordset).

Thanks for your help, I appreciate it!
Sam