Re: Opening Excel from Access

Sorry

Should have added

There is some code in the Excel application which refers to the Database and
extracts the relevant information

No problem opening Excel whether the database is open or not.

Phil

"Phil Stanton" <phil@xxxxxxxxxxxxxxxxxx> wrote in message
news:W82dnW2MNIyg8aTVnZ2dnUVZ8tfinZ2d@xxxxxxxxxxxxxxxxx
I have a form with a button which is supposed to open an Excel file (With
lots of Macros /VBA) in it. The Excel file gets it's data from the Access
program

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 XLName As String ' Excel file name from
Paths
Dim MyDb As Database
Dim Msg As String

' Find the normal path
' Folder and File
If Nz(ExcelPath) = "" Then
ExcelPath = "C:\Storage.XLS"
End If

XLName = Right(ExcelPath, Len(ExcelPath) - InStrRev(ExcelPath, "\")) '
File Name

If Dir(ExcelPath) <> XLName Then ' Not found
XLFilePath = FindFile("C:\Documents and Settings\Phil\My
Documents\Access\MDB\WFYC\", XLName)
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 & ExcelPath & vbCrLf
Msg = Msg & "Do you want to use the new name in future?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
ExcelPath = XLFilePath
End If
End If

Set ExcelApp = CreateObject("Excel.Application")

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 = CreateObject("Excel.Application")
ExcelApp.WorkBooks.Open ExcelPath, , True ' Read Only
ExcelApp.Visible = True

Exit_Storage_Click:
Exit Sub

Err_Storage_Click:
If Err = 2447 Then ' Corrupted File name (with # sign)
Resume Next
Else
MsgBox Err.Description
Resume Exit_Storage_Click
End If

End Sub

When I run it, I get an Error Box "ODBC Microsoft Access Driver Login
Failed" and the message "The database has been placed in a state by user
'Admin' on Machine 'PHIL-DESKTOP' that prevents it being opened or locked"

What am I doing wrong

Thank

Phil



.