CODEWITHSUNDEEP
csvfilems-accessvbaexport
Emi
Emi

Reputation: 484

to check if csv file is open from vba

I want to export table in access to csv file. It works fine if the csv file where I want to export the table is closed. But if the file is open, I get no error and neither the table is exported. Is there any way to check if the csv file is open already and if possible to close it??

Upvotes: 2

Views: 2677

Answers (3)

B.Angel
B.Angel

Reputation: 28

Sub MacroName()
   Dim Path As String
   Path = "C:\test.doc"
   If Not FileLocked(Path) Then
      Documents.Open strFileName
   End If
End Sub

Function FileLocked(Path As String) As Boolean
   On Error Resume Next
      Open strFileName For Binary Access Read Write Lock Read Write As #1
   Close #1
      If Err.Number <> 0 Then
         MsgBox "Error #" & Str(Err.Number) & " - " & Err.Description
      FileLocked = True
      Err.Clear
   End If
End Function

Upvotes: 1

HereIAm
HereIAm

Reputation: 53

Found the solution here. VBA Function to Check Whether File or Document Is Open https://support.microsoft.com/en-us/kb/209189

Upvotes: 2

kiks73
kiks73

Reputation: 3758

As reported in this microsoft support page, you can check if a file is read-only:

Sub Example1()

    ' Test to see if the Read-only attribute was assigned to the file.

    If GetAttr("c:\example.csv") And vbReadOnly Then
        MsgBox "File is Read-only"
    Else
        MsgBox "File is not read-only"
    End If

End Sub

If you opened that file, you could always close any file you open.

intFile = FreeFile()
Open "c:\example.csv" For Output As #intFile
Write #intFile
Close #intFile

If you don't specify the file name in the Close statement, you will close all the files that you opened.

If the file is opened by another application, I don't know if there is a way to close it.

Upvotes: -1

Related Questions