Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
code to check file size everytime an Excel file is opened | Excel Programming | |||
stop excel file opened as read only if already opened by another u | Excel Programming | |||
Is Workbook read-only because of file permission or because file is already opened? | Excel Programming | |||
Possible to write VBA code to send out an EMAIL everytime a workbook is opened? | Excel Programming | |||
How set file open path to filepath of file opened with Explorer ? | Excel Programming |