July 14, 2009 at 2:33 pm
I have 2 issues:
1. How do I ignore the first variable number of header rows? I have a header caption I can search for and then I want all rows after that header.
2. How do I loop through many many tabs with unknown names?
TIA,
Blaise
July 14, 2009 at 2:39 pm
There's a way to select from the tabs collection of the Excel object. I don't remember what it is, but if you Google/Bing/Yahoo/whatever that concept, you should be able to find it.
Not sure on skipping a variable number of rows. You could import the whole thing as text into a staging table and manage that kind of thing from there, but not sure how to do it directly from the Excel source.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 15, 2009 at 12:59 pm
Thanks for the reply. I've found several samples showing how to loop through many Excel files but I haven't seen anything good on how to loop through many tabs in a single Excel file. The tab names will use a date as part of the name so it will continue to grow and the names will be unknown.
Looping through the tabs is my major problem now. Once I can do that then I will just export all of the content out to a CSV file and deal with the header and footer junk later.
Blaise
July 15, 2009 at 1:08 pm
I just searched this "excel worksheets collection" in Bing, and the first several results all had data on how to access the list of worksheets in an Excel file (worksheets = tabs).
You might have to build a CLR proc/function that opens an Excel automation session in order to get that. One of the pages has a VBA solution for it, and you might be able to translate that into VB.NET pretty easily.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 15, 2009 at 2:04 pm
I've figured out how to do the looping from this article:
http://msdn.microsoft.com/en-us/library/ms345182.aspx
It gives me back all tabs and named ranges. The latter is a problem but I will include a ScriptTask in the ForEach loop and attempt to exclude the named ranges.
Thanks for your replies.
July 16, 2009 at 7:11 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply