question

Simone Burcombe avatar image
Simone Burcombe asked

No Named Ranges in Excel Source

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?

ssisexcel
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jason Crider avatar image
Jason Crider answered

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://dougbert.com/blogs/dougbert/archive/2009/02/13/exporting-data-from-ssis-to-excel-2007-gotchas.aspx

http://dougbert.com/blogs/dougbert/archive/2008/08/20/excel-the-office-12-ace-provider-and-performance.aspx

http://dougbert.com/blogs/dougbert/archive/2008/06/16/excel-in-integration-services-part-1-of-3-connections-and-components.aspx

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

http://www.dreamincode.net/forums/index.php?showtopic=39656

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 - for size !
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered

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.

2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I'm still going through the links from Jason to see if I can find anything that will help. But yes, that's exactly what I'd like to do: reference a range of cells by their position. The script object is less of an option for me, but if I can find an example I would be able to modify it for my needs.
0 Likes 0 ·
I didn't want to bother with scripting either, until I couldn't find another way to do it.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.