Loop through all .csv files in a folder (excel 2011)

[didj]

Hi everyone,

I want to open all csv files in a folder and just copy and paste some columns into another file. I am having a bit of trouble with the following code which can be found at ozgrid and I have modified slightly so it looks in the current directory instead of a fixed path...

Sub RunCodeOnAllFiles()
Dim lCount As Long
Dim MyDir as string
Dim wbResults As Workbook
Dim wbCodeBook As Workbook

    MyDir = CurDir()

On Error Resume Next
   Set wbCodeBook = ThisWorkbook
       With Application.FileSearch
           .NewSearch
           .LookIn = MyDir           
           .FileType = msoFileTypeExcelWorkbooks
           .Filename = "*.csv"
               
If .Execute > 0 Then 'Workbooks in folder
                   For lCount = 1 To .FoundFiles.Count 'Loop through all
 
'Open Workbook x and Set a Workbook variable to it
                       Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
                       
                       'MY COPY AND PASTE CODE GOES HERE

                       wbResults.Close SaveChanges:=False
                   Next lCount
               End If
       End With
On Error GoTo 0
End Sub

For starters, if I don't use on error resume next I get a runtime error 445: object doesn't support this action. So when I use the on error resume next, then by the time it gets to the foundfiles line, there are no variables set, so on the next line (workbooks.open) nothing happens.

Is there an easier (more up to date?) method to open all csv files (or excel files) in a folder?

 

[didj]

also would it be simpler to use.....

for each vaFileName in .foundfiles
workbooks.open vaFileName

or do I need to use

workbooks.opentext Filename:=vaFileName, Datatype:=xldelimited

etc etc

 

[alvinwlh]

There is a different between:

CurDir() & ActiveWorkbook.Path

 

[didj]

Thanks Alvin. It doesn't explain the runtime error though that I am getting at application.filesearch.

Anyone got any suggestions how to fix or improve this code?

 

[alvinwlh]

Ok, 2007 and above can try:

Your files are under fileArray

Sub GetFileLists()
 
    Dim FileArray() As Variant
    Dim FileCount As Integer
    Dim FileName, FileDir, FileSearch As String
 
 
 
    FileDir = "C:\Documents and Settings\Alvin Wong\Desktop\New Folder\"
    FileSearch = "*.xls"
 
    FileCount = 0
    FileName = Dir(FileDir & FileSearch)
    Do While FileName <> ""
        FileCount = FileCount + 1
        ReDim Preserve FileArray(1 To FileCount)
        FileArray(FileCount) = FileName
        FileName = Dir()
    Loop
End Sub

 

[didj]

Thanks Alvin! I found out that 2007 and above doesn't recognise application.filesearch but I also found another solution here which I have tested and is working nicely.

MyDir = CurDir()
 
sDir = Dir$(MyDir & "\" & "*.csv", vbNormal)
 
Do Until LenB(sDir) = 0
 
Set oWB = Workbooks.Open(sDir)
 
‘ Do some stuff here
 
oWB.close
 
sDir = Dir$
 
Loop

 

[alvinwlh]

Thanks