1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

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

Discussion in 'Business Applications' started by Gram123, Mar 20, 2015.

Thread Status:
Not open for further replies.
Advertisement
  1. Gram123

    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
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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
    
     
  3. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi Gram, busy?
     
  4. Gram123

    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!
     
  5. Gram123

    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!

    Great stuff mate, many thanks!

    Gram
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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.
     
  7. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1145152

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice