Excel 2007 DataSource connection error in Visual Studio 2008 unit test

Question

Hello,

I am trying to use an Excel 2007 as a data source in my VS 2008 Test Project a unit test.

Config file:

<configuration>
    <configSections>
        <section name="microsoft.visualstudio.testtools"
                 type="Microsoft.VisualStudio.TestTools.UnitTesting.TestConfigurationSection, Microsoft.VisualStudio.QualityTools.UnitTestFramework, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
    </configSections>
    <connectionStrings>
        <add name="MyExcelConn"
             connectionString="Dsn=Excel Files;dbq=SearchTestValues.xlsx;defaultdir=.; driverid=790;maxbuffersize=2048;pagetimeout=5"
             providerName="System.Data.Odbc" />
    </connectionStrings>
    <microsoft.visualstudio.testtools>
        <dataSources>
            <add name="MyExcelDataSource"
                 connectionString="MyExcelConn"
                 dataTableName="Sheet1$"
                 dataAccessMethod="Sequential"/>
        </dataSources>
    </microsoft.visualstudio.testtools>
</configuration>

My Test Code:

[TestMethod]
[DeploymentItem("SearchTestValues.xlsx")]
[DataSource("MyExcelDataSource")]
public void ShouldReturnResultsValidity()
{
  var minDate = (DateTime)TestContext.DataRow["MinDate"];
  var maxDate = (DateTime)TestContext.DataRow["MaxDate"];
  var minStatus = (int)TestContext.DataRow["MinStatus"];
  var maxStatus = (int)TestContext.DataRow["MaxStatus"];

  var criteria = new SearchCriteria(minDate, maxDate, minStatus, maxStatus);

  Assert.IsTrue(criteria.IsValid());
}

The excel file is in the root folder of the project. The file BuildAction is set to Content and Deploy is set to copy if newer. I also added the file to the list of Deployment Items so the excel file is deployed to the out folder of the TestResults folder when the test is run.

When I run this test, here is the error I get:

The unit test adapter failed to connect to the data source or to read the data. For more information on troubleshooting this error, see "Troubleshooting Data-Driven Unit Tests" (http://go.microsoft.com/fwlink/?LinkId=62412) in the MSDN Library.
Error details: ERROR [42S02] [Microsoft][ODBC Excel Driver] The Microsoft Office Access database engine could not find the object 'Sheet1$'.  Make sure the object exists and that you spell its name and the path name correctly.

Any ideas why I am getting this error?

Thanks

Answer 1

try

dataTableName="[Sheet1$]"

Answer 2

We had the same problem. We tried changing drivers, configurations, etc.

Certainly saving the Excel spreadsheet as 97-2003 compatible (a .xls rather that a .xlsx) solves the problem. Not sure if this is viable in your case.

Answer 3

To fix this problem you have got to change the properties of the Excel-File.

Build action: Content Copy to Output Directory: Copy if newer

Then it should work!

Answer 4

I had the same problem when deploying my tests with mstest.exe - installing VS 2008 on the target machine fixed it for me. . .