ODBC with Excel in 2003

[merlin]

I'm trying to link an org chart's shapes with an excel spreadsheet using the
ODBC function.

I think I've followed all the steps in the Wizard and the help pages but I
just get an error 'can't decrypt data' (or something like that). Can anyone
suggest what I'm doing wrong? Is there maybe something else to set up or
something else I should have installed?

 

[Al Edlund]

when playing with odbc, setup is often one of the first missteps. I like to
use Access to test my odbc setups by having it import using the odbc pointer
(not the excel import). When it get's the tables correct I can then move on.
al

 

[merlin]

Although I'm quite proficient with excel, I've never had to use Access so I
would be well out of my depth there.

Are there any common set-up mistakes I might look at?

 

[Bill Morein [MSFT]]

Is your Excel file password protected? The ODBC drivers do not support
encrypted files.

You can also try importing the Excel data in Excel using ODBC. Kind of a
strange use, but it works and you don't need to mess around with Access.

 

[merlin]

OK - I'm a bit closer. The encryption error was indeed the file protection
(could have taken forever to find that out).
I'm now getting the error 'There are no tables or views in the data source'.

I created a test excel sheet with a list of data on sheet 1 with a heading
at the top of each column in the top row.
I saved that and closed it.
Then I went in to Visio (it was already open and on a blank sheet)
Org Chart>Import Org Chart Data>Info That's already stored>ODBC compliant
data source>
browse to my testfile and select.
Then I get the error.

Help!

 

[Al Edlund]

Consider trying this. Create an orgchart with visio and then export it as
excel. Compare what Visio saves to what you are trying to give it.... Then
see if Visio can import the file that you just saved.

al

 

[merlin]

OK I tried that. It worked fine exporting and importing (except the
subordinate shapes imported and displayed in a different order, how annoying
is that). The excel file looks similar to mine.

This isn't a great surprise because I haven't had trouble importing data
from an excel file, it's the ODBC compliant import function that's causing
an error.

 

[Al Edlund]

two things. a.) what you saw with layout using the orgchart wizard is normal
b.) consider changing the name of excel file in your odbc setup to point to
the file that you just created and see if that file works.
al

 

[merlin]

OK. I'm crawling closer to cracking this.

I think I understand now that ODBC is a common interface that I have to set
up between two programs - in this case Visio and Excel. The actual process
of setting it up is eluding me.

1) is ODBC an actual file or is it something you do to the excel and/or
visio file?

2) if it's a file then where does it have to go. Can I choose or is there a
special location for it?

3) When I crack this I'll be doing it at work (practising on my laptop at
home at the mo) so can this file be on a network drive or does it have to be
local to my pc on the hard drive (I try to avaiod that). My choice of
network drives are a 'global' shared drive and a 'home' personal drive.

4) will I need administrator rights to achieve this?

Sorry for the volume of questions. This has turned out to be more complex
than expected but I not going to give up on it!

 

[Al Edlund]

Try this for a start definition
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/dasdkodbcoverview.asp

odbc (open database connectivity) are programming interfaces to allow access
to different data sources. They are a form of 'driver' and come in different
version depending on the vendors that supply them for their products (ms
generic, excel, access, oracle, db2, etc.).

for other than ms products support odbc drivers often come with the
product.. ms usually ships some basic ones with the operating systems and
office products. You can also check ms downloads for updates.

when network sharing data the two primary security groups are the networking
group (firewalling of application required flows) and data center (user
rights on servers). For this you will have to check in your own organization

 

[merlin]

Thanks for that - it should help my IT guys.

At home I've just managed to import from excel via odbc and create a visio
drawing (not an org chart) by following the MS knowledge base article. One
of the things I was doing wrong was not to define a table of data in my
excel sheets.I've also been able to update it but I had to go back through
the wizard because I couldn't find a refresh option that worked.

Now I need to achieve the same with an org chart, however, the way the data
is linked to the chart seems to be different and whereas there are various
refresh options in some drawing types, I can't find any for org chart.

You've been very helpful up to now - I hope you still have the patience to
see this through with me!

 

[Al Edlund]

the org chart wizard does not have a refresh, it's starts new every time -
so any customization gets lost....
al

 

[merlin]

There are definitely mentions of using ODBC to keep org chart shapes updated
with changes. If it's not done through the wizard then where do I need to
look?

As the org chart wizard isn't very tidy and doesn't allow pre-formatting as
such, I'd have to say this is a nearly useless facility without a refresh of
some kind.

 

[Al Edlund]

Since orgcharts are just another form flowcharts, it's not a stretch for
those with an interest in programming to take the example in the visio sdk
and build their own. The orgchart is essentially an addon to visio and as
such tends to change from version to version. Visio 2007 (and the rest of
the office stuff) is being released later this year and has a lot of new
data driven functionality (including I suspect a revised orgchart to play
with).
al

 

[merlin]

I'm very disappointed. I wish all the help files on ODBC pointed out that
you can't use this function for refreshing an org chart. It would have saves
us all a lot of time. I guess the ODBC function is avaiable on the org chart
wizard to allow connection to other databases. There doesn't seem to be any
point in importing an org chart via ODBCdata - might as well just use the
excel file!

I would try 2007 but as I can't use it at work it's of no help to me.

Unless anyone can tell me there's still a chance that an org chart can be
linked to excel for refreshing then I'm sadly going to have to give up on
this.

 

[Al Edlund]

It's not an odbc issue, it's the orgchart application..
al

 

[merlin]

So... unless anyone can tell me there's still a chance that an org chart
can be linked to excel for refreshing then I'm sadly going to have to give
up on this.

 

[Marianne]

Merlin & Al
I posted the same question two minutes before I saw this thread - my
frustration equals yours! I also have not been able to establish out whether
2007 would be an improved version.....

OrgPlus6 might be the way to go - I've seen it in action and was impressed
by the way it handled refreshing. It features a photo manager, conditional
formatting, legends, subcharts, levels etc. One can download an evaluation
version but .......
THE REFRESH DOESN'T WORK - application just hangs! Their technical team is
investigating ......

Every day a coconut - or are we just to optimistic about org charts in
general?

 

[Manticorr]

As a thought you might want to consider XML and schemas. Should be able to
do a nice updatable org chart using XML tags.