How to loop excel files using foreachloop container in integration services?

Question

i want to loop excel files and to get data from that loopfiles

Answer 1

I think an easier way to loop through a group of files in a single folder is to use the Dir function.

Use:

Sub LoopFiles()

Const PATH = "path with final back slash"

Dim sFileName, sPathFileName As String

sFileName = Dir$(PATH + "*.*")

Do While Len(sFileName) > 0

sPathFileName = PATH + sFileName

MsgBox sPathFileName

sFileName = Dir$

Loop

End Sub

to test the loop code and then replace MsgBox sPathFileName with the file processing code.  Be sure to not use any parameters with the second DIR$.

____________________________________

Or you can use for each to get the file names as indicated below

Sub LoopFiles()

Dim sFile As Variant

With Application.FileSearch

    .NewSearch

    .LookIn = "path without final backslash"

    .SearchSubFolders = True

    .Filename = "*.*"

    .MatchTextExactly = True

    .FileType = msoFileTypeAllFiles

    .Execute

    For Each sFile In .FoundFiles

    MsgBox sFile

    Next

End With

End Sub

Note that what you are retrieving here also is file names not file objects.  In this code, after testing, replace Msgbox sFile with the file processing.  Also sFile must be dimensioned as Variant (not String) for the code to work.