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("PathAndFileNameThatYouWant.xls")

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

Obviously change the worksheet and range to that required.

NickHK

"Jason V" <(E-Mail Removed)> wrote in message
news:1375D94D-1B3C-4833-9325-(E-Mail Removed)...
> 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("PathAndFileNameThatYouWant.xls")
>
> Msgbox "The shape name is: " & XLWB.Worksheets(1).Range("A1").Value
>
> Obviously change the worksheet and range to that required.
>
> NickHK
>
> "Jason V" <(E-Mail Removed)> wrote in message
> news:1375D94D-1B3C-4833-9325-(E-Mail Removed)...
> > 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