Modification of the example code in Excel VBA help for the GetObject method.

' 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, _
ByVal lParam As Long) As Long

Sub GetExcel()
Dim MyXL As Object ' Variable to hold reference
' to Microsoft Excel.
Dim ExcelWasNotRunning As Boolean ' Flag for final release.
Dim MySheet As Object
Dim rng As Object
' 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")

' 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.
'
' added for questions one and two
'
On Error Resume Next
Set MySheet = MyXL.Worksheets("Data")
On Error GoTo 0
If MySheet Is Nothing Then
Set MySheet = MyXL.Worksheets.Add(After:=MyXL.Worksheets( _
MyXL.Worksheets.Count))
Set rng = MySheet.Range("A1")
Else
Set rng = MySheet.Cells(Rows.Count, 1).End(xlup) _
.Offset(1, 0)
End If

' ...
' 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


--
Regards,
Tom Ogilvy

"James Wagman" wrote in message
oups.com...
I have two related questions:
Precondition: the code has to be late bound because I won't know if the
client has a reference to excel. I am doing the coding from Word VBA -
exporting data from Word to Excel.
1. If a workbook is open, what would the code to append a new sheet to
the activeworkbook ( and get a reference to said worksheet)
2. If a workbook is open, what would the code be to append to the
activesheet - I need to put the data starting where the data on the
sheet ends.
TIA.
P.S. How can I tell if no excel is running at all, so none of the above
errors out?