Check excel file/excel app is closed before opening

[NZArchie]

In VBA, I am exporting to a template. My checking goes to the extent of checking if a file exists, and getting permission to overwrite:

If Dir(sOutput) <> "" Then ' If output file already exists
    varConfirmOverwrite = MsgBox("The output file name already exists, would you like to overwrite it?", vbOKCancel, "File Already Exists")
        If varConfirmOverwrite <> vbOK Then ' Do not overwrite
            GoTo exit_Here
            Kill sOutput
        Else ' Delete original
          Kill sOutput
        End If
    End If

However it stumbles if the file is open (Filecopy gets its permission denied)

How do I check if a file is open, and forcibly close it if it is?

 

[Jeanette Cunningham]

This is what I do.
Check if the excel report file already exists and if it does I delete it. If I get an error deleting it, my error handler shows the user a message.

--------------------
Public Function DupFileName(strFile As String) As Boolean
On Error GoTo FunctionErr
'check if file exists
If "" <> Dir(strFile) Then
'delete it
Kill (Dir(strFile))
End If
'return true
DupFileName = False


FunctionExit:
Exit Function
FunctionErr:
Select Case Err.Number
Case 70 'the file is open and can't be deleted
'return false
DupFileName = True
MsgBox "This report is already open in excel, you can't open another copy" _
& vbCrLf & vbCrLf _
& "Close the open report and try again", vbInformation, pstrT
Case Else
Debug.Print Err.Number & " " & Err.Description
End Select
Resume FunctionExit
End Function
-------------------

 

[Rank Am]

I tend to use Excel and word as templates and always populate a new workbook from the template with data via automation from from Access, so there not an issue with files being left open, you can have as many as you want.

Public Sub OpenXLT()
Dim objXL As Excel.Application
Dim objWKBK As Excel.Workbook
Set objXL = CreateObject("excel.application")
objXL.Workbooks.Add(enter xlt path and file name here)
objXL.Visible = True
End Sub

I can't think of many instances where I have had to have an populated excel file and the data in the database. Sometimes hardcopies have to be signed and filed - but these are just printed out, and the db updated by the responsible person so that the db or ADO will now only produce read only recordsets for the signed off records. If users want to save local copies in excel to mess about with they can do so.

 

[NZArchie]

This is what I am trying to do. The error comes when copying my template across to the output folder. Is workbooks.add better than workbooks.open?

Why wouldn't my error handler be working? I have an On Error statement at the top, but the code breaks at the call to filecopy, showing the standard VBA error.

 

[NZArchie]

I've found the error handler wasn't working because I had the wrong setting in tools > options > General > Error Trapping