I need to grab data from specific cell ranges in an excel worksheet. The worksheet is protected so that the format will not change, but there are no named ranges. Is there any way to extract data from specific areas in the spreadsheet using SSIS 2005?
Excel Integration in SSIS Package Here's an older list of links I used when populating an excel spreadsheet versus extracting as you are hoping to do.
One of the biggest things I ran into is whether or not it's xls or xlsx (Office 2007). Maybe some of these will point you in a direction. I don't mean to imply that any of these will answer your question, but I'm pressed for time so I wanted to share the resources I found for my situation.
http://devcity.net/PrintArticle.aspx?ArticleID=239
http://jessicammoss.blogspot.com/2008/10/manipulating-excel-spreadsheets-in-ssis.html
http://msdn.microsoft.com/en-us/library/cc837974.aspx
http://www.sqlservercentral.com/Forums/Topic852934-364-1.aspx
http://stackoverflow.com/questions/863864/excel-2007-automation-on-top-of-a-windows-server-2008-x64
http://stackoverflow.com/questions/1051464/excel-interop-worksheet-or-worksheet
http://stackoverflow.com/questions/2012282/open-excel-file-on-a-specific-worksheet
http://bytes.com/topic/visual-basic-net/answers/385790-how-do-i-set-excel-cell-format-text-vb-net
http://vb.net-informations.com/excel-2007/vb.net_excel_page_format.htm
I dont have a lot of experience with SSIS and Excel but I would 'expect' to be able to either reference a range of cells by their position - A1:B6 to get 12 cells - or to actually open the worksheet and name a range from from a script object in the SSIS package - providing you are allowed the password for the file for this purpose.
