Long ago I blogged about using PowerShell to read out an XLS(X) file using ADO.Net. Until recently, only the x86 version of the ODBC driver was available.

Fortunately, Microsoft recently release a x64 version of the drivers as part of the Microsoft Access Database Engine 2010 Redistributable Package. Download link:

• MS Download: Microsoft Access Database Engine 2010 Redistributable

Interestingly, while this package is labeled “Office 2010”, the provider's names references “Office 12” (2007) –> this should have be released earlier :)

Marc

It’s critical to keep systems up-to-date with patches, the 4 issues described hereunder prove it again ;) They affect Windows Vista only, not 7 or XP. Certainly because the WebClient went through a serious revamp with Vista, Seven drawing benefit from product maturation.

Explorer View does not work when connection goes through a forward proxy asking for authentication 

When browsing a SharePoint site through a forward proxy (or simply web proxy) server requiring authentication, everything is working fine but when switching to Explorer viewing or simply trying to open an MS Office document, whether you directly get an “Access Denied” message or you get prompted multiple times for authentication (pop-up windows).

This problem occurs because early Vista’s implementation of the WebDAV redirector (aka WebClient) used by the Explorer do not handle correctly the HTTP Response 407 (Proxy Authentication Required)

Solution: install this hot fix (http://support.microsoft.com/kb/954807) or install Vista Service Pack 2. note: the hot fix requires SP1 to be present

Explorer View does not automatically forward credentials if the site does not belong to Local Intranet zone

A tricky one: let’s say that a user is browsing a SharePoint site that belongs to the Trusted Sites security zone of Internet Explorer while the browser is configured to automatically forward credentials for that zone (non-standard config). Although it will work fine with the browser, it will miserably fail with the Explorer View because on vista, WebClient does not rely on Internet Explorer security zones configuration and therefore does not automatically forward credentials under some circumstances.

Solution: Install this hot fix (http://support.microsoft.com/kb/943280) or install Vista Service Pack 2 and configure registry as described in the MS KB article related to the hot fix (this step is mandatory).

Explorer View does not automatically forward credentials if IE’s proxy setting check box “Automatically detect settings” is cleared

Pretty much derived from the previous issue, this one will behave identically.

Solution: install this hot fix (http://support.microsoft.com/kb/941853) or install Vista Service Pack 2

Explorer View might merge merge cookies leading to authentication issues (or other issues as well)

Cookies are often used to maintained state and sometimes to allow some kind of authentication mechanism, like form-based authentication.

In the case of authentication, products such as ISA/IAG/TMG may use so called “persistent” cookies to allow application to share authentication. This is typical when you want to seamlessly switch from a browser to an MS Office application when working with SharePoint.

Apparently, Vista’s implementation of the WebClient may accidently merge cookies when passing them to the web server or gateway, making them unusable.

Solution: install this Internet Explorer Cumulative Security Update (http://support.microsoft.com/kb/972260/), which also includes functional updates solving that problem…

Credits go to Pascal B and Nicolas S both MSFT for this one. Thanks guys!

Marc

Nothing new here, just a repost by popular demand... 
Many examples on the Internet show how to use the Office Automation COM object to achieve this. But under some circumstances, this is not possible because Excel is simply not installed locally.

More information can be found here:

• http://support.microsoft.com/kb/311731
• http://support.microsoft.com/kb/316934
• http://msdn2.microsoft.com/en-us/library/system.data.oledb(VS.71).aspx
• http://msdn2.microsoft.com/en-us/library/system.data(VS.71).aspx
• http://msdn2.microsoft.com/en-us/library/ms254500(VS.80).aspx

Note: This was not tested using an XLSX files but with a standard XLS instead
Note 2: DataSet can be used instead of DataTable object but I prefer this one in favor of greater simplicity (this is to be used by sysadmins, not developersJ)
Note 3: I blogged about reading but you can update and insert too, see references above for details

And cut!