Assign hyperlinks from Excel 2003 to Visio 2003 shapes

What you're looking to accomplish can certainly be done in Visio 2003, but it will probably require a macro to do it right. You're right that you might be able to accomplish this more easily with the data linking features in Visio 2007 Pro, but because you're trying to create hyperlinks on your Visio shapes, even that might require a macro.

A couple of questions:
1) Do you already have the 1000 shapes in your Visio drawing or do you want to place them there automatically?
2) If the shapes already exist in Visio, does each one contain a unique ID that matches an ID in the each record in Excel?

Regards,
Scott
 

Thank you so much, Scott, for your response.

No, I haven't created the 1000 shapes yet into Visio...if it is possible to place them automatically, i would LOVE it! Is that possible?

Would it be possible for me to ask for your guidance about the macro?

Thank you, again, for your time.

Joy

Can you post an example of what you want the content map to look like? Perhaps add a dozen shapes with hyperlinks and any other data that will be imported from Excel? That will help me to understand where you're headed.

As an alternative, have you experimented with the Web Site Map template in Visio 2003? Select File>New>Web Diagram>Web Site Map. When the new drawing opens, it prompts you for a URL and then it builds a map of your web site for you. Sometimes the results are quite useful, sometimes not -- but it might save you a significant amount of work.

BTW, I think the web site map is only in Visio 2003 Professional. If you only have Standard you'll want to track down a copy of the Professional edition to try this feature.

I mocked up a prototype of what I’m trying to accomplish in both Visio and Excel…I hope that helps illustrate what I’m trying to accomplish.

Unfortunately, I couldn’t share a sample of my actual URLs due to confidentiality issues and the map points to a secured intranet site.

Purpose of this task: Visual content inventory or starting point of “where we are now”. The other fields in Excel beyond the URLs are for internal reference/record keeping.

In the vsd:
1 A “Home Page” sheet that displays only Level 1 topics (if the creation is automated, I can build this page manually since I’m not sure how complex the macro can be to duplicate Level 1 topics).

2 Level 1 topics detailed on their own page which will display the external internet URLs (red), Level 1 subtopics (green), and Relationships to other Level 1 topics/subtopics (blue).
I will create the red shape URLs manually and copy the blue shapes from their perspective detail page manually..

Since I hope not have to retype each URL, is it possible to have a:
a. Macro build item #2 (Level 1 topic detail) pages, noting only the green subtopics?
Or
b. Is it possible to drag the URLs from the linked database into the shape’s hyperlink field?

Does this help or make sense? Besides the Web Site Map Template, is there an easier way of doing this? [I’m limited to what my company offers (Visio Standard) so I’m not able to fiddle with the Web Site Map template at this time.]

Any guidance you might have to help me automate, or at least cut down on the manual effort/probability of errors, is very much appreciated!

BTW: I got this prototype from an Information Architect’s blog, Patrick Walsh, which uses a subway map structure (vs. a standard hierarchy).

Thank you, in advance, for your time and consideration of my request.
 prototype.vsd prototype.xls

I'll apologize in advance for returning to Visio 2003 Professional but I'm a major proponent of using what exists whenever possible (and I know you can still find low cost upgrades to Visio 2003 Pro on ebay and elsewhere). Take a look at the attached map created from our website www.taskmap.com. I asked for a depth of three levels from the top page. Would something like this satisfy your need? A big advantage of this approach is that the need to maintain the spreadsheet disappears because you're reading data directly from your intranet (unless, of course, you need the spreadsheet for something else).

I won't have time to do any more on this today, but here's another idea to think about: what you're looking to create is a hierarchical structure; check this article that another EE expert and I wrote about adapting Visio Org Charts for a purpose not unlike what you're asking about. Although the article doesn't apply colors, there is a "color by value" capability in Visio 2003 that can do that quite easily. You could easily color your level 1, 2 and 3 shapes.

As you can probably tell, while I have no objection to writing macros, if there's a way to produce an acceptable solution that doesn't require code, it's usually a better approach.
Web-site-map.vsd

NICE! Me likey taskmap!
I agree with your mindset of using out-of-the-box features whenever possible but sadly, I am bound to applications approved on an enterprise level for my company.

