Visio Guy

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

http://visguy.com/vgforum/index.php?topic=19.0

Have a look at that... I ran into somewhat the same problems

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.

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

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

.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.

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

You know that might be a lot easier.

Thanks Wombat.

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.

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?

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

Maybe Excel (or Visio) is saving some sort of "last window state" ???

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.