RunTime error 70 on Kill(Filepath & Filename)

[Kramxel]

Hi All,

Having an issue I'm struggling (read as 'failing miserably') to solve.

Initially I was connecting to a workbook via an ODA connection - but occasionally this file maybe in use by another machine which due an MS bug/memory leak will open a read-only copy of the file. To avoid this I have created a sub which creates a copy of the file in a temp location runs the connection string and then attempts to delete the file again. When doing this I run into run time error 70 at the Kill command after some research I guess this is because there is still a connection to the file and it appears open. If I change the code so that it kills the file first (as in a copy was created from a previous process) and then hold F5 the code cycles through without problem - I guess the End Sub is breaking the link each time so that the kill command from the next loop through the sub fires. I have tried adding Reset and Close to the MakeCopy function but this doesn't seem to help either.

in short my question is how can I break the connection earlier so I do not have to leave a file needlessly until the next time.

Sub GetAgentManager()
    Dim sSQLQry As String
    Dim ReturnArray, output As Variant
    Dim Conn As New ADODB.Connection
    Dim mrs As New ADODB.Recordset
    Dim DBFilePath, DBFileName, DBPath, sconnect As String
    Dim i, j, k As Integer
    
    With Worksheets("Lists")
        i = Application.WorksheetFunction.Match("AGENTS", .Range("A:A")) + 1
        j = i + Application.WorksheetFunction.CountA(.Range("A:A")) - 3
        .Range(.Cells(i, 1), .Cells(j, 2)).ClearContents
    End With
    
    
    DBFilePath = [rFilePath]
    DBFileName = [rFileName]
        
    DBPath = MakeCopy(CStr(DBFilePath), CStr(DBFileName))
    
    sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';"
       
    Conn.Open sconnect
    sSQLString = "SELECT Agent_Name, Team_Manager From [AgentData$]"
  
    mrs.Open sSQLString, Conn
    
    ReturnArray = mrs.GetRows

    mrs.Close
    
    Conn.Close
    ReDim output(0 To UBound(ReturnArray, 2), 0 To UBound(ReturnArray, 1))
    For i = 0 To UBound(output, 1)
        output(i, 0) = ReturnArray(0, i)
        output(i, 1) = ReturnArray(1, i)
    Next i
    
    Worksheets("Lists").Range("A11").Resize(UBound(output, 1) + 1, UBound(output, 2) + 1).Value = output
    DoEvents
    Kill (DBPath)
End Sub

Function MakeCopy(sFilePath As String, sFileName As String)
    Dim sTempDir As String, sSource As String
    Dim fso As Object
    Set fso = CreateObject("scripting.FileSystemObject")
    
    
    sTempDir = Environ$("temp") & "\" & sFileName
    'Kill (sTempDir) 'if I comment out Kill command from sub above and uncomment this one - code loops without issue
    sSource = sFilePath & "\" & sFileName
    fso.CopyFile sSource, sTempDir, True
    MakeCopy = sTempDir
    
    sFilePath = Empty
    sFileName = Empty
    sTempDir = Empty
    sSource = Empty
    Set fso = Nothing
End Function

*EDIT* if I declare DBPAth as a public string and wrap the first sub in another which then runs the kill command after the end sub of the code ie.

sub One
   call GetAgentManager
   Kill (DBPath)
End sub

it works perfectly, but seems a bit of a clunky workaround - really must be missing something a bit slicker/simpler.