CORRECTED ON 4/2/09 FROM ORIGINAL POSTING ON 3/31/09
The Office 12 ACE Provider recognizes the .XLSM extension for a macro-enabled Excel 2007 workbook.
However, the Excel Connection Manager (in SQL Server 2008 Integration Services) only recognizes the .XLSX file extension for Excel 2007 files. Therefore you have to use the OLE DB Connection Manager (and Source and Destination) with Excel 2007 files that have a file type and file extension other than .XLSX.
Importing. You can connect to, and import from, a macro-enabled Excel 2007 workbook (*.xlsm). You do not have to change the file extension temporarily to *.xlsx, which is what the original version of this posting suggested.
Exporting. However, the Provider does not fully support exporting to the .XLSM format, since it would not make sense for the provider to be writing out Visual Basic code, as implied by "macro-enabled". I was not able to export successfully to an existing .XLSM workbook in a quick test using the SQL Server Import and Export Wizard.
-Doug