Just to jump in to muddy the waters!

Add a line to this portion of Chip's code:

If Not XL.ActiveWorkbook Is Nothing Then
Set WB = XL.ActiveWorkbook
End If

make it look like:

If Not XL.ActiveWorkbook Is Nothing Then
Set WB = XL.ActiveWorkbook
msbox wb.name
End If

And you'll see WB is what you want.


Jimmy wrote:

Flogging a dead horse here, if I have 7 excel workbooks open but only 1 is
active, there is no way from Word VBA that I can get a reference to that
active workbook/sheet if I don't have the name?
"Chip Pearson" wrote in message
...
Jimmy,

GetObject is used to get a reference to an existing running instance of
Excel. It will fail and return Nothing if there is no running instance of
Excel. If there is more than one instance running, you have no control
over which instance to which you will get a reference. Perhaps code like
the following will work:

Dim XL As Excel.Application
Dim WB As Excel.Workbook
On Error Resume Next
Set XL = GetObject(,"Excel.Application")
If XL Is Nothing Then
Set XL = CreateObject("Excel.Application")
End If
If Not XL.ActiveWorkbook Is Nothing Then
Set WB = XL.ActiveWorkbook
End If


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





"Jimmy" wrote in message
ink.net...
Then perhaps I shouldn't be running GetObject at all? I just need a
reference (from Word VBA) to the active Excel document, without knowing
its name. Is this possible?


"Chip Pearson" wrote in message
...
If you don't know the filename, you cannot control with instance of
Excel GetObject is going to return.



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



"joe" wrote in message
...
If I run the following code and I have an open spreadsheet with
existing
sheets, I get another instance of Excel w/no sheets. How can I
reference
the active spreadsheet(w/o knowing the filename?)

Dim oXL as object
Dim xlWorksheet as object

Set oXL = GetObject("", "Excel.Application")
Set xlWorkSheet = oXL.Worksheets.Add

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







--

Dave Peterson