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.
Still receiving error when changed value of ExcelFilePath property to *.xlsm file
ReplyDelete"Unexpected error from external database driver ()."
Make sure you don't have the Excel file open anywhere. But that is an oddly non-descriptive error. Typically you'll get something a little better. Make sure it's an actual file name, not "*.xlsm" :)
ReplyDeleteWhat I sometimes do is: a) change my XLSM to an XLSX just by renaming it (and ignoring Windows' warning). b) Set up the Connection Manager to point to the renamed XLSX. c) Change the filename back to XLSM. d) Edit the ExcelFilePath property just by changing the last letter. That way I know for sure the property is set properly.
Hi Todd , could please share some sample code which reads data from xlsm and stores in oracle using vb or macro .I am a java guy ,so dnt have knowledge abt vb n macros.Would be great if you some sample code ..
ReplyDelete