How to loop thorugh excel files

try to create a new SSIS project
add a for each loop component to the control flow to get all the excel files from a specific folder
inside it, add a dataflow
inside dataflow try to set the connection source dinamically to check the sheet name. if you cannot do like that, try using a scriot task source to do the job.
helped?
regards

Hadush,
Use something like the snippet below.
Hope it helps
Patrick

Sub Macro3()
Dim ws As Worksheet
 
For Each ws In Worksheets
    Select Case ws.Name
        Case Is = "Old"
            'do something
        Case Is = "New"
            'do something
        Case Is = "Not sure"
            'do something
        Case Else
            'do something if the other cases are not met
    End Select
Next ws
                        
End Sub

Select allOpen in new window

Thank you both PedroCGD and patrickab,
I had created this package long time a go  to download zip files, unzip using script, and do some transformations and populate to the traget database.
There are only two possible sheet names and one file can only have only one of them not both.
The problem is, I couldn't get the script done to read sheet name.

Do you want an example of a script source?
Regards

Hadush,
Does that now mean to say that you have got the code you need in my macro?
Patrick

It could be as below...

Sub Macro3()
Dim ws As Worksheet
 
For Each ws In Worksheets
    Select Case ws.Name
        Case Is = "Sheet1"
            'do something
        Case Is = "Sheet2"
            'do something
        Case Else
            'do something or nothing if the other cases are not met
    End Select
Next ws
                        
End Sub

Select allOpen in new window

Thanks every one,
yes PedroCGD, I would love to have script example that can be used SSIS script.
patrickab: I tried the script ofcourse by changing the condition and couldn't get it to work.
Thanks again

If I understand correctly you are looping through a set of workbooks looking for sheet x.  Now you want to look at either sheet x or sheet y whichever is found.

The function below will fid either if they exist by calling:
dim ws as worksheet
set ws = wbfindifsheet(thisworkbook).Name
if ws is nothing then something went wrong and neither sheet was found otherwisews will be one of the two sheets and you can process it however you want.

Replace sheetxx & sheetyy in the function for the names of your two sheets of interest.

Chris

Function findifSheet(wb As Workbook) As Worksheet
On Error Resume Next
    Set findifSheet = wb.Sheets("Sheetxx")
    If findifSheet Is Nothing Then Set findifSheet = wb.Sheets("yy")
On Error GoTo 0
End Function

Select allOpen in new window

Thanks chris_bottomley!
I will try with this function and update every one if I able to make it work.

Thank you so much PedroCGD, for your time and pateince.
You went extra miles to help.
With big respect :)

Hadush,
I'm here to help people, and I'm not in a competition. I'm very happy you resolved your problem. If you want to thank, you can do visiting my blog a post a comment!
Regards!!!
Pedro