xlsm into sql server

Question

I have an xlsm file with macros that enable data collection from OLE DB source. I just need to make an import package with it in sql server. But when I'm trying to use import/export wizard or ssis-it shows that it can not deal with .xlsm extentions. Is there any way to deal with it?

Answer 1

follow this : 1-Create an Excel Connection Manager to refer to ANY other spreadsheet with a "valid" XLS or XLSX extension. (You don't have to save your XLSM file "as" an XLSX.) 2-Select the Excel Connection Manager in your Connection Managers pane. 3-Open the Properties Window (F4). 4-Change the "Server Name" property to the full pathname of your XLSM file.

note: check the file should not be in read only mode

Answer 2

Had the same issue (SQL Server 2008 R2), Rahul's solution worked for me, but I just want to add a couple of things:

• Select Excel 2007 in Excel version when creating Excel Connection.
• Use SQL Command as Data access mode when editing the Excel Source. Visual Studio crashed for me every time I was trying to use Table or view.