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.

Embedded Excel Object - Containers Path and Name
 

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" wrote in message
...
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.

Embedded Excel Object - Containers Path and Name
 

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" wrote in message
...
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.

Embedded Excel Object - Containers Path and Name
 

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" wrote in message
...
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.

Embedded Excel Object - Containers Path and Name
 

Gerry,
Which object did you start with ?
Range("A1")
Range("A1").Parent=Worksheet
Range("A1").Parent.Parent=Worksheet.Parent=Workboo k
(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" ...
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" wrote in message
...
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.