Evoscan Logs - VBA to split it

Reply Subscribe

Thread Tools

Search this Thread

Nov 10, 2016, 01:15 PM

dr_latino999

Evolving Member

Thread Starter

iTrader: (12)

Join Date: Sep 2006

Location: Central TX

Posts: 419

Likes: 29

Received 17 Likes on 17 Posts

Evoscan Logs - VBA to split it

TL;DR - Easy way to split your Evoscan log file into 1 workbook with worksheets based on time elapsed.

I'm working with Aaron @ English for remote tuning, and one of the things he asked for was if I could split my logs into 1 minute increments to make it easier to filter through thousands of sample bites.

I thought about doing it manually, but I was killing 60-90 seconds to split each minute of log building new workbooks. I tried filtering the log, and you can select all times between X and Y - only works to 10K entries.

Solution - My good friend Microsoft Access and VBA. Output takes about 20 seconds, I've attached an example output file.

Import log to Access. In this case, I made sure it was named Evoscan.
Add this VBA module, name does not matter.
Where it says "Const xlsxPath", you can change that to wherever you would like your output file.
It's configured right now to push out 1x workbook with TimeMax sheets. Timemax is the total number of minutes of your log
Run the VBA module in Access and you'll have a log subdivided into minutes.

Code:

Option Compare Database
Option Explicit

Sub ExportToXlsx()
    Dim cdb As DAO.Database, qdf As DAO.QueryDef
    Set cdb = CurrentDb

    Const xlsxPath = "C:\Users\Edwin\Google Drive\Log2.xlsx"
   'Used to determine iterations and number of worksheets.
    Dim Times As Integer
    
    'TimesRange is the beginning of each increment, starting at 0s. Each successive iterations starts 60s later.
    Dim TimesRange As Integer
    
    'TimesRange1 is the end of each increment, starting at 60s. Each successive iteration ends 60s later.
    Dim TimesRange1 As Integer
    
    Dim TimesRange2 As Integer
    Dim TimesRange3 As Integer
    Dim TimeMax As Integer
    
    'Timemax gives us the last not whole minute
    TimeMax = (DMax("LogEntrySeconds", "Evoscan") \ 60) + 1
        
    TimesRange2 = 60
    TimesRange3 = 1
    
    For Times = 1 To TimeMax
        
        TimesRange = (Times - TimesRange3) * TimesRange2
        TimesRange1 = Times * TimesRange2
        
        'create .xlsx file if it doesn't already exist, and worksheets
        Set qdf = cdb.CreateQueryDef(Times, _
            "SELECT * FROM Evoscan WHERE LogEntrySeconds Between " & TimesRange & "  And " & TimesRange1 & ";")
        Set qdf = Nothing
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, Times, xlsxPath, True
        DoCmd.DeleteObject acQuery, Times
    Next Times
    
Set cdb = Nothing
End Sub

Attached Files