I want to drop a shape on a visio drawing using vba in visio or excel. If I
use the macro in Visio I want to get the name of the shape to drop from a
cell on a worksheet or from a variable name in an excel macro. Can anyone
help? Thanks.
--
Jason V

Jason,
Assuming Visio works similar to other VBA enabled app:
-Set a reference the Excel type library.
Dim XLApp As Excel.Application
Dim XLWB As Excel.Workbook

Set XLApp=New Excel.Application
Set XLWB=XLApp.Workbook.Open("PathAndFileNameThatYouWa nt.xls")

Msgbox "The shape name is: " & XLWB.Worksheets(1).Range("A1").Value

Obviously change the worksheet and range to that required.

NickHK

"Jason V" wrote in message
...
I want to drop a shape on a visio drawing using vba in visio or excel. If
I
use the macro in Visio I want to get the name of the shape to drop from a
cell on a worksheet or from a variable name in an excel macro. Can anyone
help? Thanks.
--
Jason V

Thanks Nick. This works. This code opens an existing excel workbook, I now
how to create a new one, bu how do you place access an excel workbook that is
already open. I have tried set xlwb = activeworkbook and set xlws =
activeworksheet but I still get an error.
--
Jason V


"NickHK" wrote:

Jason,
Assuming Visio works similar to other VBA enabled app:
-Set a reference the Excel type library.
Dim XLApp As Excel.Application
Dim XLWB As Excel.Workbook

Set XLApp=New Excel.Application
Set XLWB=XLApp.Workbook.Open("PathAndFileNameThatYouWa nt.xls")

Msgbox "The shape name is: " & XLWB.Worksheets(1).Range("A1").Value

Obviously change the worksheet and range to that required.

NickHK

"Jason V" wrote in message
...
I want to drop a shape on a visio drawing using vba in visio or excel. If
I
use the macro in Visio I want to get the name of the shape to drop from a
cell on a worksheet or from a variable name in an excel macro. Can anyone
help? Thanks.
--
Jason V

Jason,

Try this:

Dim XLApp As object
Dim XLWB As Excel.Workbook

Set XLApp=getobject(,"Excel.Application")
with XLApp
.Windows("WorkbookName.xls").activate
set XLWB = .Activeworkbook
with XLWB
Msgbox "The shape name is: "
& .Worksheets(1).Range("A1").Value
end with
end with

David Miller

Dave,
Thanks this works and puts me 95% of the way. However I want this to work
with many different workbooks so I don't want to hardcode the name or ask the
name I just want it to go th the open one. Can I somehow use activeworkbook
instead of .windows("Workbookname.xls").activate ?
Thanks
--
Jason V


"Dave Miller" wrote:

Jason,

Try this:

Dim XLApp As object
Dim XLWB As Excel.Workbook

Set XLApp=getobject(,"Excel.Application")
with XLApp
.Windows("WorkbookName.xls").activate
set XLWB = .Activeworkbook
with XLWB
Msgbox "The shape name is: "
& .Worksheets(1).Range("A1").Value
end with
end with

David Miller

You could use this, but be careful that you do not have multiple
windows open.

set XLWB = .Activewindow

David Miller

Thanks for the help this is what I needed. The visio macro that I am running
is being called from an excel macro from a file that is the active workbook
so everything should be okay, but it could be a problem.

Thanks again.
--
Jason V


"Jason V" wrote:

I want to drop a shape on a visio drawing using vba in visio or excel. If I
use the macro in Visio I want to get the name of the shape to drop from a
cell on a worksheet or from a variable name in an excel macro. Can anyone
help? Thanks.
--
Jason V