Trying to backup... Sucks.

[Sir Newbie]

G'day...

Thanks for taking a look at my post

Here's the scenario...

A person is using an Excel spreadsheet. When they click on the 'Save' button (which I created) a backup of their spreadsheet is created called 'Backup.xls' in another folder called 'Backup' (or, alternatively, a backup of their spreadsheet is created and replaces the 'Backup.xls' spreadsheet if it already exists).

My objective is to close any spreadsheets that are open (but not the one the person is using) so that the backup is carried out effectively.

I have managed to achieve my objective to a certain extent. The code below closes any other Excel spreadsheets that have been opened, if a person has double clicked on it (i.e. the icon).

[vba]
For Each w In Workbooks
If w.Name <> ThisWorkbook.Name Then
MsgBox "There is an open workbook!"
w.Close savechanges:=True
End If
Next w
[/vba]
But, alas, the code does not close any spreadsheets that have been opened using the following method...

Start > Programs > Excel > File > Open...

The problem may have something to do with the way the files are named by Windows or Excel, when they are open (although I could be entirely incorrect ).

For example, if a spreadsheet is opened by double clicking on it, I noticed that in the Taskbar it is identified only by the name of the spreadsheet (e.g. 'Myspreadsheet') when the mouse is on/over it.

But, if the spreadsheet is opened using the 'Start > Programs > Excel etc' method, in the Taskbar it is identified by the name of the application and the name of the spreadsheet (e.g. 'Microsoft Excel - Myspreadsheet'.

Any help or comments would be greatly appreciated!

Thanks in advance

[stanl]

You may have better luck using the .caption and/or .hwnd properties. Stan

[Sir Newbie]

Hi Stan,

Thanks for the reply.

Good suggestions too.

I would never have thought of caption.

As for '.hwnd', well...

Let's just say that it's time for me to pop back into the lab and work on your advice.

Cheers

[Sir Newbie]

Ummm...

Does anyone know how to use or apply the 'caption' thing that was mentioned above?

I tried the following but it doesn't work.

[VBA]
For Each w In Workbooks
If w.Caption <> ThisWorkbook.Caption Then
MsgBox "There is an open workbook!"
w.Close savechanges:=True
End If
Next w
[/VBA]

As for the 'hwnd' thing, I just don't get it

Thanks in advance

[geekgirlau]

[VBA]
Dim w As Window

For Each w In Application.Windows
If w.Caption <> ActiveWindow.Caption Then
MsgBox "There is an open workbook!" & vbCrLf & vbCrLf & _
vbTab & w.Caption
w.Close savechanges:=True
End If
Next w

[/VBA]

[Sir Newbie]

Thanks geekgirlau

Yep, that's how you use the caption thingy...

Unfortunately, I've still got the same problem overall (e.g. if a person opens the 'Backup.xls' spreadsheet using Start > Programs > Excel > File > Open... It will not close when I use the above code)

Any ideas?

I'm suspecting that no one here knows the answer. Which is ok, I mean I don't know the answer either and this Excel stuff can get pretty tricky!

Oh well, it's back to the drawing board for me.

Thanks once again for replying and also helping !!!

[geekgirlau]

Is this code in the Workbook Before Close event?

[Sir Newbie]

Geez that was quick.

Nup. It's in a command button.

[geekgirlau]

If you add it to the Workbook Before Close event, the macro will run automatically each time you close the workbook.

[mdmackillop]

It sound like you are running two (or more) separate instances of Excel, and the code is only working within your current instance. Try Alt+Tab and see how many Excels you have. I've never tried working beween them in this fashion, but I'm sure it's been done somewhere.

[stanl]

It sound like you are running two (or more) separate instances of Excel, and the code is only working within your current instance. Try Alt+Tab and see how many Excels you have.

I thought so too, that is why I suggested hwnd - http://www.informit.com/articles/art...&seqNum=3&rl=1

Stan