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

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Gram123

Thread Starter
Joined
Mar 15, 2001
Messages
1,829
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

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,639
Hi there,

Try this code:

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

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

Gram123

Thread Starter
Joined
Mar 15, 2001
Messages
1,829
Yeah man, always!

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

Gram123

Thread Starter
Joined
Mar 15, 2001
Messages
1,829
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

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,639
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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top