Inserting file name in an excel file

Topic

New Reply

I have several .csv files in a folder NEW

EQ200116
EQ210116
EQ220116
EQ230116
EQ250116
EQ270116
EQ280116
EQ290116

basically the filename consists of “EQ & date DDMMYY”.

I want to make a VBA excel that would put the date part in entire column N of the respective files.

attaching excel file for your reference

Reply | Quote

Replies

bhushanvshah,

In your sample, the sheet name is EQ290116 which is also one of the names of the files. I will assume that the data on the sheet is all the data contained in one file and that each file has its own sheet…correct? Click the button and column N will be filled with the date part of the sheet name. You did not specify that the macro include code to import the files.

HTH,
Maud

Code:

Public Sub AddColumnN()
LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
nme = Mid(ActiveSheet.Name, 3, Len(ActiveSheet.Name) – 2)
For I = 2 To LastRow
    Cells(I, 14) = nme
Next I
End Sub

Reply | Quote

Dear Maudibe,

Thanks for your reply..

i want to make it more simple.

all 30 csv files
EQ200116
EQ210116
EQ220116
EQ230116
EQ250116
EQ270116
EQ280116
EQ290116 etc are in the folder “C:NEW”

once i run the macro from a seperate excel file,
in each file i want to add the date component of the file name in column N (columns A to M contains data upto several rows)

thus it will become a fully automatic macro

rgds

Reply | Quote

thus it will become a fully automatic macro

Since you can’t store macros in CSV files, that’s not going to happen. In any event, you don’t really need to open the files in Excel to add the ‘date’ part to each line – that could be done via a vbscript that just processes the CSV files as text files (which is all they really are).

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote