Solved: delete rows without open files

[reza_doang]

hi all,
hope someone can provide vba/macro for me.
here the situation:
i have more than 2000 excel files with same structure/format, every file only have 1 worksheet.
now i want to delete rows 1-4 in every files without open the files.
i'm using excel 2010.

thanks

reza

[Bob Phillips]

Even if you could do it, maybe using ADO< I don't think it is worth the effort. Just loop, open them, delete the rows, save and close.

[Sean.DiSanti]

Might be faster to do this one with AutoIT autoitscript.com/autoit3 through COM so you don't have to keep a window up or anything.... the code to do the whole thing in AutoIT would be:
[VBA]$oEX = ObjCreate("excel.Application")
$search = FileFindFirstFile("\\server\folder\blah\*.xls")
If $search = -1 Then
MsgBox(0, "Error", "No files/directories matched the search pattern")
Exit
EndIf

While 1
$file = FileFindNextFile($search)
If @error Then ExitLoop
$oWB = $oEX.Workbooks.Open($file)
$oWB.Activesheet.Rows("1:4").Delete
$oWB.Close(1)
WEnd
FileClose($search)
$oEX.Quit
MsgBox(0,"All done","All done")
[/VBA]

[Bob Phillips]

How is that any better, you still open all the files. And shouldn't you save it?

[Sean.DiSanti]

the .Close(1) saves on exit, and by doing it through COM with autoit, the application object is created without opening the window etc. So it just happens in the background and you're free to work on other things while it's going.

[omnibuster]

Try this
[VBA]Sub AllFilesInSameFoler()
Dim SubFolderName As String, FilePath, fso As Object, fld As Object, fil As Object
Dim fName As String, WB As Workbook
Set fso = CreateObject("Scripting.FileSystemObject")
SubFolderName = ThisWorkbook.Path
FilePath = ActiveWorkbook.Path
fName = ActiveWorkbook.Name
Set fld = fso.GetFolder(SubFolderName)
For Each fil In fld.Files
If Right$(FilePath, 1) <> "\" Then FilePath = FilePath & "\"
If Right(fil.Name, 5) = ".xlsx" Then
If fil.Name <> fName Then

Set WB = Workbooks.Open(FilePath & fil.Name, UpdateLinks:=0)
ActiveWorkbook.Sheets(1).Rows("1:4").Delete Shift:=xlUp

Application.DisplayAlerts = False
ActiveWorkbook.Save
ActiveWorkbook.Close
End If
End If
Next fil
End Sub
[/VBA]