Crystal has a native driver that lets you read data stored in older Excel spreadsheets (XLS). But if you want to read data stored in the current spread format (XLSX) then you will need to get an OLEDB/ODBC driver for Excel. I have had to do this recently for two customers and it took me a while to find the correct driver. So to make it easier for the next time I am going to put the link here.
Part of the problem is that the Excel driver is part of a larger set of drivers included in the Microsoft Access Database Engine 2010 Redistributable. This includes both ODBC and OLEDB drivers for Access (MDB/ACCDB) and Excel (XLS/XLSX). When you start the download there will be two options:
AccessDatabaseEngine.exe (32 bit)
Even though you most likely have a 64 bit PC you should get the 32 bit driver. That is because Crystal Reports is still a 32-bit application and can’t read ODBC connections that are created using a 64-bit driver.
Once you have installed the 32-bit driver you can read my previous blog post on how to launch the 32-bit ODBC Administrator on a 64-bit computer, or you can skip over that confusion and use the OLEDB driver. The ODBC configuration is much more confusing than it should be.
And thanks to Adam Butt of APB Reports in Norway for showing me how much simpler OLEDB is over ODBC.