0

I'm having trouble working out why a bit of code that I found months and months ago (don't know originating website) is doing such a weird thing when trying to save the file...

In the main part of the code that is constantly looping it has the following:

Main Snippit

    path = "C:/"
    filename = "file.csv"

    If Dir(path) = "" Then
        FullPath = Environ("USERPROFILE") & "\Desktop\" & filename
    Else
        FullPath = path & filename
        If Dir(FullPath) <> "" Then
            isCsvOpen = IsFileOpen(FullPath)
            If Not isCsvOpen Then Kill (FullPath)
        End If
    End If

    If Not isCsvOpen Then outputtext delimiteddata, FullPath

It has the following two functions elsewhere in the code:

outputtext()

Function outputtext(text As String, path As String)
    Dim MyFile As String, fnum As String
    MyFile = path
    fnum = FreeFile() ' Get a free file number.
    Open MyFile For Output As fnum  'open file for output
    Print #fnum, text ' Print when you want the string without quotation marks
    Close #fnum
End Function

IsFileOpen()

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
    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

        Case Else
            Error errnum
    End Select

End Function

What is the purpose of using IsFileOpen() and outputtext(), surely just saving with overwrite would be sufficient?

To me it looks like it's checking if the file is open, locking it open, then killing the file (unlocking it) and then saving...

Any explination would be much appreciated

Ryflex
  • 5,559
  • 25
  • 79
  • 148
  • 1
    You cannot kill or overwrite a file which is open in another program - it will be locked by that process. – Tim Williams Jul 09 '14 at 00:19
  • Question has been asked before, and prior answers include logic for wanting to do this. As per my comment under Sid's answer *Although in my case when I tried something similar the time overhead of actually opening a large model hosted on a overseas server was around 2-3 minutes. Which gave a "grrr" moment when it opened readonly, whereas Sid's function above gave an immediate response. FWIW Bob Phillips listed a similar function at vbaexpress , a more advanced version waiting for the book to be closed elsewhere from Chip Pearson* – brettdj Jul 09 '14 at 01:16

1 Answers1

0

A few reasons for such a design...

  1. If the file is open elsewhere, it likely to be open in a non-Read-only mode (which is what is being checked, i.e. whether the file is locked or not). This is especially important over network drives.

  2. If the file is open and your code tries to save it, it will throw an error. So you can either

    • try to handle the error once it's thrown, or
    • preempt the problem and throw a controlled error at the user or prompt for further action, etc.

  3. This is a cleaner way to avoid file corruption especially over shared resources.

hnk
  • 2,216
  • 1
  • 13
  • 18
  • The code has the line `If Not isCsvOpen Then outputtext delimiteddata, FullPath` surely that means it's still open, but yet it still tries to save? Why not just simply issue a kill and then save? – Ryflex Jul 09 '14 at 00:40
  • it is deleting the file with the command `Kill FilePath` if nobody's using it, to create a new copy in its place with the same name – hnk Jul 09 '14 at 00:44
  • if the user wanted to append it, then this is not necessary, but since the user wants to replace the contents of the entire file, this is cleaner, especially if the filewriting operation is going to take long and in the meanwhile you don't want any access attempts off a common drive. – hnk Jul 09 '14 at 00:46
  • I think I understand, could I ask if you'd be kind enough to comment the main bit of code or if possible all 3 parts? – Ryflex Jul 09 '14 at 01:02
  • ?? Why would you want that?? Either case I'm using a phone to access this and the comments here should be sufficiently clear – hnk Jul 09 '14 at 01:09
  • I think the bit that's confusing me is the whole `Is Not isCsvOpen` bits on both, the line `If Not isCsvOpen Then Kill (FullPath)` isn't a boolean by default false so saying `If Not` doesn't that mean the same as `If IsCsvOpen = true` which essentially means it deletes the file if it's open... – Ryflex Jul 09 '14 at 04:24