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.