Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Software Development Software Development
Search Search
Search for:
Tech Support Guy > > >

VBA Excel: Check if a workbook has been changed


(!)

balloon_tom's Avatar
balloon_tom balloon_tom is offline
Member with 104 posts.
THREAD STARTER
 
Join Date: Jan 2006
Location: RALEIGH, NC
Experience: Beginner
14-Apr-2008, 11:33 AM #1
VBA Excel: Check if a workbook has been changed
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
Chicon's Avatar
Computer Specs
Member with 6,650 posts.
 
Join Date: Jul 2004
Location: 50 34' 07.13" N - 04 10' 23.
Experience: Second socks retriever
14-Apr-2008, 01:53 PM #2
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's Avatar
balloon_tom balloon_tom is offline
Member with 104 posts.
THREAD STARTER
 
Join Date: Jan 2006
Location: RALEIGH, NC
Experience: Beginner
14-Apr-2008, 03:35 PM #3
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.
Chicon's Avatar
Computer Specs
Member with 6,650 posts.
 
Join Date: Jul 2004
Location: 50 34' 07.13" N - 04 10' 23.
Experience: Second socks retriever
14-Apr-2008, 04:31 PM #4
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 ).
Chicon's Avatar
Computer Specs
Member with 6,650 posts.
 
Join Date: Jul 2004
Location: 50 34' 07.13" N - 04 10' 23.
Experience: Second socks retriever
14-Apr-2008, 11:12 PM #5
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.
Zack Barresse's Avatar
Computer Specs
Member with 5,434 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
18-Apr-2008, 11:56 PM #6
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
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