Imbed and Link External Excel Spreadsheet into Visio

8,135 pts.
Tags:
Excel 2007
SharePoint 2007
Visio 2007
Windows 2003 Server
Looking to utilize an external spreadsheet which maintains various information pertaining to our move from one data center to another. Items included are ip address, rack, U, hostname. The question is I want to utilize this spreadsheet as my source. Then within my Visio, I have tabs for each Rack. Within each Tab, I want to "link" the information related to "rack" and then all related information be imbedded into the visio tab. Any direction would be helpful.  I've attempted within sharepont and SMB shares. 

Software/Hardware used:
Excel and Visio 2007 Sharepoint SMB
ASKED: February 24, 2010  7:48 PM
UPDATED: August 23, 2013  6:52 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Well it seems nobody wants to help you on this so I’ll give it a go.

I’ve never integrated Visio and Excel, but VBA is VBA and I always thought about bridging that gap. So here is one way (I tested it and it works! – You’re okay with coding right?)

First: Create an Excel Spreadsheet with a range named “Data”

On the first page create a table. The table will have two columns. In the first column (say – in cell A3) add the title “Number”. In the second column (B3) type “Name”. Add some values to the table so it looks like:

<pre>
Number Name
1 One
2 Two
3 Three
</pre>

Now name the range “Data”. You can do this in Office 2007 using the Formulas tab and the “Name Manger” option.

Save the spreadsheet as C:\TEMP.XLS.

Second: Add a shape that accepts text to your Visio

Third: Add some VBA to your Visio project

You’ll need to get to the VBE (Visual Basic Editor). The short cut key is Alt-F11 (the same as in all MS office products).

Next, select Tools > References > and scroll way down until you find something like “Microsoft ActiveX Data Objects 6.0 Library”. If you don’t have version 6.0 – no worries, just check whatever is closest and click OK. That just added ADO functionality to your Visio project.

Then, add this subroutine:

<pre>
Sub Main()

Dim sConnect As String
sConnect = "Driver={Microsoft Excel Driver (*.xls)};" & _
"DBQ=c:\Temp.xls"

Dim sSQL As String
sSQL = "Select Name from Data where Number = 2"

ActivePage.Shapes(1).Text = SQLRead(sSQL, sConnect)

End Sub

</pre>

This declares a connection string that tells ADO to look for your data in an Excel spreadsheet called C:\Temp.xls.

Then we write a tiny SQL script.

Lastly, we assign to the first shape’s text what is in Excel.

All you need now is the SQLRead function. And that is in my blog at: Beyond Excel – Using ADO to Read a Database

Copy and paste that code into your Visio VBE Code window. Once done, click anywhere in the Main subroutine and press F5 to run it. Check your Visio diagram. The first shape will have the word “two”.

Now you have the basic tools to linking Excel data to Visio Shapes. Good luck. I hope this helped.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following