Removing rows from a excel spreadsheet using a vb script

[ruaand]

Hallo,

Can anyone help?

I have a folder containing multiple csv files. What I want to do is
write a vb script that will make a copy of the csv files (for backup
purposes), then open the csv files one by one and remove the first 4
rows.

The first 4 rows contains the following,

Daily Backup Status Report
02/22/2006 03:15:53
Summary of backup jobs done in the last 24 hours. This may exclude some
backup clients which could not be connected.
Job.Description,Job
No,JobType,JobID,JobStatus,JobStart,SourceHost,Path,Status,Name

Does anyone have a script that will do something like this? I'm very
new to vb scripting and have no clue were to begin.

Any help will be appreciated.

Cheers

Ruaan

 

[Ron de Bruin]

Hi Ruaan

Try this example for the folder C:\Data

Change the folder name in the code

Sub Example()
Dim MyPath As String
Dim FilesInPath As String
Dim MyFiles() As String
Dim Fnum As Long
Dim mybook As Workbook

'Fill in the path\folder where the files are
MyPath = "C:\Data"

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

'If there are no Excel files in the folder exit the sub
FilesInPath = Dir(MyPath & "*.csv")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If


'Fill the array(myFiles)with the list of Excel files in the folder
Fnum = 0
Do While FilesInPath <> ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop

Application.ScreenUpdating = False
'Loop through all files in the array(myFiles)
If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
mybook.Worksheets(1).Range("A1:A4").EntireRow.Delete
mybook.Close savechanges:=True
Next Fnum
End If
Application.ScreenUpdating = True
End Sub

 

[Ron de Bruin]

You must create a backup first of the folder

 

[RuaanD79]

Hi There,

I'm getting a error message when I run the script,

Line: 2
Char: 16
Error: Expected end of statement
Code: 800A0401

Do you know what is wrong?

Thanks

Ruaan

 

[Ron de Bruin]

Send me your workbook with the code private and I look at it