how to check if file is open before sending the data

polina

Registered User.
Local time
Today, 20:08
Joined
Aug 21, 2002
Messages
100
Hi

I export some data into the excel spreadsheet...so what I want to do is to check it the file is open before I do my transfer

Please suggest.

Thanks
 
Here is what I use. Trapping for error # 3010 will allow you to alert the user why the export did not work.

Private Sub bExport_Click()
On Error GoTo Err_bExport_Click

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qselectAuditors", "X:\Auditors.xls", True

Exit_bExport_Click:
Exit Sub

Err_bExport_Click:
If Err = 3010 Then 'Table '???' already exists - Excel file is already opened.
Beep
MsgBox "The '" & Me.cbReports.Column(1) & "' report you attempted to export as an Excel file is already opened on your computer." & vbCrLf & vbLf & "Please close the Excel file before attempting to export the report.", vbCritical, "Excel Export Error"
Else
MsgBox Err.Number, Err.Description
Resume Exit_bExport_Click
End If

End Sub

HTH
 
Ok

Could I also check if the file already exists?
 
Use the Dir function. Check the help files for more options.

If Dir("X:\Auditors.xls") = "" Then
MsgBox "File does not exist"
Else
MsgBox "File does exist"
End IF

HTH
 

Users who are viewing this thread

Back
Top Bottom