Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing rows from a excel spreadsheet using a vb script
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,Pat h,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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing rows from a excel spreadsheet using a vb script
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.Dele te mybook.Close savechanges:=True Next Fnum End If Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message ups.com... 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,Pat h,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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing rows from a excel spreadsheet using a vb script
You must create a backup first of the folder
-- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... 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.Dele te mybook.Close savechanges:=True Next Fnum End If Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message ups.com... 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,Pat h,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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing rows from a excel spreadsheet using a vb script
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing rows from a excel spreadsheet using a vb script
Send me your workbook with the code private and I look at it
-- Regards Ron de Bruin http://www.rondebruin.nl "RuaanD79" wrote in message ups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing Blank Rows in an excel spreadsheet | Excel Discussion (Misc queries) | |||
Removing a listbox from excel spreadsheet | Excel Worksheet Functions | |||
removing gridlines in Excel spreadsheet | New Users to Excel | |||
Removing rows via macro/VBS script | Excel Programming | |||
Removing Hard Borders from Excel Spreadsheet | Excel Worksheet Functions |