hi, I am using excel 2003, I have a folder containing around 50 excel files which generated from some other programme. Now I want to add part of the filename in a particular coulmn of the each excel worksheet further processing of the data. Kindly guide me using filename function


for eg : My excel filename is graphacc.xls, i want copy only "acc" in a column for the entire data range say a1:a50.similarly in file graphaxisbank.xls, I want to copy :axisbank: in a column for the entire data range say a1:a100. I tried using the following code, i get error "#value"


Option Explicit
Sub GetFilenames()
' Stanley D Grom, 04/12/2010
Dim MyDir As String, FN As String
Application.ScreenUpdating = False

'********** Change the path to suit your environment **********
MyDir = "C:\TestData\"

'MyDir = "C:\FLDR 1\"

FN = Dir(MyDir & "\*.xls?")
Do While FN <> ""
If FN <> ThisWorkbook.Name Then
With Workbooks.Open(MyDir & FN)
With .Sheets(1)
.Range("A1").Formula = "=MID(CELL(""filename""),SEARCH(""["",CELL(""filename""))+1, SEARCH(""]"",CELL(""filename""))-SEARCH(""["",CELL(""filename""))-1)"
End With
.Save
.Close
End With
End If
FN = Dir
Loop
Application.ScreenUpdating = False
End Sub


Kindly help me


thanks