Apply Conditional Formatting To .csv

[Coniferous]

I'm not sure how simple a request this is going to be, but i'm going to give it a shot.

Right now i've been doing some analysis of the security log in my events viewer. I made up a simple batch file that runs eventquery then opens up the resulting csv in excel. this code is here:

echo off
cls
cscript //h:cscript //s //nologo
cls
echo This Script will list all the user logins on a computer you specify. Please note that by querying a computer, your user name will show up as a successful login on the event log of the remote computer.
echo. 
set /p compname=Serial Number:
eventquery /s %compname% /fi "type eq successaudit" /fi "ID eq 528 OR ID eq 551" /fi "category eq Logon/Logoff"  /fi "user ne NT AUTHORITY\NETWORK SERVICE" /fi "user ne NT AUTHORITY\SYSTEM" /fi "user ne NT AUTHORITY\LOCAL SERVICE" /fi "user ne NT AUTHORITY\ANONYMOUS LOGON" /v /fo csv /l security > %WINDIR%\temp\%compname%.csv
start %WINDIR%\temp\%compname%.csv

This produces an unwieldy huge excel spreadsheet thats tough to read. What I would /Like/ my script to do is:
1) Call event query and save the CSV
2) Apply conditional formatting rules to a column. (Eg, 551 pops up in red, 528 pops up in green)
3) Open it in excel.

I'm pretty sure this requires a jump to VB. I don't know a lot about vb, i've done some research on the ApExcel libiary and i'm not coming up with any results to applying conditional formatting. I understand that CSV does not contain any formatting as well, so i'm not sure if a conversion of the format needs to be dont first.

Thank you in advance for your help :3

[Target]

this is a job for VBA - CSV is plain text so there's no opportunity to store formatting or other niceties

The VBA macro would do the import and add the necessary trimmings (actually, you could possibly set this up as a template)

[Coniferous]

Well, i have dome some research on it. I understand that csv has no formatting, just values. However, if i open up the csv in excel, then get the vba script to do the rest then (i assume) it should work. I found a couple snippets of code that are close to what i need. Such as this...

Set ApExcel = CreateObject("Excel.application") 'Creates an object
ApExcel.Visible = True ' So you can see Excel
ApExcel.Workbooks.Add 'Adds a new book.
ApExcel.cells(1, 1).Formula = "HELLO"    'Add Text to a Cell
'You can use the line above, changing coordenates to go to any
'cell and you can also add Formulas
ApExcel.Range("A1:Z1").BORDERS.Color = RGB(0, 0, 0) 'Use it to
'change the borders.
ApExcel.Columns("A:AY").EntireColumn.AutoFit 'To adjust the
'column's width.
ApExcel.Range("A:Z").Select 'To establish a selection
ApExcel.Selection.NumberFormat = "0" 'Adding different formats

This one right here makes a new sheet and fills some of the cells with content. If i could come up with some way to get it to open the csv then apply formatting to a certain column... then i would be gold. I'm trying real hard to find some sort of documentation on ApExcel and I'm not finding anything.

[AndyM]

ApExcel is just the name you assigned to the application object so you can use it in subsequent lines.  There was probably a "dim ApExcel As Object" that preceded the code you found.  This defined ApExcel as the name of an object which is then set to represent the Excel application itself.

The approach I use for the sort of project you are doing, is to go through all the steps manually so I know exactly what I want the code to do, and in what order.

Then I start filling in the steps with code.

It's very helpful to record the manual steps and then view what you've done in the VBA editor.  However much needs to be changed from there, particularly anything to do with variables.

Record the importation of a .csv file, and continue to record saving it as an .xls file (which you'll need if you want to keep your formatting).  Then look at the code.

Excel's VBA help has a lot, but I find it difficult to find things, and the explanations require a working background that I found difficult to get in one comprehensive overview.

Google an Excel vba primer, look for one that has examples like the sort of thing you are looking to do.  There are Microsoft Excel MVP websites that are invaluable (Pearson, DeBruin, McGimpsey, etc)  Check out the board at vbaexpress.com and the microsoft.public.excel.programming newsgroup

When everything is automated, use an Auto_Open or Workbook_Open macro which will run as soon as your workbook is opened by the script that created the .csv file in step 1.

Good luck.

[r0bertdenir0]

Just thinking off the top of my head here...

Yr script will always generate a CSV with the same filename?
If you can fix the path of the CSV, then you can have an actual Excel spreadsheet which then links 2 the CSV, using it as a datasource.
Just go Data->Import in Excel the first time.
Then every other time after exporting yr eventlog, just open yr spreadsheet & do a refresh 2 update yr data.

Excel helps you out cos whenever you refresh yr data, it creates a named range (after the original file) which is exactly the size the data occupies.
It's simple 2 wrote a macro 2 operate on that named range & do yr conditional formatting.
You can also do it automatically by having the macro run everytie the data is refreshed.
But if you don't know VB start with the macro & take it from there

Just another method that might do wat you want...