Accessing cells in Excel spreadsheet from Visio
 

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

Accessing cells in Excel spreadsheet from Visio
 

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

Accessing cells in Excel spreadsheet from Visio
 

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

Accessing cells in Excel spreadsheet from Visio
 

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

Accessing cells in Excel spreadsheet from Visio
 

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

Accessing cells in Excel spreadsheet from Visio
 

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

set XLWB = .Activewindow

David Miller

Accessing cells in Excel spreadsheet from Visio
 

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