Excel VBA Importing CSV files from dynamic filename based on date

kirksmim

New Member
Joined
May 6, 2016
Messages
12
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.


Rich (BB code):
Sub OpenTextFile()
Rich (BB code):
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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"
 
Upvote 0
Pleased to read it helped and thank you for letting me know...
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top