Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple instances of the same file being opened
What happens:
A file on a network drive - call it "one", which when opened creates a file on the local drive - call it "two", & drops all holds on file "one" in doing so. File "two", when printed, updates file "one" then file "two" in that order leaving file "two" open on the local drive until closed. The premise: File "one" is left available to all users with no restrictions at any time and is updated along with "two" as changes are made. The reason for doing it this way is because some users do not relinquish control of file "one" for days at a time which is totally unacceptable. The problem: A means of eliminating multiple instances of file "two" being opened on one computer whether it is in the same instance of excel or separate instances of excel. Remember that file "one" creates an instance of file "one" as it is opened. The solution?: '=================================== Function IsFileOpen(filename As String) Dim filenum As Integer, errnum As Integer On Error Resume Next ' Turn error checking off. filenum = FreeFile() ' Get a free file number. ' Attempt to open the file and lock it. Open filename For Input Lock Read As #filenum Close filenum ' Close the file. errnum = Err ' Save the error number that occurred. On Error GoTo 0 ' Turn error checking back on. ' Check to see which error occurred. Select Case errnum ' No error occurred. ' File is NOT already open. Case 0 IsFileOpen = False ' Error number for "Permission Denied." ' File is already. Case 70 IsFileOpen = True ' Another error occurred. Case Else Error errnum End Select End Function Private Sub Workbook_Open() '===================================== Dim MyFile As String ' Returns "one.xls" if it exists. Checks is "one" exists ' skipping the remaining checks if it does not MyFile = Dir("c:\one.xls") If MyFile = "" Then MsgBox "File does not exist." GoTo ThatsAll End If '===================================== ' Test to see if the file is open. If IsFileOpen("c:\one.xls") Then ' Test to see if the file is open in this instance of excel Dim bk As Workbook On Error Resume Next Set bk = Workbooks("one.xls") On Error GoTo 0 If Not bk Is Nothing Then 'MsgBox(prompt[, buttons] [, title] [, helpfile, context]) MsgBox "Please use the previously opened file. This file will close after clicking OK" ThisWorkbook.Close True ' close the file without saving GoTo secondcheck End If ' Display a message stating the file in use. MsgBox "File is already open" Application.Quit ' close Excel ' secondcheck: ' '===================================== Workbooks.Open "c:\two.xls" ThatsAll: End If '=========================== End Sub The questions: I have tested this and as near as I am able to tell the code will do the job.... But... will this work for all possibilities and is there better ways of accomplishing the same thing? Please help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple instances of the same file being opened
Have you looked into setting the workbook as a shared workbook, so as when
the workbook is opened, the first person to open the workbook is not the only person to have control of it, but others can also edit the file. One thing though that you may want to have in effect is tracking changes. Check it out under Tools, then both, "Share Workbook..." and "Track Changes" -- Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "flashpoint" wrote in message ... What happens: A file on a network drive - call it "one", which when opened creates a file on the local drive - call it "two", & drops all holds on file "one" in doing so. File "two", when printed, updates file "one" then file "two" in that order leaving file "two" open on the local drive until closed. The premise: File "one" is left available to all users with no restrictions at any time and is updated along with "two" as changes are made. The reason for doing it this way is because some users do not relinquish control of file "one" for days at a time which is totally unacceptable. The problem: A means of eliminating multiple instances of file "two" being opened on one computer whether it is in the same instance of excel or separate instances of excel. Remember that file "one" creates an instance of file "one" as it is opened. The solution?: '=================================== Function IsFileOpen(filename As String) Dim filenum As Integer, errnum As Integer On Error Resume Next ' Turn error checking off. filenum = FreeFile() ' Get a free file number. ' Attempt to open the file and lock it. Open filename For Input Lock Read As #filenum Close filenum ' Close the file. errnum = Err ' Save the error number that occurred. On Error GoTo 0 ' Turn error checking back on. ' Check to see which error occurred. Select Case errnum ' No error occurred. ' File is NOT already open. Case 0 IsFileOpen = False ' Error number for "Permission Denied." ' File is already. Case 70 IsFileOpen = True ' Another error occurred. Case Else Error errnum End Select End Function Private Sub Workbook_Open() '===================================== Dim MyFile As String ' Returns "one.xls" if it exists. Checks is "one" exists ' skipping the remaining checks if it does not MyFile = Dir("c:\one.xls") If MyFile = "" Then MsgBox "File does not exist." GoTo ThatsAll End If '===================================== ' Test to see if the file is open. If IsFileOpen("c:\one.xls") Then ' Test to see if the file is open in this instance of excel Dim bk As Workbook On Error Resume Next Set bk = Workbooks("one.xls") On Error GoTo 0 If Not bk Is Nothing Then 'MsgBox(prompt[, buttons] [, title] [, helpfile, context]) MsgBox "Please use the previously opened file. This file will close after clicking OK" ThisWorkbook.Close True ' close the file without saving GoTo secondcheck End If ' Display a message stating the file in use. MsgBox "File is already open" Application.Quit ' close Excel ' secondcheck: ' '===================================== Workbooks.Open "c:\two.xls" ThatsAll: End If '=========================== End Sub The questions: I have tested this and as near as I am able to tell the code will do the job.... But... will this work for all possibilities and is there better ways of accomplishing the same thing? Please help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple instances of the same file being opened
On Mon, 8 Sep 2003 12:20:03 -0400, "Ronald Dodge"
wrote: Have you looked into setting the workbook as a shared workbook, so as when the workbook is opened, the first person to open the workbook is not the only person to have control of it, but others can also edit the file. One thing though that you may want to have in effect is tracking changes. Check it out under Tools, then both, "Share Workbook..." and "Track Changes" I have to confess I have not for the simple reason I am trying to circumvent "crop up problems". You know .. those problems that crop up due to usage and lack of understanding or perhaps thru the desire to prove someting is not 'bullet proof'. I'll admit I'm not sure I want to proceed down any path that requires any user intervention & / or decision making other than the absolute minimium. While the users are quite capable of using a computer thier skill levels do vary. Another consideration is network traffic. By keeping file access and manipulation over the network to a minimium traffic is reduced and service is enhanced (I think). Right or wrong I do not want to tie up server resources by allowing a file to be 'accessed' by a user for an excessive time frame. And yet, having said all this I am unable to shake the feeling that I have overlooked something fundamental and simple. Later..... Cal..... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
add multiple x values to multiple instances of y coordinate | Charts and Charting in Excel | |||
Multiple instances of Excel (Multiple workbooks) | Setting up and Configuration of Excel | |||
Create list of unique instances from list of multiple Instances | Excel Worksheet Functions | |||
Opening file in Excel 2003 opens multipule instances of same file | Excel Discussion (Misc queries) | |||
cannot open the excel file, the file is already opened | Excel Discussion (Misc queries) |