PowerPoint Linking to Excel Issue

When updating a link (right click Update Link) or editing a linked object (double click on the object) in PowerPoint the user receives a dialog box (shown below) stating “The linked file was unavailable and can’t be updated" . Here is a document I wrote that describes how to fix this issue. We encountered this issue in Excel 2007 sp2.

New OLEDB Provider for Reading Excel 2007

I work for a financial company that uses alot of Excel. Many of the the business users here practically live in it. So we are constantly trying to figure out how to leverage Excel in our applications.

Do we just export data to Excel? If so, then is it a snapshot/copy of the data or do we build a connection to the backend data? What about importing data? Where is the boundry between using VBA and VSTO? Then if we pile SharePoint and Excel Services on this heap it starts to get really interesting.

One of our technical frustrations has been the OLEDB driver for reading Excel on the server was fairly lame. It made alot of assumptions about the data that made it nearly unusable except in the simpliest cases. Last week I found this updated Provider for Excel 2007 and I am looking forward to giving it a deeper look. What I can say is, that it did read in all my data rather easily. I just have not had time to play around the fringes much.

Download details: 2007 Office System Driver: Data Connectivity Components
href="http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

This made me laugh

A friend I have not heard from in a couple months sent me an email yesterday with this as the subject. Yeah the subject! After I stopped laughing, I opened the email. Thought I would share this one…

1. Ninjas are mammals.


2. Ninjas fight ALL the time.


3. The purpose of the ninja is to flip out and kill people.

UI Testing

I spent a bunch of time in the early Windows days trying to do UI testing the way this (UI Test Automation Tools are Snake Oil) blog entry talks about. Like him (or his clients) we used some really expensive tools and ended up not doing a very good job. I really like the thinking Michael is doing here. This is definately where my head is at. The problem is that I am struggling with creating MVC-style applications.

Where I work now we just don't build big applications. Instead we have lots of small applications that we deliver in weeks not months or years. I have not found this pattern of doing software very condusive for building applications with lots of design. Now hold on a minute - that does not mean we don't do design. We just don't do lots of design. When an application is very small how much design do you really need to do? Most of the applications tend to look like each other - read some data...munge it together...display it. We don't do much data entry; which is an exception in the pattern of apps I have built over the years.

That is not to say we don't have some big-ish applications. We do. Just that they are the exception. Could they do with more engineering? Absolutely! But we just don't have the infrastructure (staff, mindshare, experience, etc) to do it that way. Of course there are people doing a high level of engineering here. It's just that it's not everyone - it's not our default.

At first this was a hard pill to swallow (and it still makes me a little gassy at times). But it's the nature/culture of the way we do things. It's a model that works, but not in a scaleable way. Sharing anything in this model is very hard - maybe I will blog later about how we do that.

Old Problem – New Context

