VBA to find all CSV files in a Variable Folder name

Question

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

FilePath is a variable, not a string. Remove the double-quotes. — Comintern
– Comintern, Commented Mar 21, 2017 at 21:33
Also if you're looking for CSV files your extension is wrong... See here for how to loop over all CSV files in a folder: stackoverflow.com/questions/25163369/… — Tim Williams
– Tim Williams, Commented Mar 21, 2017 at 21:37
1. Use FileMonth = Format(Date, "mmmm") to get the full month name. 2. Use Debug.Print FilePath before you try to open the CSV and look in hte VBE's Immediate window to make sure it is correct. 3. As above. — user4039065
– user4039065, Commented Mar 21, 2017 at 22:10
Oops. was in a rush. had several versions I was tinkering with and then copied the oldest here. Obviously it should be ".csv" — Scott H
– Scott H, Commented Mar 22, 2017 at 13:09

Sorceri · Accepted Answer · 2017-03-22 15:26:39Z

1

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

answered Mar 22, 2017 at 15:26

Sorceri

8,0932 gold badges32 silver badges40 bronze badges

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

4 Comments

Scott H Over a year ago

Oh I see... thanks so much. The above worked perfectly.

Scott H Over a year ago

so I recorded a Macro to parse out the CSV data. I'm trying to save the file and convert it to xlsx format. I want to preserve the CSV file name which was simply a date. I found all sorts of stuff on line but none seems to work. rec. Macro sets the file name.

Scott H Over a year ago

Convert = Dir(startPath & "*.xlsx") 'FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False ActiveWorkbook.SaveAs startPath & Convert Not working. . . .:/

Sorceri Over a year ago

@ScottH I would create a new question and post the code portion there so not to get the two issues confused in case someone comes here to review this one.

Collectives™ on Stack Overflow

VBA to find all CSV files in a Variable Folder name

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related