Is file open?

[jsic1210]

Hello all,

In Access, I'm trying to transfer a table to a spreadsheet, but I only want to do it if the spreadsheet file is CLOSED. I have tried a few things that I've read online, but with no luck. So I want to run the following code ONLY if the file Metrics Monthly Metrics Report is closed. Here is my code:

Private Sub cmdCategoryYTD_Click()
'-----------------------------------
'Export Pending to Excel
'-----------------------------------

Dim strLoc As String
Dim strTbl As String
Dim strDest As String

    'File Name
    strLoc = "E:\MIS\"
    strTbl = "tblPending"
    strDest = strLoc & "Metrics\Monthly Metrics Report.xls"


    'Export to Excel
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strTbl, strDest, True

End Sub

I should mention that I want to ensure it's not loaded on anybody's computer. (E:\ is a shared drive)

 

[Beetle]

This code example adapted from this kb article.

Place the following function in a code module in your app;

Function FileLocked(strFileName As String) As Boolean
   On Error Resume Next
   ' If the file is already opened by another process,
   ' and the specified type of access is not allowed,
   ' the Open operation fails and an error occurs.
   Open strFileName For Binary Access Read Write Lock Read Write As #1
   Close #1
   ' If an error occurs, the file is currently open.
   If Err.Number <> 0 Then
      FileLocked = True
      Err.Clear
   End If
End Function

Then call it in your procedure like this;

Private Sub cmdCategoryYTD_Click()
'-----------------------------------
'Export Pending to Excel
'-----------------------------------

Dim strLoc As String
Dim strTbl As String
Dim strDest As String

    'File Name
    strLoc = "E:\MIS\"
    strTbl = "tblPending"
    strDest = strLoc & "Metrics\Monthly Metrics Report.xls"

    If FileLocked(strDest) Then
        MsgBox "File is currently open"
    Else
        'Export to Excel
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strTbl, strDest, True
    End If

End Sub

I did test it on a shared drive on our network where the file was open by another computer on the network.

 

[jsic1210]

Thank you very much! I tested it across computers, too, and it works. That is a huge relief!

----------------------------
Keep Calm and Chive On!

 

[ted.martin]

Can anyone tell me why this line of code

 Open strFileName For Binary Access Read Write Lock Read Write As #1

in the above post creates a file of the same strFileName when it runs. (my file is a .pdf and so will not open anyway as it has no data)

I am now using

 Kill strFileName

to get rid of it but I would prefer the file not to be produced in the first place.

Thanks

 

[vbaInet]

ted.martin, I only use that method for text file types not for pdfs. What's your objective?

 

[Dick7Access]

This code example adapted from this kb article.

.

Where you been, I haven't seen a post from you in a long time? Did I just miss them somehow?
D7A