I have been working with a number of clients recently setting up their Coded UI Tests to be data driven using Excel workbooks as their data source. There is a lot of articles out there showing how to use Excel as a data source, but they almost all reference the older Excel format, which is not based on the Open XML Format Spreadsheet file.
[DataSource("System.Data.Odbc",
"Dsn=Excel Files;" +
"Driver={Microsoft Excel Driver (*.xls)};" +
"dbq=|DataDirectory|\\DataSources\\UserData.xls;" +
"defaultdir=.;" +
"driverid=790;" +
"maxbuffersize=2048;" +
"pagetimeout=5;" +
"readonly=true",
"UserData$",
DataAccessMethod.Sequential)]
You can continue to use the old Excel Driver to access workbook, as long as you remember to save the file in the older format (Excel 97-2003 Workbook format). But if you’re like me, I always forget to do that and get the error that it can’t access the files, slap myself on the forehead and correct the format.
To save yourself the hassle, just save the file in the default format and use the correct data source.
[DataSource("System.Data.OleDb",
"Provider=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=|DataDirectory|\\DataSources\\UserData.xlsx;" +
"Persist Security Info=False;" +
"Extended Properties='Excel 12.0 Xml;HDR=YES'",
"UserData$",
DataAccessMethod.Sequential)]
If you get a connection error, it's most likely due to the fact that you don't have the 2007 Office System Driver installed for the OLEDB provider. You can download it from the following Microsoft link:
http://www.microsoft.com/en-us/download/details.aspx?id=23734
NOTE: In the sample connection strings, the Excel files are stored in a folder called DataSources and used a worksheet called UserData. If you were not aware, you need to put the dollar sign ($) after the name of the of the worksheet. If the worksheet already ends with a dollar sign, you will need to add another dollar sign after it (e.g., if the worksheet name is UserData$, the connection string would need to UserData$$).
Excelsior!