CODEWITHSUNDEEP
vbaexcelcsv
Scott H
Scott H

Reputation: 69

VBA to find all CSV files in a Variable Folder name

I have a project to create VBA to find and open all CSV files in a folder that is named the current month. I found some stuff online that seemed close but not quite. They will eventually be converted to XLSX files and parsed. That part I have. The macro that converts, parses and saves will be housed in a different file along the same path but not as "deep".

So on my Desktop is a folder name "CSV find test". Inside are two folders "Feburary" and "March". I need it to open all csv files in the most recent month. I have the rest of the syntax. . . . .

I wouldn't imagine that it would take a huge amount of syntax. Thanks for any direction.

Sub OpenFile()

    FileMonth = Month(Date)
    FileDate = Format(Date, "mmmm")
    FilePath = "C:\Users\Me\Desktop\CSV find convert tests\" & FileMonth & "\" & FileDate & ".xls"

    Workbooks.Open Filename:="FilePath" <- - -  - error happens here.

End Sub

Upvotes: 0

Views: 4182

Answers (1)

Sorceri
Sorceri

Reputation: 8033

I don't think you really understand how a variable works as you keep putting it in a string. If you put something in double quotes it creates a string. Below is how you can add the month to the string via the variable.

Sub OpenCSVs()
Dim MyFiles As String, ThisMonth As String
Dim startPath As String
ThisMonth = Format(Date, "mmmm")
startPath = "C:\Users\ME\Desktop\CSV find convert tests\" & ThisMonth & "\"
MyFiles = Dir(startPath & "*.csv")

Do While MyFiles <> ""

Workbooks.Open startPath & MyFiles

       'Do stuff to it will go here
'ActiveWorkbook.Close SaveChanges:=True (Deactivated for now)

MyFiles = Dir

Loop

End Sub

Upvotes: 1

Related Questions