Opening Excel from Access 2000

I have a form with a TextBox that holds the path to an Excel File and a
command button that opens the excel file. The Excel file basically contains
about 5 maps, one on each work*** scatter charts on the maps showing
locations. I had to use Excel as I wanted each point of the scatter chart
labeled and used Bob Bovey's Chart Labler addition to Excel.

The data for the scatter charts come from the database ( x & y positions and
the appropriate labels
If the database is closed the Excel file opens perfectly every time. With
the DB open again 100% success. However, if I open the file using the
command button it appears to open OK and then 1 of 2 problems. As soon as I
click the mouse on a chart, Excel crashes. If I click on a blank cell first
and then click on the chart normally there is no problem but sometimes I get
the message "The database has been placed in a state bu user 'admin' on
machine 'ABC-DESKTOP' that prevents it being opened or locked" and then goes
on to indicate the query linking the Excel to the DB won't run.

Here is the code

Private Sub Storage_Click()

On Error GoTo Err_Storage_Click

Dim ExcelApp As Object
Dim ExcelWasNotRunning As Boolean ' Flag for final release
Dim XLFilePath As String
Dim XLPath As String ' Excel path name from Paths
Dim XLName As String ' Excel file name from Paths
Dim Mydb As Database
Dim Msg As String
Dim QCoInfoPathsSet As Recordset

' Find the normal path
Set Mydb = CurrentDb
Set QCoInfoPathsSet = Mydb.OpenRecordset("QCoInfoPaths")
With QCoInfoPathsSet
.MoveFirst
XLPath = !ExcelPath
XLName = Right(XLPath, Len(XLPath) - InStrRev(XLPath, "\"))

If Dir(ExcelFileName) <> XLName Then ' Not found
XLFilePath = FindFile("C:\Documents and Settings\Phil\My
Documents\Access\MDB\WFYC\", ExcelFileName)
Msg = "The name of the file you have selected is " & vbCrLf
Msg = Msg & XLFilePath & vbCrLf
Msg = Msg & "but the original file was " & vbCrLf
Msg = Msg & XLPath & vbCrLf
Msg = Msg & "Do you want to use the new name in future?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
.Edit
!ExcelPath = XLFilePath
.Update
End If
ExcelFileName = XLFilePath
End If
.Close
End With
Set QCoInfoPathsSet = Nothing
Set Mydb = Nothing

If ExcelFileName.DefaultValue <> ExcelFileName Then
ExcelFileName.DefaultValue = ExcelFileName
DoCmd.Save acForm, Me.Name
End If

'*************** The bit above is just to check the path is OK
On Error Resume Next
Set ExcelApp = CreateObject("Excel.Application")
'ExcelApp.Visible = True

If Err.Number <> 0 Then ExcelWasNotRunning = True
Err.Clear ' Clear Err object in case error occurred.

' Set the object variable to reference the file you want to see.
Set ExcelApp = GetObject(ExcelFileName)

' Show Microsoft Excel through its Application property. Then
' show the actual window containing the file using the Windows
' collection of the ExcelApp object reference.
ExcelApp.Application.Visible = True
ExcelApp.Parent.Windows(1).Visible = True

Exit_Storage_Click:
Exit Sub

Err_Storage_Click:
MsgBox Err.Description
Resume Exit_Storage_Click

End Sub


Any Ideas gratefully received

Thanks

Phil



.