Get the name of an excel sheet dynamically in dts

Question

Post reply

Get the name of an excel sheet dynamically in dts

dcordle

SSC-Addicted

Points: 469
More actions
November 7, 2008 at 12:55 pm

#72093

I've got an excel file that's sent to us with a changing file name and a changing sheet name. I can handle the pattern matching for the file name in an activex task, but I can't quite figure out how to check the names of the sheet(s) in the workbook to find the one that matches the pattern I'm looking for and set the global variable to it so that I can use it later in the package.
Ex.
File name today: clientdata_342395.xls
sheet name today: clientdata_4333243453
file name next week: clientdata_43432134214.xls
sheet name next week: clientdata_34134143434312
Any help would be appreciated.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 1

Hi,

Check out the attachment, it's a SP that I've built to get all the sheets' names from an Excel workbook.

HTH,

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 2

HI

Hopefully you are aware of the problems with format when you import data from Excel

with DTS or SSIS. I would do the pattern matching in VBA and save the file as a TAB sep textfile and use bulk insert.

But you can in DTS with VB script do things in Excel. There are a lot of examples on the Web.

Just google on something like VBscript Excel create object.

Gosta

dcordle SSC-Addicted Points: 469 More actions · Answer 3

Gosta Munktell (11/11/2008)
HI
Hopefully you are aware of the problems with format when you import data from Excel
with DTS or SSIS. I would do the pattern matching in VBA and save the file as a TAB sep textfile and use bulk insert.
But you can in DTS with VB script do things in Excel. There are a lot of examples on the Web.
Just google on something like VBscript Excel create object.
Gosta

Yes, I am more than aware of the shortcomings with DTS and Excel, been fighting them for quite a while. VBA is not an option, I need to keep the entire process contained in a DTS package. My thought would be to use ADODB to open a connection to the excel file, but I have no idea how to get a sheet name dynamically from the file. This is where I need help.

thanks.

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 4

"VBA is no alternative"

Ok I understand your dilemma. But as VBScript is an integrated part of DTS you can develope

in VBA and migrate to VBScript. I can help you more if you like this procedure.

//Gosta

You can create an Active X script like below and contect to a database etc.

' Visual Basic ActiveX Script

'************************************************************************

dim excelapp

Function Main()

Set excelapp = CreateObject("Excel.Application")

'Set worksheet = CreateObject("Excel.sheet")

excelapp.visible = true

excelapp.Workbooks.Open "C:\Book1.xls"

'excelapp.Worksheets("Sheet1").Copy

'excelapp.Worksheets.add

'excelapp.ActiveSheet.Paste

excelapp.Cells.Select

excelapp.Selection.Copy

excelapp.Sheets.Add

excelapp.Sheets("Sheet2").Select

excelapp.Range("A1").Select

excelapp.ActiveSheet.Paste

Main = DTSTaskExecResult_Success

End Function