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?

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?

Was this the code to append to workbook/worksheet? If so, can you
clarify? Or perhaps I wasn't clear enough. From Word VBA, I need to get
a reference to a running Excel workbook in order to add a new worksheet.
Also, I would need to get a reference to the active sheet if the user
wants to append to the activesheet.

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




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

It looks for a worksheet named Data. If it exists it appends to that sheet.
If not it adds a sheet and appends to that.

I could add a line
Set MySheet = MyXL.Worksheets.Add(After:=MyXL.Worksheets( _
MyXL.Worksheets.Count))
Set rng = MySheet.Range("A1")
Mysheet.name = "Data"

ActiveSheet would just be

Set MySheet = MyXl.Activesheet

while the code does not perform exactly what you asked it does show you how
to do both thins that you asked. Find the next cell to write to in an
existing sheet or to add a sheet.

--
Regards,
Tom Ogilvy

"joe" wrote in message
...
Was this the code to append to workbook/worksheet? If so, can you
clarify? Or perhaps I wasn't clear enough. From Word VBA, I need to get
a reference to a running Excel workbook in order to add a new worksheet.
Also, I would need to get a reference to the active sheet if the user
wants to append to the activesheet.

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




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Tom,

the issue is that I won't know the name of the file to open; all I'll
know is that it is the active document/sheet. How would that change
your code?

You write:

Set MyXL = GetObject("c:\vb4\MYTEST.XLS")

What if I don't know the filename? I just know I want the active
document and/or sheet?



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Joe,

Use code like

On Error Resume Next
Set MyXL = GetObject(,"Excel.Application") ' note leading comma
If MyXL Is Nothing Then
Set MyXL = CreateObject("Excel.Application")
End If



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"joe" wrote in message
...
You write:

Set MyXL = GetObject("c:\vb4\MYTEST.XLS")

What if I don't know the filename? I just know I want the
active
document and/or sheet?



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

And the getobject will get whatever is active while the create object
will create if necessary?


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Joe,

GetObject will return a reference to a running instance of Excel,
if one exists. Once you have the reference to Excel, you can use
ActiveWorkbook to get the workbook that is active. E.g.,

MsgBox MyXL.ActiveWorkbook.Name


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"joe" wrote in message
...

And the getobject will get whatever is active while the create
object
will create if necessary?


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!