Add File Name as column in two sheets

[swathidas]

Hi ,

I have a folder C:\DataFiles, in this folder I have around 300 files, in each xls file there are two tabs Dev and Test.

I am trying to add a new column in both the sheets.

In Dev worksheet I have to create a filename in column F and in Test I have to create filename in column N.

Right now I am doing it manually, what I thought was I will use theActiveWorkbook.Name function to get the filename and fill it , I have created the below macro.

Please Login or Register  to view this content.

Please tell me how can I
a) add the filename to the columns as per the data present.(please see the atatchment), right now this macro is adding filename to all the columns in the xls.
b)how to call this function in both the sheets so that the filename will be inserted.

Thanks,
Swathi Das

[tigeravatar]

swathidas,

Give the following a try:

Please Login or Register  to view this content.

[swathidas]

Hi,

Thanks for the macro, I executed it but the macro did not add the file names in the xls files.

Thanks,
Swathi Das

[tigeravatar]

swathidas,

Heh, turned out it wasn't saving the changes >_< Try it this way where is does save the changes:

Please Login or Register  to view this content.

[swathidas]

Hi,

Where should I set this option in the code.

Thanks,
Swathi Das

[tigeravatar]

swathidas,

i'm not sure what you mean. If you mean the option to save the changes, its the wb.Close True line. The True indicates that we do want to save changes. In the prior code it was wb.Close False which indicates that we do not want to save changes, which was incorrect.

[swathidas]

Hey,

I am sorry , i got it. Thanks a lot.

Thanks,
Swathi Das