how to batch processing excel worksheet modifications?

[neptune]

Hi,

I hope that anyone can help me. I have 300 excel files to process, I
don't what to have to open all these files one by one. What I have to
do is the following: I have to modify the excel files in some simple
way, add a column and insert the filename of the corresponding file in
this column for every line items of my excel file.

example :

dir \name_my_excels\ has 2 files, fileA(5 lines) and fileB(10 lines)

To do:

open fileA, add a column, insert the filename in the columnF for the 5
active lines and close the file,

open fileB, add a column, insert the filename in the columnF for the 10
active lines and close the file,

and so on, this for 300 times.

Is there a way that I can do this on one files and batch process my
directory to have it done on all my excel files?

A quick respond an a software, macro or anything like this would same
me time and energy.

Thanks a lot,

Marc
ps. Can anyone cross post this message for me, I'm new to groups

 

[Earl Kiosterud]

Neptune,

A macro could do it. It would have to be written, and you'd have to be able
to put it into a workbook. You'd want to put a copy of the files in a
separate folder (certainly don't start processing your only copy of the
files).

 

[marko]

I believe a macro can do this, but you've got to be a lot clearer on
what you want it to do. If you're going to write the macro, I would
have a flowchart of what needs to be done here. What is the
'corresponding filename' you're referring to. That's what really threw
me.

 

[Ken Macksey]

Hi

Be sure to make back up copies all wokbooks first.
Always work with copies of the workbooks
and test your code until you are satisfied
that everything works the way you want it to.

This code will go thru all of the workbooks in a folder.

Write the specific code you need to be performed and insert it where shown.
Then test,test,test until you are sure!!!





Application.ScreenUpdating = False
Application.DisplayAlerts = False

With Application.FileSearch
.NewSearch
.LookIn = "C:\temp 1" ' your drive / directory here
.SearchSubFolders = True
.FileName = ".xls" ' all files ending in xls
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
' how many files are there in the selected folder?
MsgBox "There were " & .FoundFiles.Count & " file(s) found."

For i = 1 To .FoundFiles.Count
Workbooks.Open .FoundFiles(i), 0
'
'The code to select a column
'
'and insert a column
'
'and insert the file name
'
'in the columns and
'
'whatever else you need to do goes here.
'
ActiveWorkbook.Save
ActiveWorkbook.Close
Next i
Else
MsgBox "There were no files found."
End If
End With


Application.ScreenUpdating = True
Application.DisplayAlerts = True

MsgBox "All Done!"