Believe me, I've tried to introduce tools that would meet many department needs but it requires a global infrastructure review process.

Needless to say, Visio 2003 and 2007 professional  and TaskMap would make my request mute, or at least, less involved, but given the above, I'm stuck.

It makes things harder than they have to be, I admit.

After reviewing the hierarchical structure article, do you kow if the "Importing" would preserve the hyperlink nature of the spreadsheet when converted to Visio? That's the main thing I'm interested in...not having to retype those 1000 hyperlinks, you know? It appears that it brilliantly builds the hierachy and text only but didn't see the possibilty of text features, like hyperlinks.

Thank you for having this dialogue with me and for providing so many sound solutions.

Wow! This is so impressive. I am impressed with your code being so succinct and yet so powerful. I very much appreciate you accommodating this request to fit into my company’s current software version restrictions. You have made this process incredibly simple.


I am curious as to how I might lastly change the org chart layout/format. Some of these webpages will have multiple branches which will not display nicely.

Do you have recommendations on the easiest way to convert the org chart to the subway template? For instance, changing a Level 1 shape in the Org Chart to a specified Big Station from the subway map, Level 2 shape to a Small Station from the subway map, Level 3 shape to a Small Station, etc?

(For my purposes, I don’t think a third macros is needed to convert the hyperlink into an URL since the entire shape has the hyperlink property…that is EXACTLY what I hoped for. Most likely, I won’t display the URL path in Step a since most viewers of this document will be less concerned about that.).

To wrap my mind around this, I would need to:
Excel:
a.      Move all items to the same column (no indentation)
b.      Add a Reports To column (?) so the ExtractReportsTo macros will have some place to spit the data into (or does the VBA already create the column).
c.      Add a Hyperlink column (?) so that the ExtractHL macros will have someplace to display the full URL (or does the VBA automatically create the column)?
Visio :
a.      Start a new Org Chart wizard, using data from an Excel file > Pointing to the revised Excel sheet > Name = UniqueID and Reports to = Reports to, > Fields to Display = Add Level 1  (I won’t display the Hyperlink path) > Custom Properties = all table headings from Excel
b.      Change the shapes, as needed.

Thank you so much for the macros, the examples, and for taking the time to validate my understanding!!!

I just realized that you answerd and geniusly answerd my original question about auto-generating a visio drawing with hyperlinks.

Thank you very much for taking the time to understand my issue and for such a concise and brilliant solution.

I will open another question about how to swap the org chart shapes for another templates shapes so I can manually adjust each drawing's layout.

Thank you!!!!!!

Thanks for the grade and for your kind remarks, Joy. I'll look for the follow on question.

I also have a few additional notes and final thoughts that I'll post to this question later tonight.

Scott

Thank you, Scott! I'll look forward to the final notes!

I opened the new question under "Changing all Visio 2003 shapes to another template's shapes" - I hope used the correct verbiage that will make sense to everyone.

Thank you, again!

Joy -- I tried to reduce the work you need to do in order to get your existing spreadsheet ready for the org chart wizard. The steps below don't require any manual restructuring of the data -- Excel does all the work once you add columns and paste formulas. I don't know whether you're familiar with the VB Editor, but if not, just ask.

1.      In VB Editor:
•      Insert>Module
•      Paste two macros into new module
2.      Close VB Editor
3.      Delete row 1 so that the column headings are in row 1.
4.      Insert four columns between D and E.
5.      Assign these names to the new columns:
•      E: Name
•      F: Reports To
•      G: Level
•      H: Hyperlink
6.      Enter the following formula:
•      E2:      =IF(B2<>"",B2,IF(C2<>"",C2,IF(D2<>"",D2,"")))
•      F2:      =ExtractReportsTo(A2)
•      G2:      =IF(B2<>"",1,IF(C2<>"",2,IF(D2<>"",3,"")))
•      H2:      =ExtractHL(B2)

After that you can save the workbook and run the org chart wizard. In the dialog box that asks what data you want to be included as shape data, be sure to include at least: Name, Level and Hyperlink.

The field called Level will let you apply colors based on the value of the that field. Click Tools>Add-ons>Maps and Floor Plans>Color by Values...   (don't ask why "Maps and Floor Plans"!!) The only downside of the color by value function is it only does one page at a time. It would be possible to automate that with a macro if you're interested.

Function ExtractHL(rng As Range)
' Get hyperlink from Level 1 column if one exists, otherwise
' try Level 2 column, then Level 3

    ExtractHL = ""
    If rng.Hyperlinks.Count <> 0 Then
        ExtractHL = rng.Hyperlinks(1).Address
    ElseIf rng.Offset(0, 1).Hyperlinks.Count <> 0 Then
        ExtractHL = rng.Offset(0, 1).Hyperlinks(1).Address
    ElseIf rng.Offset(0, 2).Hyperlinks.Count <> 0 Then
        ExtractHL = rng.Offset(0, 2).Hyperlinks(1).Address
    End If

End Function
Function ExtractReportsTo(rng As Range)
' Remove ".n" from end of Unique ID to generate the "Reports To" field
' that the Visio org chart wizard will use
    
    Dim s As String
    s = rng.Value
    If InStr(s, ".") > 0 Then
        ExtractReportsTo = Left(s, InStrRev(s, ".") - 1)
    Else
        ExtractReportsTo = ""
    End If

End Function

Select allOpen in new window

A correction in step 5 of my previous post: for the first bullet, column E should NOT be titled "Name". You can call it whatever you'd like, "Level Name" or something else, but if you call it Name, the org chart wizard will assume it is the "employee's name" for the purpose of building the org chart. You need to use "Unique ID" for that purpose.

Thank you, Scott for this last bit!

I wasn't able to get the VBA to run...I'm sorry to ask but can you help?

no problem...

With your spreadsheet open, press Atl+F11, which opens the Visual Basic Editor (VBE).

In the VBE window, click in the upper left as shown below to insert a new module, then paste the code into the window on the right.

Close the VBE window.

If you have already pasted the formulas into the worksheet, press Shift+F9 to recalculate. Otherwise, paste the formulas in and you should be all set.
VBE-1.jpg
VBE-2.jpg

I feel real dumb.

I didn't know that SHIFT-F9 could trigger the VBA....but when I tried, nothing happened. I am so sorry to bother you...could you please take a look at my sheet to tell me where I've erred?

Thank you for your patience.
Copy-of-prototype.xls

Just a couple of things to fix:

In one case my instructions above weren't clear. For example, where I said to copy
   F2:      =ExtractReportsTo(A2)
I meant to copy
   =ExtractReportsTo(A2)
into cell F2. In a couple of cases you copied everything, including the F2: into the cell. Sorry that I wasn't clear. If you delete everything before the "=" it should fix the problem. To say that another way, the formula you copy into the cell should include the equal sign and everything that follows but nothing before it.

The only other problem has nothing to do with you but has to do with the the way Excel formats cells in inserted columns. I don't know what the rules are but it picks up cell formatting from somewhere... adjacent columns perhaps. The solution is simple: select column E through H, then apply this formatting:
-- Align Text Left
-- No underline
-- Black text color
Once you do that, the results of all of the formulas will be visible.

I just noticed one more thing that is a difference between Excel 2003 and the version I'm using: you will need to copy the formulas from E2:H2 down to all rows.

BTW, Shift+F9 is an Excel shortcut that recalculates the current worksheet. It doesn't have anything to do with macros or VB, per se.

So you see? Nothing dumb involved... incomplete instructions and differences in behavior between Excel 2003 and later versions.

Oh, boy, you are so gracious and kind. Yes, it was my ignorance but I followed your fixes and it's PERFECT!

I'm terribly embarrased but am so grateful for your guidance.

We are done with this episode!

Thank you so much, Scott, for your guidance, patience, and expertise.

You're very welcome, Joy. Every once in a while a question like this comes along where I get to try something new and even learn a few things along the way! For example, I hadn't known about user-defined functions in Excel before this, but in looking for a way to extract the hyperlink from a cell I stumbled on some code that did something similar with a user-defined function.

So -- don't be afraid to ask questions! Yours were interesting and a break from the routine...

Oh, thank you, again, especially for being so gracious and lending your expertise to these brain noodles. Thank you, also, for encouraging the questions. I value Experts Exchange so much as a saving grace after I've googled myself as far as I can go. It's a great resource because of the patience and generosity of experts like you!