Embedded Excel Object - Containers Path and Name

[Guest]

I have embedded an Excel Sheet into a Word Document and the sheet has a
button on it that I have placed some code in. I desparately need to figure
out what VBA code to use in order for me to get the name of the document that
the Excel Sheet is embedded in.

I have tried ThisWorkBook.FullName but that does not give me the full path
of the document. It does return the name of the Word Document, but not the
path.

I have tried ThisWorkBook.path, but that returns nothing.

Any Ideas Folks?


Thanks,
Gerry O.

 

[NickHK]

Gerry,
I was under the impression that the "Container" object was for this, but I
can not it to work, although I can crash Excel easily using it. However,
Range("A1").Parent.Parent.Name
returns something like
"Worksheet in C: Documents and Settings Nick Desktop Doc1.doc"
ie. the full path but without folder back slashes - for some reason.
(but sometimes only "Worksheet in Doc1.doc" )

It may be safe if you made sure you only used paths that did not contain
spaces in the name, but still seem somewhat flakey.

If you read the Help on "Container Property Example":
<From Help>
Set myBinder = GetObject("Binder1.obd", "Office.Binder")
Set myWorkbook = myBinder.Sections(1).Object
With myWorkbook
.Container.Sections(2).Visible = False
.Sheets(1).Cells(1, 1).Value = 345.67
End With
</From Help>

This make the .Container pointless, as you have to know the Containing app
or title in order to get the .Container property.
So the code above is the same as:
myBinder.Sections(2).Visible = False

Maybe it'll will help in the right direction.

NickHK

 

[Guest]

I'll give that a try. If I can get the full path, even without slashes, that
would be perfectly fine by me. I'll get back to you.

Thanks,
Gerry O.

 

[Guest]

Unfortunately it did not work for me. I have put a button on the worksheet and
the code I put inside the button to test was msgbox parent.parent.name and I
got Microsoft Excel returned to me. This is after I embedded the workbook in
a Word document.

The intention of this is to create templates from various MS Applications
that
people will use. When they click the button on my embedded Excel workbook,
it will get the name and path of the document and write various bits of info
to a database.

I will not even know what type of application it will be before hand.

Thanks,
Gerry O.

 

[NickHK]

Gerry,
Which object did you start with ?
Range("A1")
Range("A1").Parent=Worksheet
Range("A1").Parent.Parent=Worksheet.Parent=Workbook
(here Workbook.Name return the "Worksheet in C: Documents and Settings Nick
Desktop Doc1.doc"
Range("A1").Parent.Parent.Parent=Worksheet.Parent.Parent=Workbook.Parent=Application/Excel

So depending which object you took the .Parent.Parent of, you may get the WB
or Excel.

After testing the other way (Word doc embedded in Excel worksheet), these
works as expected:
Private Sub CommandButton1_Click()
MsgBox ThisDocument.Container.FullName '>>WS path & name
MsgBox ThisDocument.Container.Parent.Name '>>Excel
End Sub

So seems to work with Word in Excel, but not Excel in Word.
Don't know about other Office app combinations yet.

NickHK