How to loop and open all csv files in my current folder in VBA

I randomly create a new folder on my desktop, in this folder I have one template file with .xlsm extension, which contains my VBA code. Meanwhile I have several csv files saved in the same folder with my raw data.

The purpose is looping through all those csv files one by one, open it, and copy some data and paste to my template file(I know how to do this part) from it and close it after all operations are done.

Currently I meet a problem about how to loop through my folder and open those csv one by one. I didn't set a specific folder name, since I want to share it with other people to use,therefore I use Application.ActiveWorkbook.Path to get the path for my current folder.

Here is my code:

Option Explicit Sub Range_End_Method() Dim Dir As String Dim i As String Application.ScreenUpdating = False Dir = Application.ActiveWorkbook.Path & "\" For Each i In Dir.Files Debug.Print i.Name If (i.Name Like "*.csv") Then Workbooks.Open (i.Path) End If Next End Sub 

2 Answers

I'm guessing you want to use the Dir function. To use that, make a call to it, specifying folder and file type in the first call, then call it empty until it returns an empty string. Like this:

Folder = Dir(Application.ActiveWorkbook.Path & "\*.csv") Do While Folder <> "" Debug.Print Folder Workbooks.Open Folder Folder = Dir() Loop 

You can use this function and macro. Juste replace MsgBox (myFile + "OK") by the action you want to execute.

FUNCTION

Function ClasseurOuvert(NomFich) On Error Resume Next Workbooks(NomFich).Activate If Err <> 0 Then Workbooks.Open FileName:=NomFich On Error GoTo 0 End Function 

MACRO

Sub LoopFiles() Dim myPath As String, myFile As String myPath = Application.ActiveWorkbook.Path & "\" myFile = Dir(myPath & "\*.*") Do While myFile <> "" And myFile Like "*.csv" Call ClasseurOuvert(myPath & "\" & myFile) With Workbooks(myFile) MsgBox (myFile + "OK") End With Workbooks(myFile).Save Workbooks(myFile).Close myFile = Dir() Loop End Sub