2 xls files the same, but not the same

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I run a daily backup that uses the file modified dates and file sizes to
determine whether to refresh an archived file. I tested this recently by
doing a binary compare between what should be an exact replica of the
directory, and the actual directory, to my suprise 5 out of 40 excel files
show a difference.

In all 5 files this difference is 6 bytes long, and for two of the files the
difference starts at file offset 0x46c. All files seem to open fine with
excel.

My first guess would be that this is a hash code windows writes to the
physical file once it is opened to handle the event of another excel instance
(or user) opening the same file. The problem is I can't seem to verify this.

Does anyone have any thoughts on what this difference might be? If not, can
you point me somewhere to find the answers I seek.

Thanks in advance,

Ryan
 
Do some searching around for the format of BIFF (BInary File Format) and look
for BIFF5 or BIFF8 - these are the file format descriptions for Excel files.
I'm not sure what the current BIFFx used for say Excel XP, but someone will
probably come along and let us both know.

There's also a very interesting commentary on Excel file formats at this page
http://www.robweir.com/blog/2007/01/formats-of-excel-2007.html
which discusses the radical change in the file formats used in Excel 2007.
 
Excel has two types of referencse, they are:
1. Relative reference
2. Absolute reference

Relative reference:
If you want to find the product of two cell values, Al and B1 in cell C1, A2
and B2 in cell C2, A3 and B3 in cell C3, A4 and B4 in cell C4, you have enter
a formula in cell C1 as follows:
1. Enter "="
2. Press Ctrl and select cell A1 press the * and then select B1.
Your formula should look like this =A1*B1
3. Press Enter
4. Select cell C1
5. Click and drag till C4
you will notice the formula is applied automatically to all cells between C2
to C4. This is done using Relative reference.

Absolute Reference
It is defined as the absolute position of value within a cell using the =$B$4.
If you copy any cell value and paste in a different cell location using the
Paste link option under the Paste dialog box, that value becomes a absolute
reference cell value.

Challa Prabhu
 
As a matter of interest, what does the Ctrl key do for you at step 2? I
don't use it, and I can't see any difference in result.
--
David Biddulph

....
Relative reference:
If you want to find the product of two cell values, Al and B1 in cell C1,
A2
and B2 in cell C2, A3 and B3 in cell C3, A4 and B4 in cell C4, you have
enter
a formula in cell C1 as follows:
1. Enter "="
2. Press Ctrl and select cell A1 press the * and then select B1.
Your formula should look like this =A1*B1
3. Press Enter
4. Select cell C1
5. Click and drag till C4
you will notice the formula is applied automatically to all cells between
C2
to C4. This is done using Relative reference.
Click to expand...
....
 
Excel .xls files store date and userID of the last access (even if no changes
were "saved"). This would account for the minor binary compare differences
you are seeing among files with the same size and date/time stamp.

There is one other potential issue with your scheme. When Excel opens an
..xls file with write access, it immediately changes the date/time stamp to
the current date and time, and only changes them back when the file is closed
normally. Thus if Excel crashes, there is a power glitch to the user's
terminal, etc., the date/time stamp will remain permanently changed even
though there were no changes saved. The only way I know of to avoid this
problem is to set the Read Only attribute for the file.

Jerry
 
Thank you for the responses.

There is something that I should have mentioned in my first post though.

My archive is compressed. The program I use (7zip) to create the compressed
archive lets me specify a base archive. If the modified timestamp and the
file size are identical the already compressed file is copied directly from
the base archive, otherwise it is compressed from the network drive and added
to the archive. This sounds like it would be a fairly complex process to
program, and that makes me a little nervous.

Jerry, I noticed that excel changes the modified date while it is open.
This doesn't affect the backup since it looks for an identical timestamp, I
have verified this. However, this invalidates my last theory about what I
though was happeneing.

My theory was:

1. User opens the file
2. Makes some changes
Excel writes some state information to physical file
3. Backup process runs
4. Person closes the file, without saving changes

Now I realize that the open file would have a different modified date, and
so get added to the days archive. But when it is closed without being saved
the date should revert, and be added to the next days archive since the dates
don't match again.

I don't know if I should worry about this or not. I think my problem
revolves around the question of whether or not excel would ever change the
physical file, without changing the modified date. Maybe if the excel
crashes. I dunno.

Ryan
 
Nevermind, I managed to reproduce it.

1. Make a new excel worksheet
2. Make sure it is closed
3. Make a backup copy of it -> This is what is added to my archive
4. Open it again
5. Change some stuff
6. Close it without saving -> This is the working file
7. Compare them (QVerify works well, and no install)

Voila, the two files show identical modified dates and file sizes and are
for all intents identical, but they are nevertheless different. Excel must
have a loose interpretation of what it means for a file to be modified.

I do see that the Last Accessed date is changed, maybe I can use that, or
have faith that Excel will know what to do.

Thanks for the input

Ryan
 
As far as the loose interpretation of what constitutes a change and what
doesn't - you're correct, it seems to be a pretty simple toggle type thing -
if you change something, even if you then undo it, Excel marks the file as
changed and will give you the save changed file option if you then just try
to close it.

Good luck.
 
Back
Top