How to prevent Run time error '2302': Microsoft Office Access can't 1

[GarHeard]

If one user of my application clicks on a button to create an Excel file report, my Access application works fine.

However, if that user has the Excel file open and then another uses clicks on a button to create yet another Excel file, I get the error message as follows:

Run time error '2302': Microsoft Office Access can't save the output data to the file you've selected

Is there a way that I can modify the following code to see if the Excel file is already open and if so, write to another file and open the new file.
Perhaps I could increment the file suffix name to create unique file names everytime the file is written to and opened.


Set com = New ADODB.Command
With com
.CommandType = adCmdStoredProc
.CommandText = "dbo.procSpForeign"
.Parameters.Append .CreateParameter("RptYear", adInteger, adParamInput, 4, intYearSP)
.ActiveConnection = cn
Set rstQueryFS = .Execute
End With
ExportedFile = "C:\UDL\FRGNCTRY.XLS"
DoCmd.OutputTo acOutputTable, "tblSpFCY", acFormatXLS, ExportedFile
If isFileExist(ExportedFile) Then StartDocXLS ExportedFile

Private Function StartDocXLS(FileName)
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet

'open excel template
Set xlApp = New Excel.Application
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Open(FileName)
Set xlWS = xlWB.Worksheets(1)
xlApp.ScreenUpdating = True
End Function

Private Function isFileExist(filePath As String) As Boolean
isFileExist = (filePath <> "" And Dir$(filePath) <> "")
End Function

 

[jebry]

Hi!

Try this:

ExportedFile = "C:\UDL\FRGNCTRY " & Format(Now()) & ".XLS"

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst