Linking a shape to excel through double click event

Started by maltman, July 11, 2008, 02:58:54 PM

Previous topic - Next topic

0 Members and 2 Guests are viewing this topic.

maltman

Hey all,

I am trying to make a shape's double click event open an Excel document.  I tried this first through a macro but could not get Visio to recognize any Excel calls.  So I set up a GOTOPAGE and pointed it to Excel.  This works great but I need to get them to go to a specific sheet inside my workbook.  Right now I have this: =GOTOPAGE("C:\temp\test.xls").  What can I add to the end of that to open a specific sheet?  Or is there a way to get Visio to recognize the VBA Excel calls?

Thanks,

Matt

Lars-Erik


maltman

Thanks for the reply

I have added a macro and it opens the workbook but not the sheet.  Here is the code I am using.


Sub Test()

Dim appExcel As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Set appExcel = CreateObject("excel.application")
appExcel.Visible = True
Set xlBook = appExcel.Workbooks.Open("C:\Test.xls")
Set xlSheet = xlBook.Worksheets("WEB")
End Sub


It opens to a sheet called Sheet5 for whatever reason.

Wombat

isn't there some kind of "#" notation for linking to sub-parts of Word and Excel documents?
6'4" and full of muscle

Lars-Erik

xlSheet.Activate? something like that not sure about the exact command. Your just setting the xlSheet, not doing anything like with the book (there you Open it)
So could also try instead of
Set xlSheet = xlBook.Worksheets("WEB")
something like
Set xlSheet = xlBook.Worksheets("WEB").Open or .Activate
Again, im not sure what the command is, but have a look in the VBA editor and try the commands that sound like they might do what you want.

- Lars

maltman

#5
.Activate worked great.  Thanks a lot Lars.

I do have a couple more questions.  First, Is there a command that will say when someone X's out of Excel that the workbook will close instead of error out?  Second, I have about 80 shapes that I need to add this macro to.  Instead of creating 80 different modules, is there a way (maybe with a Case statement) to have the code look at what shape you just clicked on and activate that part of the Case statement?  I will do some searches on these two questions and see what I can come up with.

Thanks again.

Wombat

You can link straight to a sheet/cell in Excel with a formula like this:

GOTOPAGE("C:\Documents and Settings\Wombat\Desktop\Test Spreadsheet.xlsx#Sheet2!B8")

You don't need the vba
6'4" and full of muscle

maltman

You know that might be a lot easier.

Thanks Wombat.

maltman

That is working very well Wombat.  The only thing that is going on is it does not maximize the Excel worksheet when Excel opens.  This is a minor issue.

Thanks again to you and Lars for all of your help.

Visio Guy

Hey Maltman,

Maybe try the HYPERLINK function instead of GOTOPAGE?

From the Visio SDK help file, the arguments are thus:

HYPERLINK("address"[,"subaddress","extrainfo",window,"frame"])

Maybe one of those will specify maximized?
For articles, tips and free content, see the Visio Guy Website at http://www.visguy.com
Get my Visio Book! Using Microsoft Visio 2010

maltman

I don't know how it happened but it looks like they are all opening maximized now.  Thanks a lot Visio Guy.

Visio Guy

Maybe Excel (or Visio) is saving some sort of "last window state" ???
For articles, tips and free content, see the Visio Guy Website at http://www.visguy.com
Get my Visio Book! Using Microsoft Visio 2010

maltman

That's a good question.  It looks like as long as I save Visio maximized, Excel will maximize as well.  I tried to save Excel maximized but it still would not come up maximized.  Oh well.  I am going to make sure I keep Visio maximized and all should be good. 

Thanks all.