Over write a file someone has left opened.
 

NOTE: This is in a network environment.

This sub is part of an end of day macro in this “master file” whose
name is changed each day based on the date. The macro hides some of
the command buttons, and gives it a “generic” file name. The purpose
is so that anybody can access the data from their desk without opening
up the master file. Also, everyone can have an icon on their desktop
of this file whose name remains the same each day. The purpose of this
“generic” file is read-only.

Question: is there code I can use in this sub that will allow this
generic file to save over the older version during this end of day
report, EVEN if someone has left the generic file up on their
computer?

Right now, if someone has the generic file opened, the person running
this report will get an error when it tries to overwrite the older
version of this generic file. I want to keep this generic file name
the same each day so that the icon on everyone’s desktop will work
each day. The error stops the macro, which causes a couple other
routines left undone.
Thanks for your help.

Sub SaveToGenericName()

'This will save-as to make a generic file name for others to access
via bookmark.
'Work Save-as

'This will hide sheets not needed in the generic file.
With Worksheets(Array("Touches", "Notes", "Add-delete history", "Graph
Data", "Cycle Count")).Visible = xlSheetHidden
End With

‘This will hide command buttons not needed in generic file.
With Sheets("Menu")
.CommandButton3.Visible = False
.CommandButton4.Visible = False
'.CommandButton5.Visible = False
.CommandButton7.Visible = False
.CommandButton9.Visible = False
.CommandButton8.Visible = True
End With

‘This will remove contents in column O that is not needed in the
generic file.
Worksheets("Menu").Activate
Columns("I:O").Select
Selection.ClearContents

Range("A1").Select

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs _
Filename:="\\ceddfssrv01\cedroot\public\furniture staging list" &
"\Current Furniture Staging List.xls"

Application.DisplayAlerts = True

End Sub

Over write a file someone has left opened.
 

AFAIK it can't be done.
however, you could insert a macro into that generic workbook that
automatically closes it after a certain amount of time of inactivity -
i know i've seen threads on this (if you search the newsgroup) but i
don't know if it's been made to work successfully.
just an idea.
:)
susan


On Jun 2, 9:54*am, excelnut1954 wrote:
NOTE: This is in a network environment.

This sub is part of an end of day macro in this “master file” whose
name is changed each day based on the date. The macro hides some of
the command buttons, and gives it a “generic” file name. The purpose
is so that anybody can access the data from their desk without opening
up the master file. Also, everyone can have an icon on their desktop
of this file whose name remains the same each day. The purpose of this
“generic” file is read-only.

Question: is there code I can use in this sub that will allow this
generic file to save over the older version during this end of day
report, EVEN if someone has left the generic file up on their
computer?

Right now, if someone has the generic file opened, the person running
this report will get an error when it tries to overwrite the older
version of this generic file. I want to keep this generic file name
the same each day so that the icon on everyone’s desktop will work
each day. The error stops the macro, which causes a couple other
routines left undone.
Thanks for your help.

Sub SaveToGenericName()

'This will save-as to make a generic file name for others to access
via bookmark.
'Work Save-as

'This will hide sheets not needed in the generic file.
With Worksheets(Array("Touches", "Notes", "Add-delete history", "Graph
Data", "Cycle Count")).Visible = xlSheetHidden
End With

‘This will hide command buttons not needed in generic file.
With Sheets("Menu")
* * * * .CommandButton3.Visible = False
* * * * .CommandButton4.Visible = False
* * * * '.CommandButton5.Visible = False
* * * * .CommandButton7.Visible = False
* * * * .CommandButton9.Visible = False
* * * * .CommandButton8.Visible = True
* *End With

‘This will remove contents in column O that is not needed in the
generic file.
* * * *Worksheets("Menu").Activate
* * * * Columns("I:O").Select
* * Selection.ClearContents

* * Range("A1").Select

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs _
Filename:="\\ceddfssrv01\cedroot\public\furniture staging list" &
"\Current Furniture Staging List.xls"

Application.DisplayAlerts = True

End Sub

Over write a file someone has left opened.
 

On Jun 2, 10:23*am, Susan wrote:
AFAIK it can't be done.
however, you could insert a macro into that generic workbook that
automatically closes it after a certain amount of time of inactivity -
i know i've seen threads on this (if you search the newsgroup) but i
don't know if it's been made to work successfully.
just an idea.
:)
susan

On Jun 2, 9:54*am, excelnut1954 wrote:



NOTE: This is in a network environment.

This sub is part of an end of day macro in this “master file” whose
name is changed each day based on the date. The macro hides some of
the command buttons, and gives it a “generic” file name. The purpose
is so that anybody can access the data from their desk without opening
up the master file. Also, everyone can have an icon on their desktop
of this file whose name remains the same each day. The purpose of this
“generic” file is read-only.

Question: is there code I can use in this sub that will allow this
generic file to save over the older version during this end of day
report, EVEN if someone has left the generic file up on their
computer?

Right now, if someone has the generic file opened, the person running
this report will get an error when it tries to overwrite the older
version of this generic file. I want to keep this generic file name
the same each day so that the icon on everyone’s desktop will work
each day. The error stops the macro, which causes a couple other
routines left undone.
Thanks for your help.

Sub SaveToGenericName()

'This will save-as to make a generic file name for others to access
via bookmark.
'Work Save-as

'This will hide sheets not needed in the generic file.
With Worksheets(Array("Touches", "Notes", "Add-delete history", "Graph
Data", "Cycle Count")).Visible = xlSheetHidden
End With

‘This will hide command buttons not needed in generic file.
With Sheets("Menu")
* * * * .CommandButton3.Visible = False
* * * * .CommandButton4.Visible = False
* * * * '.CommandButton5.Visible = False
* * * * .CommandButton7.Visible = False
* * * * .CommandButton9.Visible = False
* * * * .CommandButton8.Visible = True
* *End With

‘This will remove contents in column O that is not needed in the
generic file.
* * * *Worksheets("Menu").Activate
* * * * Columns("I:O").Select
* * Selection.ClearContents

* * Range("A1").Select

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs _
Filename:="\\ceddfssrv01\cedroot\public\furniture staging list" &
"\Current Furniture Staging List.xls"

Application.DisplayAlerts = True

End Sub- Hide quoted text -

- Show quoted text -

Thanks Susan. I'll search the old posts for some ideas. Maybe I can
have the generic file create another name when it's opened.
I appreciate your help.
j.o.