[RESOLVED] Permission denied

[danasegarane]

Hi All,
I am using this method reading Excel .

Private Sub Command1_Click()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
 Sql = "Select * from [Call Summary$] Where F2 Is Not Null And F3 Is Not Null and F4 Is Not Null "
        ConnectionString = GetDriverString("D:\NewSampleFile.xls", "xls")
        Set cn = New ADODB.Connection
            With cn
                .ConnectionString = ConnectionString
                .Open
                If .State = 1 Then
                    Set rs = New ADODB.Recordset
                    cn.CursorLocation = adUseClient
                    rs.Open Sql, cn, adOpenDynamic, adLockOptimistic
                    rs.Close
                End If
            End With
            cn.Close
            Set rs = Nothing
            
          Set cn = nothing
    Dim fso As New FileSystemObject
    fso.MoveFile "D:\NewSampleFile.xls", "d:\test\"
End Sub
Public Function GetDriverString(ByVal sFileName As String, ByVal Extension As String) As String
    Dim strProvider As String
    Select Case Extension
    
        Case "xls"
            strProvider = "Provider=MSDASQL;DSN=Excel Files;Uid=;Pwd=;Dbq=" & sFileName & ";"
            GetDriverString = strProvider
                
        Case Else
            'Do nothing
        End Select

End Function

And during the Moving File I get the Permission denied Error. Any ideas ?

[Joacim Andersson]

The connection is probably not closed yet. Try adding a DoEvents call before the MoveFile call.

[LaVolpe]

Do you get the error if you unrem and fix the line (typo in your pose): Set cn = Nothing

While file is opened in a connection I don't think there is any way you can move the file.

Edited: A DoEvents may be the ticket as Joacim pointed out. But recommend setting cn = Nothing too.

[danasegarane]

Never Mind it worked...

The thing was that One Excel instance was locking the file in the background . After I killed that instance using Process Manager it worked....

Thanks for your time


LaVolpe
Joacim Andersson