VBA Excel: Check if a workbook has been changed

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

balloon_tom

Thread Starter
Joined
Jan 4, 2006
Messages
107
This is a followup to my "Data transfer between 2 different open Apps" thread, which is now functioning...sort of. I have not found a way to access the data from the workbook which is opened in the second application. I can only reopen it from it's last saved location, which is OK, if I know that the workbook has not been changed since it was last saved. Part of what I'm doing uses a IsFileAlreadyOpen function (kernal32) as recomended to me in that thread.

QUESTION: I would like to know if an open workbook has been changed since it was last saved. This workbook is in a different app. Is there a function (perhaps kernal32) that can be called to give me the changed state of a workbook?
Thanks,
Tom
 
Joined
Jul 29, 2004
Messages
6,650
Hi balloon_tom,

Via a FileSystemObject, you may use the DateLastModified property in order to get the date and the time where the file was modified for the last time.
 

balloon_tom

Thread Starter
Joined
Jan 4, 2006
Messages
107
This is one of the tests which I am currently performing. When I copy the data from Book2 to Book1, I save the DateLastModified property of Book2 in a cell in Book1. When my program runs the next time, I check to see if Book2 is open and again get the DateLastModified of Book2.

Case1: If Book2 is NOT open and the DateLastModified is the same as the saved datelastmodified, then I know the data has not changed, thus the earlier copied data is valid. No problem.

Case2: Book2 is NOT open and the 2 dates do not match, then the data has changed, so I simply open Book2 and refresh the copy. No problem.

Case3: Book2 IS open and the 2 dates match. Book2 could have been modified but not yet saved. In this instance, the user must first save the changes to Book2 before Book1 can access the changed data. These workbooks are in different apps so Book1 can only open a saved version, Book1I cannot see the currently open Book2. Here, I would like to check the changed status of the already open Book2. If the workbook has not been changed, then the last saved data is valid. If the workbook has been changed, then the user is instructed to save Book2, then proceed.
 
Joined
Jul 29, 2004
Messages
6,650
Edit : Sorry, I forgot that Book2 was used by another application. Therefore, just ignore my prior post.

For case3 : the problem is that App2 must tell App1 it has already started to write something in Book2.
The simplest way to do that is to use a semaphore. When App2 starts to write something in Book2 for the first time, it drops a small text file. With that file, App1 will know App2 has started to update Book2.
 
Joined
Jul 25, 2004
Messages
5,458
Just check the Workbook.Saved property. If anything has been changed, it will be set to False, otherwise it will be True. No need for APIs or FSOs.

HTH
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top