Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ineste a fixed name read... (2000 sr 1)

    i would want to use this code to know if a file is alreday opene from other user...

    But the name of wbook not is fixed in book2.xls it can assume variouos name 4500.xls. prova.xsl ecc.....
    how to substituite the name book2 with the real name of wbook opened?

    Private Sub Workbook_Open()
    ' Test to see if the file is open.
    If IsFileOpen("c:Book2.xls") Then

    ' If IsFileOpen("c:realname.xls") Then

    ' Display a message stating the file in use.
    MsgBox "File already in use!"
    '
    ' Add code here to handle case where file is open by another
    ' user.
    '
    Else
    ' Display a message stating the file is not in use.
    MsgBox "File not in use!"
    ' Open the file in Microsoft Excel.
    Workbooks.Open "c:Book2.xls"
    '
    ' Add code here to handle case where file is NOT open by another
    ' user.
    '
    End If

    End Sub
    ' This function checks to see if a file is open or not. If the file is
    ' already open, it returns True. If the file is not open, it returns
    ' False. Otherwise, a run-time error occurs because there is
    ' some other problem accessing the file.
    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 by another user.
    Case 0
    IsFileOpen = False

    ' Error number for "Permission Denied."
    ' File is already opened by another user.
    Case 70
    IsFileOpen = True

    ' Another error occurred.
    Case Else
    Error errnum
    End Select

    End Function

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: ineste a fixed name read... (2000 sr 1)

    You can replace the code with a variable. At some time during runtime you will have to determine the name of the file (thru whatever means)

    How is the program going to get the name of the file? Are you going to ask the user?

    Steve

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ineste a fixed name read... (2000 sr 1)

    modified with, but the macro tell me "file is in use" but onlu i have opened this file, i would want to intecept if this file is opoened from other user and not i:

    Sub TestFileOpened()

    ThisWorkbook.Activate

    Dim File_on_server As String

    File_on_server = ActiveWorkbook.Path & "" & ActiveWorkbook.Name

    ' Test to see if the file is open.
    If IsFileOpen(File_on_server) Then
    ' Display a message stating the file in use.
    MsgBox "File already in use!"
    '
    ' Add code here to handle case where file is open by another
    ' user.
    '
    Else
    ' Display a message stating the file is not in use.
    MsgBox "File not in use!"
    ' Open the file in Microsoft Excel.
    Workbooks.Open (File_on_server)
    '
    ' Add code here to handle case where file is NOT open by another
    ' user.
    '
    End If

    End Sub

    ' This function checks to see if a file is open or not. If the file is
    ' already open, it returns True. If the file is not open, it returns
    ' False. Otherwise, a run-time error occurs because there is
    ' some other problem accessing the file.

    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 by another user.
    Case 0
    IsFileOpen = False

    ' Error number for "Permission Denied."
    ' File is already opened by another user.
    Case 70
    IsFileOpen = True

    ' Another error occurred.
    Case Else
    Error errnum
    End Select

    End Function

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ineste a fixed name read... (2000 sr 1)

    You set File_on_Server to the full name of the active workbook, so of course it is open - the active workbook is open by definition. You will have to supply the name of another workbook than the active one. As Steve asked: "How is the program going to get the name of the file? Are you going to ask the user?"

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ineste a fixed name read... (2000 sr 1)

    Edited by HansV to create link to post. It's easy: select the post number <!t>[Post: 536652]<!/t>, copy it (Ctrl+C), and paste it into your post (Ctrl+V)

    tks Hans, but i would want to solve this:
    <post#=536652>post 536652</post#>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ineste a fixed name read... (2000 sr 1)

    So see my reply to that post.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •