Embedded Excel Object - Containers Path and Name

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.

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

"GerryO" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

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.


"NickHK" wrote:

> 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
>
> "GerryO" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
>
>
>

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" wrote:

> 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
>
> "GerryO" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
>
>
>

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

"GerryO" <[email protected]> ¼¶¼g©ó¶l¥ó·s»D:[email protected]...
> 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" wrote:
>
>> 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
>>
>> "GerryO" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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.
>> >
>>
>>
>>