Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trapping Events generated by a Worksheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trapping Events generated by a Worksheet
Thanks Bill
"Bill Manville" wrote in message ... Debartsa wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trapping Events generated by a Worksheet
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trapping Events generated by a Worksheet
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 "onedaywhen" wrote in message om... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine 2 worksheet change events | Excel Discussion (Misc queries) | |||
IS THERE A WAY TO CLEAR WORKSHEET EVENTS USING ANOTHER MACRO | Excel Discussion (Misc queries) | |||
Chart Events - Trapping arrow key strokes | Charts and Charting in Excel | |||
Worksheet Events | Excel Discussion (Misc queries) | |||
trapping click events on shapes | Excel Programming |