Export to Excel fails if target already open

[TrekBiker]

I'm using this routine to send query data to Excel. Everything works fine except if the resulting spreadsheet is already open, when I get Run-time Error 70, Permission Denied. How can I prevent this and perhaps say the spreadsheet is already open, do you want to overwrite it?

Private Sub cmdExportAllActivities_Click()

    Dim FolderLocation As String
    Dim FileLocation As String
    Dim DirLength As Integer
 
'   Create the target file location   
    FolderLocation = "c:\temp"
    FileLocation = FolderLocation & "\Output All.xlsx"
    DirLength = Len(Dir(FolderLocation, vbDirectory))
    
'   If the folder doesn't exist, create it
    If DirLength = 0 Then
        MkDir FolderLocation
    End If
    
'   Check if the ouput file already exists, if not create it, if so kill it and recreate
    If Len(Dir(FileLocation)) = 0 Then
        DoCmd.TransferSpreadsheet acExport, , "qryInvoiceTotals", FileLocation, True
    Else
        Kill (FileLocation)
        DoCmd.TransferSpreadsheet acExport, , "qryInvoiceTotals", FileLocation, True
    End If

'   Get control of the output spreadsheet
    Dim XLapp As Object
    Dim objSheet As Excel.Worksheet
    Dim objWkb As Excel.Workbook

    Set XLapp = CreateObject("Excel.Application")
    XLapp.Visible = True
    XLapp.Workbooks.Open FileLocation, True, False
    Set objSheet = XLapp.ActiveWorkbook.Worksheets(1)
        
'   Start formatting the spreadsheet
    objSheet.Columns("A:Z").EntireColumn.AutoFit

'   Tidy up
    Set objSheet = Nothing
    Set XLapp = Nothing

End Sub

 

[TrekBiker]

OK, resolved by adding error trapping to warn if the output file is already open.