Mass File Column Insert and Name

[Kirk P.]

I need to create a procedure that will loop through all files in a specified
path, insert a new column in each file called FileName, then write the actual
file name for that file into the column all the way down the list.

How can I accomplish this?

 

[Joel]

Sub addcolumns()

Const MyPath = "c:\temp"


First = True
Do
If First = True Then
Filename = Dir(MyPath & "\*.xls")
First = False
Else
Filename = Dir()
End If

If Filename <> "" Then
Workbooks.Open (MyPath & "\" & Filename)
Range("B1").EntireColumn.Insert

Range("B1:B100").Value = Filename


End If
Workbooks(Filename).Close
Loop While Filename <> ""

End Sub