Excel 2010 (and presumably the next version of Excel) have taken some security precautions with regards to macros in Excel files.  If a spreadsheet has macros in it, you can't save it as an XLSX.  Excel forces you to save it with an extension of XLSM, indicating that the file has macros in it.  The assumption being, I suppose, to make it a little harder to sneak a malicious macro under a user's nose when they're not expecting one.
The Problem
This causes problems with SQL Server Integration Services 2005 and 2008, because the developers didn't take macro-enabled files into consideration when they designed the Excel Connection Manager.  If you attempt to use a macro-enabled Excel file name in the Connection Manager, it will report that the path "contains invalid Excel File" (bad English too).  It recommends you identify a file with an XLS or XLSX extension.
The Solution
Point the Connection Manager to a valid Excel XLSX file - any one will do, and save it.
Edit the Connection Manager properties (press F4) and change the ExcelFilePath property to your actual macro-enabled Excel file.  Consider SSIS fooled.