Excel worksheet into global variable for dynamic connection

[DanC]

I'm trying to get the worksheet name from an excel workbook to pass into a global variable to be used to dynamically change the import task for importing the sheet. I can get the filename and path using the File System Object, but I can't seem to be able to get the first worksheet's name. Here's the code (probably not the best way)

Function Main()
Dim fsoObject, objFolder, objFile, colFiles, strFileName

Set fsoObject = CreateObject("Scripting.FileSystemObject&quot
Set objFolder = fsoObject.GetFolder("c:\projects\client\source\&quot
Set colFiles = objFolder.Files


If colFiles.Count > 0 Then
For Each objFile in colFiles
If left(objFile.Name,4) = "prod" Then
strFileName = objFile.Name
End If
Next
End If

DTSGlobalVariables("FileName&quot.value = strFileName
DTSGlobalVariables("FilePath&quot.value = objFolder & "\" & strFileName
DTSGlobalVariables("SheetName&quot.value =

Main = DTSTaskExecResult_Success

Set fsoObject = nothing
Set objFolder = nothing
Set objFile = nothing
Set colFiles = nothing

End Function