Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Bob Dobalina
 
Posts: n/a
Default Macro to delete first line of 200+ files

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
  #2   Report Post  
Ron de Bruin
 
Posts: n/a
Default

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


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Bob Dobalina" wrote in message ...
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



  #3   Report Post  
Bob Dobalina
 
Posts: n/a
Default

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)

"Ron de Bruin" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Bob Dobalina" wrote in message ...
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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro Line Insert Frantic Excel-er Excel Discussion (Misc queries) 4 March 21st 06 12:08 AM
Insert Line Macro Spyder Excel Discussion (Misc queries) 1 March 3rd 05 01:17 AM
Cannot delete a macro Jeff B Excel Discussion (Misc queries) 3 February 15th 05 01:43 AM
How do I record a macro which should work on multiple files ? Venkataraman.P.E Excel Discussion (Misc queries) 2 January 16th 05 11:26 AM
Macro - Skipping a line PaulPoll Excel Worksheet Functions 4 December 21st 04 01:43 AM


All times are GMT +1. The time now is 10:36 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"