how to track changes made to a excel sheet using VisualBasic

[Daffo]

Hi everybody, i want to know how to track changes made to a excel sheet
using VB. suppose i open a file by name car3.xls. i make some data
changes, changes in the formula, add data to a cell etc. Now i should
be able to track all these changes made to car3.xls programatically
using VB.

I am able to open car3.xls and access the file and get the list of
formulas in the workbook using VB. But i am not able to track the
changes made to the file programatically using VB. It would like to
avoid using VB macros for this purpose.
Can anyone please suggest me how to start and any usefull links. which
is the best way to accomplish this.


With Regards,
Daffo

 

[Dave Peterson]

I wouldn't know where to start.

But have you looked at Tools|Track Changes.

It requires that the workbook be shared and there are lots of things that are
not available in shared workbooks.

But maybe it'll be sufficient for you.

 

[Daffo]

Hi Dave, i dont want to do it by using Tools| Track Changes. I want to
track changes programatically from VB.

With Regards
Daffo

 

[Dave Peterson]

Good luck.

Hi Dave, i dont want to do it by using Tools| Track Changes. I want to
track changes programatically from VB.

With Regards
Daffo

 

[Daffo]

Hi Dave, thak you.

Good luck.

 

[Dave Peterson]

I didn't mean to sound to flip, but there are a lot of things that the user can
change--values, formulas, formatting, insertion/deletion of ranges (including
whole rows/columns). Insertion/deletion of worksheets. Adding/deleting shapes,
names, charts and graphs, ...

Good gawd, there's a lot of stuff that anyone can do.

========
On the other hand, if your workbook is restricted (no inserting of
rows/columns/ranges) and you only want to find the differences (not keep track),
you could look at how Myrna Larson and Bill Manville did it:

http://www.cpearson.com/excel/whatsnew.htm
look for compare.xla

This does a cell by cell compare (a1 with a1, x99 with x99) and you have to have
a copy of the before to be able to find the changes.

Hi Dave, thak you.

 

[Daffo]

Hi thankyou for the links. i ll check out. Can i open an excel
apllication with events and track the changes based on the events. i am
trying this approach right now.

With regards
Daffo

 

[Dave Peterson]

Chip Pearson has a bunch of information about application events at:
http://www.cpearson.com/excel/AppEvent.htm

But you may find that there are some events that can't be tapped into.

Hi thankyou for the links. i ll check out. Can i open an excel
apllication with events and track the changes based on the events. i am
trying this approach right now.

With regards
Daffo

 

[Daffo]

Hi i am able to capture the events in the excel workbook. But i am
facing a problem. the application doesnot quit.
for example

xlApp.Quit( )

doesnt quit the application.
i tried declaring xlApp as Private and in the next case declared xlApp
as public, but in both the cases xlApp doesnot quit.

Private Sub BeforeBookClose(ByVal Wb As Excel.Workbook, ByRef Cancel As
Boolean)
xlApp.Workbooks.Close()

xlApp.Quit()
MsgBox("closing wbooks")
End Sub

with regards
daffo

 

[Dave Peterson]

Make sure that all your object variables are set to nothing.

And while you're testing, you may want to make the excel application visible.
Then you could see if there is a message that you have to dismiss before the
closing can continue.

ps.

If the application is closed, then no more lines of your code will be executed.
You may want to move the msgbox higher.

Hi i am able to capture the events in the excel workbook. But i am
facing a problem. the application doesnot quit.
for example

xlApp.Quit( )

doesnt quit the application.
i tried declaring xlApp as Private and in the next case declared xlApp
as public, but in both the cases xlApp doesnot quit.

Private Sub BeforeBookClose(ByVal Wb As Excel.Workbook, ByRef Cancel As
Boolean)
xlApp.Workbooks.Close()

xlApp.Quit()
MsgBox("closing wbooks")
End Sub

with regards
daffo

 

[Daffo]

hi Dave thank you.. i wil try out... wat u hav mentioned.

with regards
daffo