[Here is an old post I found that I had never published. I think it's because I could not get the formatting of the code to look "right". This time I published from Word and the formatting was still not preserved - rats! Also, the article is not as complete as I would like it but I wanted to get the code into the blogosphere.]

[There are a few technical things wrong with this post. The first is that using Thread Local Storage is risk (at best) in an ASP.NET application. See http://www.hanselman.com/blog/CommentView.aspx?guid=320. The second is that I would prefer to use a different pattern than the one below; something a little more elegant. Lastly, after using LINQ much more I don't know that this code is even a best practice; I need to noodle on this more.]

I keep thinking that there are like a dozen or so abstract problems that I solve and everything else is just another derivation from those. This is one of those dozen problems – what is context and why do we need it so often?

We have been using a lot of LINQ lately. While using LINQ to SQL I ran into a problem a few weeks ago and created a little hack to work around it. Well we are starting to roll LINQ out to the rest of the developers we need more than a hack for this issue. What is the issue, well it's the same we have had in the last 4 ADO stacks - how do I reuse a connection when I have a transaction open. The only time I have not had to work around this issue was in MTS/MTX and when we used the Microsoft Enterprise Library (EntLib)

The way MTX and EntLib get around this is that they are a "layer" in between me (you) and the underlying infrastructure. I remember Bernie's old saying - "Interesting things can happen when you can get in between things". Another way of saying that layers or interception are something that are a key part of any design.

So here is what I want the client/consuming code to look like.

const
string CN = "Server=localhost;Database=AdventureWorks2008;Trusted_Connection=true";

using (var aTrx = new TransactionScope())

{

var aCtx = new AdventureWorksDataContext(CN);

var x = from p in aCtx.Persons

where p.FirstName.StartsWith("A")

select p;

var y = x.First();

var aCtx2 = new AdventureWorksDataContext(CN);

var x2 = from p in aCtx2.Persons

where p.FirstName.StartsWith("A")

select p;

var y2 = x2.First();

}

So I wrote a little class that caches the connections so that they can be reused across LINQ statements. If we don't do it this way then we are invoking the Distributed Transaction Coordinator (DTC). We only want to do this when the statements need to be in the same transaction.

public
static
class
TransactionalDataContext

{

[ThreadStatic]

private
static Hashtable mConnections;

public
static IDbConnection DataContext(string pCnStr)

{

var aTrx = Transaction.Current;

if (aTrx == null)

{

return CreateConnection(pCnStr);

}

if (mConnections == null)

{

mConnections = new Hashtable();

}

if (mConnections.ContainsKey(pCnStr))

{

return (IDbConnection)mConnections[pCnStr];

}

var aCn = CreateConnection(pCnStr);

mConnections[pCnStr] = aCn;

aTrx.TransactionCompleted += TransactionCompleted;

return aCn;

}

private
static IDbConnection CreateConnection(string pCnStr)

{

// cn str is name value pairs with '=' and ';' separating each pair

// get each of the pairs

var aCnStrPairs = pCnStr.Split(new[] { ';' });

// find the pair with 'Provider'

var aProvPairEnum = aCnStrPairs.Where(i => string.Compare(i, "Provider", true) == 0);

IDbConnection aCn;

if (aProvPairEnum.Count() == 0)

{

// no provider specified so assume MS SQL Server

aCn = new SqlConnection(pCnStr);

}

else

{

// get the provider name

var aProvPair = aProvPairEnum.First();

var aProvNvp = aProvPair.Split(new[] { '=' });

// create a connection

var aFactory = System.Data.Common.DbProviderFactories.GetFactory(aProvNvp[1]);

aCn = aFactory.CreateConnection();

aCn.ConnectionString = pCnStr;

}

// we open the cn so that LINQ does not - this will also signal to LINQ not to close the cn

aCn.Open();

return aCn;

}

// handler for when the trx is complete

private
static
void TransactionCompleted(object sender, TransactionEventArgs e)

{

try

{

// dispose of each connection

foreach (DictionaryEntry aEntry in mConnections)

{

try

{

((IDbConnection)aEntry.Value).Dispose();

}

catch { }

}

}

finally

{

// reset the collection, in case doing another on this thread

mConnections.Clear;

}

}

}

Outlook Rules

After reading the title to this entry I had to laugh. So am I stating an opinion on how I feel about Outlook (it rules!) or something else? When it the electrons from my brain told my finger tips to type this title I was definitely thinking something else. Hey man – words do mean something.

So after reading Hanselman's blog entry (http://www.hanselman.com/blog/TheThreeMostImportantOutlookRulesForProcessingMail.aspx) over the Holiday about processing Outlook rules; I was inspired to revisit my own set of rules. When I was working at Microsoft (as Scott now does) I was totally overwhelmed with the amount of internal information flowing around the company; and at the time most of it was through email distribution lists. Between the stuff directed at me as part of my job (Microsoft Consulting) and the internal chatter; I was getting hundreds of emails a day. Without a good set of rules it would have been impossible to get through everything in my inbox.

That practice has stayed with me ever since and has served me well over the years. Two things have happened recently that I wanted to write about with regard to Outlook.

First I wanted to record this (http://office.microsoft.com/en-us/outlook/HA103869131033.aspx?pid=CH102499821033) article called "Outlook Meeting Requests: Essential Do's and Don'ts". As a long time Outlook user; I already do many of the things in this article. But there were some other nuggets in there that I found useful; especially around using rules on meeting requests. One thing I have been looking all over for and found on this page was how to turn off the acknowledgement to a meeting invite. There are a few meetings that I invite 20+ people to and I don't need to know who has accepted and who has not. I had a rule just to catch all of these responses and move them off to a folder.

Secondly, I wanted to mention that about 4 months ago I became a Crackberry user for my work email. There were many things happening at work that I just needed to stay apprised of during the time I was not logged in. Well this changed the way that I wrote many of my rules. Suddenly I was not just filtering things so that my Inbox was less cluttered (and relatively prioritized). Now I needed more because the Blackberry (Bb) is synchronizing with my Inbox and my hip was buzzing for every email.

Side note – I cannot tell you how annoying it is that the Bb buzzes about 7 seconds before an email arrives in my Inbox. As if the little bubble pop-up isn't enough of a distraction!!

So now I have two Inboxes. I have a series of rules that move everything EXCEPT the "hey a server is down" type of emails to my secondary inbox. The trick is to do this given the fact that the Bb service (on the Exchange server) sees only the emails that fall off of the server side rules waterfall. So I have been using the "Move" and "Stop Processing More Rules" combination much more. There are also some rules that are order dependent; which I am not happy with.

But the whole thing works relatively well now. It's funny how refactoring my rules for the Bb may have actually made them better. Kind of like my code/designs; sometimes it's not until a new requirement comes along that I realize a better way. As Hannibal Smith used to say (The A-Team) – "I love it when a plan comes together".

What I would really love is for the Blackberry guys to give me a way to interact with the Exchange service running on the server. Let me write some rules separately, just for the device. I would not be at all surprised if such a thing exists and we are just not "with it" enough to have deployed it. It's probably for the same reason that we deploy WindowsXP with the old Start Menu configuration (no MRU etc). Because someone felt that it would be just too hard and confusing to the users to change anything. Now that we have rolled out Office 2007 I better not hear that excuse ever again!!