Solved

Visio 2010 - Linking Excel

Posted on 2016-07-29
13
83 Views
Last Modified: 2016-11-15
Hi Experts,

This is more of an informational question, but if able to do in Visio would appreciate examples:

I have a listing of tables and fields in separate columns in Excel and am wondering if it is possible to link the listings to Visio table drawings?  Filling all the tables along with all the fields into the table shape?

Please help and thanks...
0
Comment
Question by:Amour22015
  • 7
  • 6
13 Comments
 
LVL 30

Expert Comment

by:Scott Helmers
ID: 41736061
If there is a shape in each cell of the table in Visio then the answer is yes. Or if there is a shape you can use in each cell of the table, then the answer is also yes. Linking a diagram to data involves two steps: 1) linking the diagram to a data source (e.g., Excel, Access, SharePoint list, database); and 2) linking each row of imported data to a shape. As I suggested above, #2 can be accomplished by either by dragging a data row onto an existing shape or by selecting a master in a stencil, and then dragging a data row onto the page (in the latter case, Visio drops an instance of the selected master onto the page and then adds the data).

Let me know if you need more details...
0
 

Author Comment

by:Amour22015
ID: 41736084
Hi Scott Helmers,

It has been mentioned to use "visio 2010 reverse engineer sql server"

I am really knew to Visio, so I would need step-by-step detail

I have about 150 Tables with all there fields, index's,

Thanks for helping...
0
 
LVL 30

Expert Comment

by:Scott Helmers
ID: 41736457
Thanks for the additional comments... I missed that you were talking about database tables in your original question.

The reverse engineering capability existed in Visio Professional up to and including Visio 2010. Consequently, neither Visio 2013 nor Visio 2016 includes this feature but 2010 and earlier do. Note also that you must have the Professional edition, not the Standard edition.

These instructions were written for Visio 2007 but they are essentially correct for Visio 2010.

If you have any trouble finding the Database Modeling template in Visio 2010, take a look at this page.
0
 

Author Comment

by:Amour22015
ID: 41736468
Hi Scott Helmers,

So you are saying that instead of me manually entering in each: Table, Field, Index which would take weeks to do.  I can do the same in a few hours?  By just using "reverse engineering "?

Wow

thanks...
0
 
LVL 30

Expert Comment

by:Scott Helmers
ID: 41736486
Yes, that's the idea. The results may not be perfect, but when you point the reverse engineering add-in at a database, it will create a diagram that shows the tables and relationships.

Here's an example I just found: http://social.technet.microsoft.com/wiki/contents/articles/25701.reverse-engineering-a-sql-server-database-using-visio.aspx
0
 

Author Comment

by:Amour22015
ID: 41737324
Ok,

I don't even know how to connect, every time I try it just comes up with no listing at all.

I tried connecting to:
SQL Server
Excel (loco on C:)

Please help and thanks
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 30

Expert Comment

by:Scott Helmers
ID: 41737349
Unfortunately, I can't help you with how to connect to your database. You'll need to talk to a database admin who can provide username, password, and other connection details.
0
 

Author Comment

by:Amour22015
ID: 41737766
Ok,

I tried to do the "reverse engineering " but with no luck.  Seems that I can not do this at all.  So I tried with DDS SQL Server Script.  I went into SSMS generated a script and I have it on the local machine.   But don't know enough about Visio 2010 to import that DDS Script into Visio.


Please help and thanks...
0
 
LVL 30

Accepted Solution

by:
Scott Helmers earned 500 total points
ID: 41737798
I don't believe that Visio can use your script directly. It generally queries a database via ODBC (some ODBC drivers allow you to enter SQL query commands).

Consequently, I think you'll either need to use one of the built-in ODBC drivers available in Office or search for and download one online. If your data is in SQL Server or Oracle you should be all set with one of the built-in drivers. The sequence of screen shots below is what I stepped through to get to the point of selecting a SQL database. Because I don't have a SQL database handy, that's as far as I can go.
SQL-ODBC.jpg
0
 

Author Comment

by:Amour22015
ID: 41737981
Thanks for your response.

Yes, I tried that.  Seems getting to the servers is the problem.  

So the next step would be to download a listing of just Tables and Fields to Excel. I did this and still having problems connecting to my Excel spreadsheet (local) on my C drive.

I tried:
ODBC Generic Driver and Microsoft SQL Server
Excel Files
<Next>

So in short this is the problem I am having:
https://social.technet.microsoft.com/Forums/office/en-US/3c8eb13a-f5ec-4248-ac3b-620868bcd031/visio-fails-to-reverse-engineer-excell-database?forum=visiogeneral

In your first response I am guessing that you are not talking about "reverse engineering"

Please help and thanks
0
 
LVL 30

Expert Comment

by:Scott Helmers
ID: 41738187
You are correct that in my very first response above I was not referring to database reverse engineering but to the more general data linking facility. While data linking will let you read data from Excel quite easily, it does not draw a diagram for you. Consequently, if you would still like to have Visio create all of the tables and links for you, I think you'll need to connect with someone local who can help you get access to the SQL database. Visio provides the mechanism for doing so, but it can't know about database locations, permission, etc. You will need that sort of local information in order for the Visio reverse engineering wizard to do its thing.
0
 

Author Closing Comment

by:Amour22015
ID: 41738222
Thank you for helping me to understand...
0
 
LVL 30

Expert Comment

by:Scott Helmers
ID: 41887897
A postscript to this question for anyone who is looking for the database reverse engineering (DBRE) feature in Visio: as of today, Microsoft has announced availability of a preview version of the DBRE feature for Visio Pro for Office 365.

Here's the announcement article.

Please note that the preview is currently restricted to Visio Pro for Office 365 and is not available for the desktop (msi installer) version of Visio Professional.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Problem to With line 4 41
Merging-Splitting-Multiple-Rows 33 42
Excel Automation VBA 19 33
Excel vba to add signature to email when created 11 29
Periodically someone asks me whether there’s a way to automatically convert all of the pages in a Visio drawing to PowerPoint slides. There have even been a few times when I’ve wanted to do that myself but I never really had enough incentive to figu…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now