Problem opening Excel 2000 within Access2000

[OC2]

After reviewing and applying the getobject function listed in the VB help file, I was unsuccessful to open a file if Excel was already running.

This is the code I used.


In the module

Option Compare Database

Option Explicit

Private Const XLT_LOCATION As String = "c:\test\book1.xls"
Private Const CLT_LOCATION As String = "c:\test\aidt.xls"

Declare Function FindWindow Lib "user32" Alias _
"FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As Long) As Long

Declare Function SendMessage Lib "user32" Alias _
"SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, _
ByVal lParam As Long) As Long
-----------------------------------------------------------
Sub DetectExcel()
' Procedure dectects a running Excel and registers it.
Const WM_USER = 1024
Dim hWnd As Long
' If Excel is running this API call returns its handle.
hWnd = FindWindow("XLMAIN", 0)
If hWnd = 0 Then ' 0 means Excel not running.
Exit Sub
Else
' Excel is running so use the SendMessage API
' function to enter it in the Running Object Table.
SendMessage hWnd, WM_USER + 18, 0, 0
End If
End Sub

----------------------------------------------------------

Using a command click

Private Sub Excel_Click()
Dim MYXL As Object
Dim ExcelWasNotRunning As Boolean
On Error Resume Next
Set MYXL = GetObject(, "exel.Application&quot

If Err.Number <> 0 Then ExcelWasNotRunning = True
Err.Clear
DetectExcel
Set MYXL = GetObject(XLT_LOCATION)

MYXL.Application.Visible = True
MYXL.Parent.Windows(1).Visible = True



End Sub
---------------------------------------------------------

This code will open the file name I want if Excel is not running, but if an Excel file is open, all this code will do is maximise the open excel file and stop there.

Also this code will not read any of my VB scripts.

Could anyone help me put this right as the code Ive used is exactly what the help file has suggested to use, but it doesnt work.

I dont want to use the createobject function because I want to eliminate the chance of opening a &quot;read-only&quot; File, should the exel file already be open.

thx

 

[nath]

Hi OC2,

Maybe you could use the GetObject function. If excel is not open, you can trap the error and in the error handling, you can use the CreateFunction then resume your code; you then check in your excel object if the workbook is in the workbooks collection; if it is you work on it, if not you open it.

Hope this might help,

Nath