How to error trap FileDialog for File In Use

[essaytee]

I cannot error trap a FileDialog routine, where the user selects an Excel spreadsheet for the purposes of copying the contents of the spreadsheet into a a table.

If the user selects an Excel file that is already open in Excel, the FileDialog messagebox appears advising that the file is in use and for the user to close the other application. The problem is, I cannot trap for that error using the 'On Error' handling routine. I want to log and record the fact the Excel file was already open and should be closed in the other application. Sample code below:

Private Sub cmd_Import_Click()
On Error GoTo Error_In_Code

    Dim strFileName As String
    Dim dlg As FileDialog
    Dim rst As Recordset
    Dim strTable As String
    Dim lngCount As Long
    Dim strSqlDelete As String
    Dim strMsg As String
                
    strTable = "tbl_Brief_Charges_Spreadsheet"
    strSqlDelete = "Delete * from " & strTable & ";"
    
    Set dlg = Application.FileDialog(msoFileDialogFilePicker)
    
    With dlg
        .Title = "Select the Excel file to import"
        .AllowMultiSelect = False
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xlsx", 1
        .Filters.Add "All Files", "*.*", 2
                
        If .Show = -1 Then
            strFileName = .SelectedItems(1)
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strFileName, True
            
            Set rst = CurrentDb.OpenRecordset(strTable, dbOpenDynaset)
            lngCount = NumberOfRecords(rst)
            
            If lngCount > 0 Then
                strMsg = "You are about to import " & Str(lngCount) & " Charges, Proceed."
                
                If MsgBox(strMsg, vbYesNo, "Spreadsheet Import") = vbYes Then
                    AppendCharges (BuildSQL_AppendCharges())
                    
                    ' Delete temporary imported charges, they have already been added to the brief
                    CurrentDb.Execute strSqlDelete
                    
                    LogImportSession "Import", "", strFileName, lngCount
                    
                Else
                    ' empty the temporary charges table
                    CurrentDb.Execute strSqlDelete
                    
                    LogImportSession "Import Cancelled", "", strFileName, 0
                    
                End If
            
            Else
                ' no charges have been imported, notify the user
                strMsg = "No charges have been imported, check the spreadsheet"
                MsgBox strMsg, , "Spreadsheet charges"
                
                LogImportSession "Import", "Empty spreadsheet", strFileName, 0
            
            End If
            
        Else
            LogImportSession "Import", "Spreadsheet file not found", "", 0
            
        End If
    End With
    
Exit_Code:
    Exit Sub
    
Error_In_Code:

    Select Case Err.Number
    
    Case 2391 ' Field doesn't exist
        MsgBox "The spreadsheet you selected is in the wrong format, the row headings are not correct.   Download again from #######"
        LogImportSession "Import Error", "Spreadsheet wrong format", strFileName, 0
        
    Case Else
        MsgBox Err.Number & " " & Err.Description
        LogImportSession "Import Error", Err.Number & " " & Err.Description, strFileName, 0
    End Select
    
    Resume Exit_Code

End Sub

Any advice appreciated, it's been a while since I last tinkered with my application.

 

[Gasman]

How to select the file which is already opened using OpenFileDialog

I am trying to select the file which is already opened in quickbook software. code : OpenFileDialog ofdBrowseVInv = new OpenFileDialog(); ofdBrowseVInv.Title = "Locate QuickBook Co...

stackoverflow.com

 

[Bennet]

A ghetto solution would be to copy the selected file to C:\Temp\Tempfile.xlsx (or wherever) and read the contents from there, then delete it.

 

[Gasman]

A ghetto solution would be to copy the selected file to C:\Temp\Tempfile.xlsx (or wherever) and read the contents from there, then delete it.

And if that file is being updated, you would lose all the updates?

 

[Bennet]

Yep! Something the OP would have to find a way to manage... Maybe it would be immediately obvious to the user. Who knows. Just a thought.

 

[Minty]

I always check if a file is open first before trying to delete/write/open to it?

Function IsFileOpen(fileName As String)
    Dim filenum As Integer, errnum As Integer

    'Firstly check there is a file to check ;)
    If Not FileExists(fileName) Then
        IsFileOpen = False        'doesn't exist so therefore it can't be open
        Exit Function
    End If

    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

Which relies on this function:

Function FileExists(ByVal strFile As String, Optional bFindFolders As Boolean) As Boolean
    'Purpose:   Return True if the file exists, even if it is hidden.
    'Arguments: strFile: File name to look for. Current directory searched if no path included.
    '           bFindFolders. If strFile is a folder, FileExists() returns False unless this argument is True.
    'Note:      Does not look inside subdirectories for the file.
    'Author:    Allen Browne. http://allenbrowne.com June, 2006.
    Dim lngAttributes    As Long

    'Include read-only files, hidden files, system files.
    lngAttributes = (vbReadOnly Or vbHidden Or vbSystem)

    If bFindFolders Then
        lngAttributes = (lngAttributes Or vbDirectory)        'Include folders as well.
    Else
        'Strip any trailing slash, so Dir does not look inside the folder.
        Do While Right$(strFile, 1) = "\"
            strFile = Left$(strFile, Len(strFile) - 1)
        Loop
    End If

    'If Dir() returns something, the file exists.
    On Error Resume Next
    FileExists = (Len(Dir(strFile, lngAttributes)) > 0)

