1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

VBA Excel: Check if a workbook has been changed

Discussion in 'Software Development' started by balloon_tom, Apr 14, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. balloon_tom

    balloon_tom Thread Starter

    Joined:
    Jan 4, 2006
    Messages:
    104
    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
     
  2. Chicon

    Chicon

    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.
     
  3. balloon_tom

    balloon_tom Thread Starter

    Joined:
    Jan 4, 2006
    Messages:
    104
    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.
     
  4. Chicon

    Chicon

    Joined:
    Jul 29, 2004
    Messages:
    6,650
    For case3 : as Book2 is open, you may export its data into a text file and compare with a previous text file generated from Book2. ( VBA procedures : Exporting And Import Text With Excel ).
     
  5. Chicon

    Chicon

    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.
     
  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,447
    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
     
  7. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/703666