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

Question

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

I don't understand why you'd use VBA for this, when Power Query does it so well. — Olly
– Olly, Commented Nov 6, 2018 at 9:25
... as does powershell, batch, VBScript and any other number of scripting languages — Nick.Mc
– Nick.Mc, Commented Nov 8, 2018 at 5:46

Sam · Accepted Answer · 2018-11-06 09:40:53Z

1

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

answered Nov 6, 2018 at 9:40

Sam

5,7411 gold badge21 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

eleove · Accepted Answer · 2018-11-06 09:51:38Z

1

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

answered Nov 6, 2018 at 9:51

eleove

557 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related