Solved: VBA - del rows where date in string doesn't match filename

[Gram123]

I currently have a PS1 script that takes yesterday's log files from a server, collates them into a single file and emails the file out in csv format. Out of necessity this always includes the first file from today's date, as that file contains the final activity from yesterday as well as the first activity from today.
Problem is, that means some lines of data will appear in today's file and tomorrow's file.


The filename of the daily csv file always contains yesterday's date in the format <7-char filename>dd-mm-yyyy.csv, e.g.:

ABCDEFGH19-03-2015.csv​

At the outset, one of these daily csv files will consist of tens of thousands of rows of data, with all data in column A. Each string begins with a date/time stamp, so the values in col A are in the format yyyymmdd_hhmmss<log file information> e.g.:

20150319_093015< >long string of log information​

I then run a macro on the csv file, beginning with inserting two new columns to split the data into 2 columns using the formula:

Range("A2").FormulaR1C1 = "=LEFT(RC[2],15)"
Range("B2").FormulaR1C1 = "=RIGHT(RC[1],LEN(RC[1])-15)"​

In other words, it places the date & time stamp in one col A, and all the rest of the guff in col B. It then goes off and does a whole bunch of other stuff.

What I need to do first, though (don't mind whether it's before or after the column has been split into two), is delete all rows where the date does not match the filename.
So, in VBA, here's what I need to do:

- Get the filename of the active file as a variable.
- Extract the date from the filename, i.e. chars 8 to 17 - e.g. 19-03-2015
- Convert the variable's format, so it matches the first 8 chars of the values in Column A, i.e. 20150319
- Delete all rows in the data that do not begin with this value.


I realise this could be done by the PS1 script before the daily email is issued, but evidence suggests this would slow down the script considerably, so I'd rather the processing was by the Excel macro on the user's PC, than by the script on the server.

Thanks in advance!

G123

 

[Keebellah]

Hi there,

Try this code:

Public Sub deleteOtherdayrows(fName As String)
Dim xrow    As Long
Dim lstRow  As Long
Dim fString As String
Dim fdate   As String
lstRow = Cells(Rows.Count, 1).End(xlUp).Row
fString = Mid(fName, 8, 10)
fdate = Right(fString, 4) & Mid(fString, 4, 2) & Left(fString, 2)
Debug.Print fString, fdate
For xrow = lstRow To 1 Step -1
    If Left(Cells(xrow, 1).Value, Len(fdate)) <> fdate Then Cells(xrow, 1).EntireRow.Delete
Next xrow
End Sub

The syntax would be pass the csv filename without the path as parameter

Sub testing()
Dim tstr As String
tstr = "ABCDEFG20-03-2015.csv"
deleteOtherdayrows fName:=tstr
End Sub

 

[Keebellah]

Hi Gram, busy?

 

[Gram123]

Yeah man, always!

Thank you very much for the code, Keebs, I'll give it a test today and get back to you!

 

[Gram123]

Hey Keebs,
I modified the code slightly to grab the Active Workbook name, and to switch screen updating off whilst it runs through the For Next Loop, and it works perfectly!

Dim xrow As Long
Dim lstRow As Long
Dim fString As String
Dim fdate As String
lstRow = Cells(Rows.Count, 1).End(xlUp).Row

fName = ActiveWorkbook.Name
fString = Mid(fName, 8, 10)
fdate = Right(fString, 4) & Mid(fString, 4, 2) & Left(fString, 2)
Debug.Print fString, fdate

Application.ScreenUpdating = False
For xrow = lstRow To 1 Step -1
If Left(Cells(xrow, 1).Value, Len(fdate)) <> fdate Then Cells(xrow, 1).EntireRow.Delete
Next xrow
Application.ScreenUpdating = True

Great stuff mate, many thanks!

Gram

 

[Keebellah]

Good to hear it works. Yes, I just placed the code and you put the finishing touch
Glad to have been able to help again.