Import from a macro-enabled Excel 2007 workbook (*.xlsm) (CORRECTED)


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.



Comments (4) -

Todd McDermid

Doug - my Excel Connection Manager (in 2008) rejects use of non-XLS/XLSX filenames.  (This is the Connection String: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Sauder Projects\dev\net\Test SSIS\Test.xlsx;Extended Properties="EXCEL 12.0;HDR=YES";)

Am I using the wrong provider?

Todd McDermid

Doug - more info that I can find: on Connect -">

Tell us they're wrong!



The Excel connection manager only recognizes the .XLSX file extension for Excel 2007 files, you're right. You have to use the OLE DB connection manager, source, and destination for Excel 2007 files with other extensions. I've added this above to the text of the blog post.

The number of votes on Connect issues may affect what gets fixed and what doesn't, as I mentioned in another blog post. Readers who want to work more conveniently with Excel in SSIS, take note.


Carla Sabotta

According to this recent post by Devin Knight, you can use Excel Macro (.xlsm) enabled files in both SSIS 2008 and 2012.

For both versions, you need to install Microsoft Access Database Engine 2010 Redistributable driver ( )

In 2008, as Doug mentions, you have to use an OLE DB Connection Manager and an OLE DB Source (to select a worksheet or query the work book). And, you need to modify the Extended Properties to add Excel 12.0 Macro;HDR=Yes|NO.

In 2012, the Excel Connection Manager and Excel Source support the .xlsm files. And, you don't need to modify the Extended Properties.