Opening files with vba

[kenman]

I have about 1000 excel files in a folder. I am looking to be able to open them, one bby one, with a VBA program so that I can alter some data(delete col and or rows) etc. and then save the file, close it, and then open the next file in the folder, make the changes etc. until all the files have been worked on.

Is there any way to do this in VBA rather than have to open each file manually, run the VBA that will make the changes, then save and close manually until all are done?

I would also like to change the files name, but if that makes the program too difficult, I can manage withouot that.

Thanks

You guys, and gals(I'm sure there is a Mrs.Excel somewhere) are the best.

Kenman

 

[Tom Urtis]

Something along these lines might be what you want. It's possible to change the workbook name but since only one workbook per name may be located in the same folder and you have many workbooks to deal with, you'd presumably tie that into an incremental variable, such as perhaps the "x" variable in this case, where the code line would be for example (edited)
ActiveWorkbook.SaveAs sPath & "\" & "NewName" & x & ".xls"



Sub Test1()
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False

Dim sPath$, x&
sPath = "C:\Your\File\Path"

With .FileSearch
.LookIn = sPath
.FileName = "*.xls"
If .Execute(SortBy:=msoSortByFileName, SortOrder:=msoSortOrderAscending) > 0 Then
For x = 1 To .FoundFiles.Count
Workbooks.Open (.FoundFiles(x))



MsgBox "YOUR CODE GOES HERE"



ActiveWorkbook.Close True
Next x
End If
End With

.EnableEvents = True
.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub

 

[kenman]

opening files

Tom,

Thanks for the QUICK!! reply. Will give it a test this weekend.

Kenman

 

[Norie]

ActiveWorkbook.Name = "NewName"

Sure about that Tom?

I thought the only way to change the activeworkbook name would be with SaveAs.

Either that or close the workbook and use Name.