Excel VBA Importing CSV files from dynamic filename based on date

[kirksmim]

Hi all,

I have been asked to create a monthly report which will involve importing (as text) 5 different CSV files into 5 different sheets within an Excel template.

I want to write a piece of VBA that imports the files from a different folder every month according to the name of the folder, where the folders will be named after the month and year ("mmm-yy").

This is the code I have at the moment, and I want to change the 'Aug-16' (3rd line) to some sort of variable (something along the lines of =text(date(year(today()),month(today()),1),"mmm-yy"). Note this code is only for the import of one file however the other 4 files will all be found in this folder and will be called the same things every month.

Sub OpenTextFile()

Dim FilePath As String
FilePath = "H:\1 Work folder\Course Collection review\Holds\Macro test\Aug-16\MKS CC loans past 3 months.csv"
Open FilePath For Input As #1
row_number = 0
Do Until EOF(1)
Line Input #1, LineFromFile
LineItems = Split(LineFromFile, ",")
ActiveCell.Offset(row_number, 0).Value = LineItems(1)
ActiveCell.Offset(row_number, 1).Value = LineItems(0)


row_number = row_number + 1


Loop
Close #1


End Sub

(The original code was taken from: http://www.homeandlearn.org/open_a_text_file_in_vba.html)

I'm know I still have a lot of VBA to write until I get to what I want, but at the moment I am purely concerned with the variable date aspect.

Much Appreciated

 

[Trevor G]

Try adjusting the FilePath line to this

FilePath = "H:\1 Work folder\Course Collection review\Holds\Macro test\" & Format(Now,"mmm-yy") & "\MKS CC loans past 3 months.csv"

 

[kirksmim]

Fantastic! Thank you very much Trevor, worked a treat.

 

[Trevor G]

Pleased to read it helped and thank you for letting me know...