Author Topic: Linking a shape to excel through double click event  (Read 10372 times)

0 Members and 1 Guest are viewing this topic.

maltman

  • Newbie
  • *
  • Posts: 7
Linking a shape to excel through double click event
« on: July 11, 2008, 09:58:54 AM »
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

  • Sr. Member
  • ****
  • Posts: 278
Re: Linking a shape to excel through double click event
« Reply #1 on: July 11, 2008, 11:28:58 AM »
http://visguy.com/vgforum/index.php?topic=19.0

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

maltman

  • Newbie
  • *
  • Posts: 7
Re: Linking a shape to excel through double click event
« Reply #2 on: July 11, 2008, 03:46:51 PM »
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.

Code
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

  • Jr. Member
  • **
  • Posts: 16
Re: Linking a shape to excel through double click event
« Reply #3 on: July 12, 2008, 06:22:56 AM »
isn't there some kind of "#" notation for linking to sub-parts of Word and Excel documents?

Lars-Erik

  • Sr. Member
  • ****
  • Posts: 278
Re: Linking a shape to excel through double click event
« Reply #4 on: July 12, 2008, 07:41:53 AM »
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
Code
Set xlSheet = xlBook.Worksheets("WEB")
something like
Code
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

  • Newbie
  • *
  • Posts: 7
Re: Linking a shape to excel through double click event
« Reply #5 on: July 12, 2008, 09:09:40 AM »
.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.
« Last Edit: July 12, 2008, 09:42:57 AM by maltman »

Wombat

  • Jr. Member
  • **
  • Posts: 16
Re: Linking a shape to excel through double click event
« Reply #6 on: July 12, 2008, 01:28:01 PM »
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

maltman

  • Newbie
  • *
  • Posts: 7
Re: Linking a shape to excel through double click event
« Reply #7 on: July 12, 2008, 02:03:09 PM »
You know that might be a lot easier.

Thanks Wombat.

maltman

  • Newbie
  • *
  • Posts: 7
Re: Linking a shape to excel through double click event
« Reply #8 on: July 14, 2008, 09:29:06 AM »
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

  • Administrator
  • Hero Member
  • *****
  • Posts: 1716
  • Smart Graphics for Visual People...n' Stuff
    • Visio Guy
Re: Linking a shape to excel through double click event
« Reply #9 on: July 14, 2008, 11:50:39 AM »
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

  • Newbie
  • *
  • Posts: 7
Re: Linking a shape to excel through double click event
« Reply #10 on: July 14, 2008, 12:49:14 PM »
I don't know how it happened but it looks like they are all opening maximized now.  Thanks a lot Visio Guy.

Visio Guy

  • Administrator
  • Hero Member
  • *****
  • Posts: 1716
  • Smart Graphics for Visual People...n' Stuff
    • Visio Guy
Re: Linking a shape to excel through double click event
« Reply #11 on: July 14, 2008, 02:52:24 PM »
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

  • Newbie
  • *
  • Posts: 7
Re: Linking a shape to excel through double click event
« Reply #12 on: July 14, 2008, 05:40:13 PM »
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.