The following works under Access-200/2002 and came originally from
Microsoft Visual Basic 5.0. I don't know if it will work in your case or
not.
-------------------------------
' Declare necessary API routines:
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, lParam As Any) As Long
Sub GetExcel()
Dim MyXL As Object ' Variable to hold reference
' to Microsoft Excel.
Dim ExcelWasNotRunning As Boolean ' Flag for final release.
' Test to see if there is a copy of Microsoft Excel already running.
On Error Resume Next ' Defer error trapping.
' Getobject function called without the first argument returns a
' reference to an instance of the application. If the application isn't
' running, an error occurs.
Set MyXL = GetObject(, "Excel.Application")
If Err.Number <> 0 Then ExcelWasNotRunning = True
Err.Clear ' Clear Err object in case error occurred.
' Check for Microsoft Excel. If Microsoft Excel is running,
' enter it into the Running Object table.
DetectExcel
'Set the object variable to reference the file you want to see.
Set MyXL = GetObject("c:\vb4\MYTEST.XLS")
Set MyXL = GetObject("h:\mlindsay\book1.xls")
' Show Microsoft Excel through its Application property. Then
' show the actual window containing the file using the Windows
' collection of the MyXL object reference.
MyXL.Application.Visible = True
MyXL.Parent.Windows(1).Visible = True
' Do manipulations of your
' file here.
' ...
' If this copy of Microsoft Excel was not running when you
' started, close it using the Application property's Quit method.
' Note that when you try to quit Microsoft Excel, the
' title bar blinks and a message is displayed asking if you
' want to save any loaded files.
If ExcelWasNotRunning = True Then
MyXL.Application.Quit
End If
Set MyXL = Nothing ' Release reference to the
' application and spreadsheet.
End Sub
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
----------------------------------------
The following also seems to work but it may cause
problems that I have not tested for.
Sub sample1()
Dim xlo As Object
Dim xlwb As Workbook
On Error Resume Next
'First test to see if Excel is running
Set xlo = GetObject(, "excel.application")
If Err.Number <> 0 Then
MsgBox ("No Excel Application Window is currently open")
Err.Clear
Exit Sub
End If
'Now test to see if the desired workbook is loaded.
Set xlo = GetObject("c:\book1.xls") 'Full path is required
If Err.Number <> 0 Then
MsgBox ("Workbook requested is not currently open.")
Err.Clear
Exit Sub
End If
Set xlwb = xlo
End Sub
Michael R. Lindsay NCT
(e-mail address removed)
Graham Mandeno said:
Hi Sullivan
I believe that CreateObject("Excel.Application") will always create a new
instance of the application (while Word will reuse an existing one if it
can). This means that the new application object has no workbooks open,
which explains why the code is not getting into your loop.
You can test whether a file is open by trying to open it for exclusive
access:
Public Function IsFileOpen(sFileName As String) As Variant
Dim hFile As Long
On Error GoTo ProcErr
hFile = FreeFile
Open sFileName For Input Lock Read Write As #hFile
IsFileOpen = False
Close #hFile
ProcEnd:
Exit Function
ProcErr:
With Err
If .Number = 70 Or .Number = 55 Then
IsFileOpen = True
Else
.Raise .Number, .Source, .Description, .HelpFile, .HelpContext
End If
End With
Resume ProcEnd
End Function
You can use GetObject to obtain a reference to an already running instance
of Excel, but if there is more than one active then it may be tricky to get
the right one.
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.
Sullivan said:
After running some queries, my current graphing macros
call an excel workbook(Because of the size and scope, I
couldn't get Access to cooperate, and I'm much more
familiar with Excel). Well, now that I'm done making sure
that the graphs work, I'm trying to have it so that it
doesn't open the workbook again if it's already opened.
While using the following loop, which I used a very
similar one before when trying to find out if Word
Documents are open while in Excel, I noticed that it was
still opening the workbook, and not seeming to even check
if it was open.
Dim xlapp As Object
Set xlapp = CreateObject("Excel.Application")
For Each book In xlapp.Application.Workbooks
If InStr(1, book.Name, "ChartCreator", 1) Then
docFound = True
book.Activate
Exit For
Else
docFound = False
End If
Next book
Upon further inspection (by going line by line) I noticed
that it hits the first line, but then skips the entire
loop and just goes on (the next line is if docFound is
false, open the workbook). I've found this perplexing
especially since there are currently 3 workbooks open on
my computer.
Anyone have any ideas why it would skipping these?
