Find the name of a worksheet using activeX inside DTS

[GJP55]

Does anybody know the correct code to identify a worksheet name from an Excel file ?. I am writing code to look at each worksheet in an excel file and will loop it through each one during an import.

Code I have so far but this does not work with the worksheet section.

Thanks



Function Main

Set MsExcObj = CreateObject("Excel.Application")
Set wbk = GetObject("J:\Precedent Decisions by country.xls")
Set Wsht = wbk.Worksheets

For Each Wsht In wbk
msgbox wbk.name
Next

Set wbk = Nothing
Set MsExcObj = Nothing
Set Wsht = Nothing

Main = DTSTaskExecResult_Success
End Function

 

[gmmastros]

I don't know much about DTS, but I have some VB code that will enumerate a list of excel worksheets from a workbook. This approach may work for you, and if not, perhaps it's a least a starting point.

Dim DB As ADODB.Connection
Dim RS As ADODB.Recordset

Set DB = CreateObject("ADODB.Connection")
DB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[!]C:\tektips.xls[/!];Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
Call DB.Open

Set RS = DB.OpenSchema(adSchemaTables)

While Not RS.EOF
    Debug.Print RS.Fields.Item("Table_Name").Value
    RS.MoveNext
Wend
Call RS.Close
Set RS = Nothing

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[GJP55]

Hi,

Thanks for your reply. Definately in the right direction there.

Thanks