Unit Testing Error - The unit test adapter failed to connect to the data source or to read the data

Question

I'm using VSTS 2K8 and I've set up a Unit Test Project. In it, I have a test class with a method that does a simple assertion. I'm using an Excel 2007 spreadsheet as my data source.

My test method looks like this:

        [DataSource("System.Data.Odbc", 
        "Dsn=Excel Files;dbq=|DataDirectory|\\MyTestData.xlsx;defaultdir=C:\\TestData;driverid=1046;maxbuffersize=2048;pagetimeout=5", 
        "Sheet1", 
        DataAccessMethod.Sequential)]
    [DeploymentItem("MyTestData.xlsx")]
    [TestMethod()]
    public void State_Value_Is_Set()
    {
        string expected = "MD";
        string actual = TestContext.DataRow["State"] as string;
        Assert.AreEqual(expected, actual);
    }

As indicated in the method decoration attributes, my Excel spreadsheet is on my local C:/ Drive. In it, the sheet where all of my data is located is named "Sheet1".

I've copied the Excel spreadsheet into my project and I've set its Build Action = "Content" and I've set its Copy to Output Directory = "Copy if Newer".

When trying to run this simple unit test, I receive the following error:

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.

I've verified that the sheet name is spelled correctly (i.e. Sheet1) and I've verified that my data sources are set correctly.

Web searches haven't turned up much at all. And I'm totally stumped.

All help or input is appreciated!!!!

Answer 1

Between the great expanses of Google and Bing, I've been able to locate an answer:

http://msdn.microsoft.com/en-us/library/ms243192.aspx

Found using Key Search Terms:

"VS TEST Excel Data Source"

Answer 2

Change the "Build Action" property of Excel(.xlsx) to Content and "Copy to Output Directory" to Copy if newer.

This worked for me when I got the same error as urs...

++ also dont forget to browse and add the excel file in Deployment tab of TestRun.config file that is in the server soution..:)

Answer 3

I do not know if you have solved this, but this link helped me: