Sir Newbie
05-02-2006, 12:09 AM
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).
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
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 :)
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).
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
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 :)