[SOLVED] Need to auto duplicate a 2007 workbook in 97-2003 format

[Wedge120]

I have an Excel 2007 workbook that I use daily, and I typically make a point to save a copy of it as a '97 workbook so that I can use it at my work place (yes, my employer sees no need to upgrade to 2007).

So a dilemma occurs when I forget to save this workbook as a '97 workbook on my PC at home to transport with me to work. One solution is to work strictly in '97 at home, but I do not have it installed on my home PC, nor do I want to have it installed. I like 2007. I want to learn VBA in 2007.

My question: is there a way for my 2007 workbook to automatically duplicate itself as a '97 workbook every time I save changes and/or close my 2007 workbook?

edit: By saving as a '97 workbook, what I mean is saving it as a 97-2003 workbook.

[6StringJazzer]

I believe this does what you want; I tested it. It will always save the workbook in both versions before closing, even if you didn't change it. (This could be a problem if you make changes and decide to abandon them. Closing the file will save them anyway.) The code has to go in the ThisWorkbook module.

Please Login or Register  to view this content.

After adding the code you will have to save your 2007 version as an .xlsm file.

The SaveAs will lop the "m" off the end of the name, and Excel will see the ".xls" extension and save it as a 97-2003 file. To be honest I didn't try to reopen it using Excel 2003 because I have it on a different machine. You should try that to make sure this works for your exact situation.

Edited to add:

Something just occurred to me. When you save it as 97-2003, it will still have this macro in it. We probably want to disable the macro so the other version doesn't try to do the same trick. I am thinking it should be something like this:

Please Login or Register  to view this content.

Also, the user of the 97-2003 version will be notified that it contains macros. Not sure how much of an issue that will be for your users.

[Wedge120]

Where you have:

Please Login or Register  to view this content.

Do I need to place the name of my workbook where "name" is located?
For example, my workbook is named "Phone book.xlsm"

Should it be:

Please Login or Register  to view this content.

[Wedge120]

A little more help needed. I am not sure what belongs in "ThisWorkbook.Name".

[6StringJazzer]

A little more help needed. I am not sure what belongs in "ThisWorkbook.Name".

You use that code exactly as written.

ThisWorkbook is a built-in variable that refers to the workbook that owns the code. Name is an attribute of that workbook. Using

ThisWorkbook.Name

is just like using the actual name of the workbook, but using this code means that the workbook name can change and the code will still work.

[romperstomper]

Why not use the BeforeSave event together with SaveCopyAs rather than saving whenever closed, even if no changes have been made?

[6StringJazzer]

Why not use the BeforeSave event together with SaveCopyAs rather than saving whenever closed, even if no changes have been made?

Why not indeed! Excellent advice. The following code would be used instead of what I provided above:

Please Login or Register  to view this content.

I have tested this.

I forgot about SaveCopy. This approach is transparent to the user.

However, there is still the point that the user of the 97-2003 version will be notified that it contains macros.

[Wedge120]

I will try this when I get home from work.

Will this create a copy in the same directory?

[6StringJazzer]

I will try this when I get home from work.

Will this create a copy in the same directory?

Yes, same directory.

[Wedge120]

However, there is still the point that the user of the 97-2003 version will be notified that it contains macros.

This will not be an issue for me, and I am the only one using this workbook.

Your solution works with a freshly created workbook. However, my current workbook, which contains about 8 sheets of data, is not duplicating for some reason. It is an .xlsm document, so document-type should not be an issue, should it? Are there any other specific attributes about an 2007 Excel workbook (that I can change) that might keep this from working properly?

[broro183]

hi,

I'm not sure about ".savecopyas" (Help file doesn't say much & RS is usually on to it ) but I think that you need to explicitly change the FileFormat if using ".saveas" as well as changing the file name extension. It's only an untested guess & I could be wrong!

Syntax
expression.SaveAs(FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AddToMru, TextCodepage, TextVisualLayout, Local)

FileFormat: The file format to use when you save the file. For a list of valid choices, see the XlFileFormat enumeration. For an existing file, the default format is the last file format specified; for a new file, the default is the format of the version of Excel being used.

Rob

[Wedge120]

Rob might be onto something. I am getting inconsistent results. I can no longer get the "Workbook_BeforeSave" code to work.

edit: I am only able to get this to work a single time. If I re-open the workbook, edit or add data, then save-close, the Excel 97-2003 workbook does not contain the changed data.

[Wedge120]

One final bump for this one...

I am unable to get consistency with the code provided. I can duplicate my workbook once, but once only. Additional edits to the workbook do not get recorded in the duplicate '97-2003 file of the same name.

Can anybody else verify that the code performs for them without fail?

[6StringJazzer]

Here is the mod based on Rob's comment. I have not had time to bounce the files between my two machines (I don't have both versions on the same machine) to exhaustively test, just that I was able to open the .xls file in 2007. I will do my best to do more testing but probably won't be able to for another 12 hours.

Please Login or Register  to view this content.

There is no file format value for 2003, presumably because it's the same format as 97. I am not familiar with whether there are any differences.

[Wedge120]

SaveCopyAs does not take the FileFormat argument.

[romperstomper]

Sorry - I forgot that SaveCopyAs doesn't allow you to change format!
Try this version - I don't have 97 available to test on:

Please Login or Register  to view this content.

[6StringJazzer]

I am unable to get consistency with the code provided. I can duplicate my workbook once, but once only. Additional edits to the workbook do not get recorded in the duplicate '97-2003 file of the same name.

Can anybody else verify that the code performs for them without fail?

I am able to get the version using SaveCopyAs to work consistently and repeatedly. I change data, close the file, I am prompted to save it and I do, and the .xls version is silently saved with the update.

The only thing I can think of that would prevent it is either macros not being enabled, or the code refusing to overwrite an existing file. When I run this, SaveCopyAs silently overwrites the existing copy, so I don't imagine that's the problem.

On re-reading the thread I don't see how switching to SaveAs from SaveCopyAs would solve this problem.

It might be helpful to post your file, and I could see if can reproduce your problem with the same file.

Edit: I am assuming that your 2007 file is being saved as a .xlsm file.

[Wedge120]

I will try your new code after work and report back.

edit: Yes the 2007 file is being saved as an xlsm.

[Wedge120]

This works. Excellent. Thank you very much!

Now, time to study this code in an attempt to understand it.