Macro to delete first line of 200+ files

B

Bob Dobalina

Ok... I know this is an easy one for this group and I would try and figure it
out on my own but I'm crunched for time.

I need a quick macro that will take all the files in my directory and delete
the first line only. Every file has a dummy line at the top (as they were
converted from XML and retained the /Data string). I need to strip them out
so I can use another macro that a very awesome Bernie helped me create over
the last couple of weeks.

Any thoughts? I'm sure this is a no-brainer.

thanks,
- S
 
R

Ron de Bruin

Hi Bob

Try this example that delete row 1 of the first sheet of the workbooks in the folder C:\Data

Sub test()
Dim mybook As Workbook
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir

MyPath = "C:\Data"
'Add a slash at the end if the user forget
If Right(MyPath, 1) <> "\" Then
MyPath = MyPath & "\"
End If

ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

On Error GoTo CleanUp
Application.ScreenUpdating = False

Do While FNames <> ""
Set mybook = Workbooks.Open(FNames)
mybook.Worksheets(1).Range("A1").EntireRow.Delete
mybook.Close True
FNames = Dir()
Loop

CleanUp:
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub
 
B

Bob Dobalina

Yeppers... that did the trick.

I knew I could count on you guys. Thanks again!!!!! You all should get the
Interstellar Quarterly Karma Award.

cheers,
- S (B)
 
Top