End Function

 

[gemma-the-husky]

I presume the file dialog sub does not generate a run time error.

The process is clearly built carefully and just returns a code to indicate success or failure.

So on error does not get triggered.

 

[cheekybuddha]

How to select the file which is already opened using OpenFileDialog

I am trying to select the file which is already opened in quickbook software. code : OpenFileDialog ofdBrowseVInv = new OpenFileDialog(); ofdBrowseVInv.Title = "Locate QuickBook Co...

stackoverflow.com

That is a C# example - is that what you intended?

 

[Gasman]

Well the filedialog would still have the same property, would it not, that was my thinking?

ValidateNames = <span>false</span>;

Hmm, the compiler does not object, but falls over with invalid property?
So, my bad. Sorry.

 

[LarryE]

You might be able to use the Windows API to determine if EXCEL is running BEFORE the FileDialog is opened and if it is, then warn the user and log the event and then close EXCEL. I am sorry I can't tell you how to do that, but I know the Windows API has the capability to determine what windows are currently open. It could be one option. Perhaps someone here in the forum who is familiar with the Window API can give you more advice on that.

 

[LarryE]

I found this Function that determines what process is running:

Public Function IsProcessRunning(Process As String) As Boolean
    Dim objList As Object
    Set objList = GetObject("winmgmts:").ExecQuery("select * from win32_process where name='" & Process & "'")
    IsProcessRunning = objList.Count > 0
End Function

You can then determine if EXCEL is running and if it is, close it with this Function:

Public Function IsExcelRunning()
    If IsProcessRunning("Excel.exe") = True Then
       'Close EXCEL window
        If Not GetObject(, "Excel.Application") Is Nothing Then
            GetObject(, "Excel.Application").Quit
        End If
    End If
End Function

I would call IsExcelRunning BEFORE you open your File Dialog so if Excel is running, it will close first before continuing.

 

[cheekybuddha]

@LarryE - this might not help if another user on another computer has the file open?

 

[sonic8]

I know the Windows API has the capability to determine what windows are currently open. It could be one option. Perhaps someone here in the forum who is familiar with the Window API can give you more advice on that.

The Restart Manager API is quite useful for this type of task if the file in question is locked on the same computer.

If you prepared to spend a small amount of money on the problem, you could use my commercial, ready-made FileLockAnalyzer component to save yourself implementing the above mentioned API.

 

[gemma-the-husky]

What's wrong with just telling the user that the file is open, and try again later.

 

[LarryE]

What's wrong with just telling the user that the file is open, and try again later.

Nothing, but the OP wants to capture and log the event first for some unknown reason at the time the error message occurs. I thought the OP could capture it before the File Dialog opens, log it as an error and move on. This procedure identifies any Excel window that is open on the users PC, whether it is the one they want to import from or not. It looks for EXCEL.exe. Don't know why they want to log the error event.

 

[CJ_London]

@essaytee - why do you care if the file is open from another user? What do you want to happen if it is? Abort whatever you are doing? Ignore the message and carry on? Something else?

If you use sql rather than transferspreadsheet to import then any ‘in use’ message will not occur

 

[gemma-the-husky]

Nothing, but the OP wants to capture and log the event first for some unknown reason at the time the error message occurs. I thought the OP could capture it before the File Dialog opens, log it as an error and move on. This procedure identifies any Excel window that is open on the users PC, whether it is the one they want to import from or not. It looks for EXCEL.exe. Don't know why they want to log the error event.

Well excel being open doesn't mean it's viewing the excel file you want. It would more sense to log the error as part of the process when the file dialog does not succeed. You surely can't assume it's safe to kill excel even if you can identify the active process.

 

[LarryE]

It would more sense to log the error as part of the process when the file dialog does not succeed.

How? That's exactly what the OP was complaining about. He couldn't capture the error.

 

[gemma-the-husky]

How? That's exactly what the OP was complaining about. He couldn't capture the error.

I see what you mean. I presume in that case that application.filedialog just selects the required disk file. I thought he was calling a process to actually open the file, but he is using transferspreadsheet for that.

@Minty showed a simple way of testing the file first to see if it is in use.

 

[essaytee]

Thanks all, sorry for my delay in responding, other stuff happened over the last few days. Anyway, yeah, I'm not concerned if the Excel program is running or not, though if an Excel spreadsheet is open, then obviously Excel is running.

I want to log the success and failures of a new feature that has been requested by the users, and in so doing, in time, I'll have an idea of how it's actually used.

Your suggestions are useful and they bring back memories from years ago and I reckon I've got some of those functions in my bag of tricks. Yes, it's been quite a while since I last dabbled in coding.