0

I have a log file in CSV format that I need to truncate periodically. What I am looking for is a way to search for the last entry in the file, get the row number for the record, then keep only the previous 100 rows, and delete the remaining rows. I need this to run on a once a week basis. something like this
search column A for Null
find row number for that Null entry
create an array for that row number -101 rows to create an array of the last 100 rows of data
delete all rows outside of that array - leaving only the last 100 rows.

I have changed my approach and instead am using VBS (since a macro can't be saved in a CSV file). I am asking the code to check cell A450 for any content. If there is something there, then rows 1-250 should be deleted. 

Option Explicit
Dim Val
Dim ExcelApp
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Visible = True
ExcelApp.Workbooks.Open("c:\CodeTest\P16UPSHEAD_In.csv")
Val = ExcelApp.ActiveSheet.Range("A450")
If Val <> "" Then
ExcelApp.ActiveSheet.Range("A1:A250").EntireRow.Delete
End If
ExcelApp.Workbooks.Save("c:\CodeTest\P16UPSHEAD_In.csv")

The problem is that the file is opening as Read-Only - so the Save command is not working. What am I doing wrong?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Dave
  • 1
  • 1
  • 2
    Welcome to SO. Are you hoping for a VBA solution? If so, please tag as such and show us the code you've tried. If not, it should be posted on SuperUser. – Doug Glancy Aug 14 '13 at 14:43
  • also, does it have to be written in excel? use of [Unix type commands or powershell](http://stackoverflow.com/questions/187587/looking-for-a-windows-equivalent-of-the-unix-tail-command) may give you what you are looking fore – SeanC Aug 14 '13 at 15:04
  • I have changed my approach and have some working code - I have a vbs script to open the CSV file, check to see if there is anything in cell A450 and if so, delete rows 1-250. This works except the CSV file opens as Read-Only so I cannot save the changes. Option Explicit Dim Val Dim ExcelApp Set ExcelApp = CreateObject("Excel.Application") ExcelApp.Visible = True ExcelApp.Workbooks.Open("c:\CodeTest\P16UPSHEAD_In.csv") Val = ExcelApp.ActiveSheet.Range("A450") If Val <> "" Then ExcelApp.ActiveSheet.Range("A1:A250").EntireRow.Delete End If ExcelApp.Workbooks.Save("c:\CodeTest\P16UPSHEAD_In.csv") – Dave Aug 15 '13 at 19:44

0 Answers0