Originally Posted by
XLGibbs
Well VB6, and VBA are so close, code is always useful as reference. Glad my mud against the wall stuck for you.
well... basic code for creating the XML file
Sub evtlogs()
cXML = "C:\temp\events.xml"
cSQL = "SELECT timegenerated, timewritten, computername, eventid, eventtypename, "
cSQL = cSQL & "eventcategory, eventcategoryname, sourcename, SID, Resolve_SID(SID), "
cSQL = cSQL & "message, strings, data INTO " & cXML & " FROM *.evt WHERE "
cSQL = cSQL & "eventtype IN (1;2) AND timegenerated > '2006-01-01 00:00:00' "
cSQL = cSQL & "ORDER BY timegenerated"
Set oLog = CreateObject("MSUtil.LogQuery")
oLog.maxParseErrors = 100
Set oInput = CreateObject("MSUtil.LogQuery.EventLogInputFormat")
Set oOut = CreateObject("MSUtil.LogQuery.XMLOutputFormat.1")
oLog.ExecuteBatch cSQL, oInput, oOut
If oLog.lastError <> 0 Then
For Each strMessage In oLog.errorMessages
'display error message
Next
End If
Set oOut = Nothing
Set oInput = Nothing
Set oLog = Nothing
End Sub
then you would use the Workbook .OpenXML(Filename:="cXML",LoadOption:=xlXmlLoadImportToList) then run your code afterwards. As I said, I wrote this in a scripting language that is external (and can be compiled as a standalone exe, or called as a UDF within an exe). It has a few nuances [like] code must make explicit references, functions use ( and ), positional parameters are coded differently. I took your pivot sub and massaged it to my scripting. After my code creates an Excel instance with oXL = CreateObject("Excel.Application") I do this....
oXL.WorkBooks.OpenXML(::Filename="%cXML%",LoadOption=xlXmlLoadImportToList)
oWS=oXL.ActiveSheet
oWS.Name="Data"
;remove 2 columns not really needed - they were added by LogParser
oWS.Columns("A:A").Select
oXL.Selection.Delete(::Shift=xlToLeft)
oXL.Selection.Delete(::Shift=xlToLeft)
oWS.Cells(1,1).Select
oRange=oWS.Cells(1, 1).CurrentRegion
cRange = StrCat(oWS.Name,"!",oRange.Address)
oXL.ActiveWorkBook.PivotCaches.Add(::SourceType=xlDatabase, SourceData=cRange).CreatePivotTable(::TableDestination="", TableName="Events")
oWS=oXL.ActiveSheet
oXL.ActiveWindow.DisplayGridlines = @False
oWS.Name="Pivot"
oWS.PivotTableWizard(::TableDestination=oXL.ActiveSheet.Cells(3, 1))
oWS.PivotTables("Events").AddFields(::RowFields="EventTypeName")
oWS.PivotTables("Events").PivotFields("EventTypeName").Orientation = xlDataField
ForEach s in oXL.ActiveWorkbook.Sheets
If !StrIndex("Data/Pivot",s.Name,0,@FWDSCAN) Then s.Delete
Next
oXL.ActiveWorkbook.Sheets("Pivot").Select
oXL.ActiveWorkbook.Sheets("Pivot").Move(::After=oXL.ActiveWorkbook.Sheets("Data"))
oXL.ActiveWorkbook.Sheets("Data").Activate()
oXL.ActiveWorkbook.SaveAs(cXLS)
oRange=0
oWS=0
;uncomment next line to close and exit excel
;oXL.Quit()
oXL=0
pretty similar... code is code... it is the results that matter. Stan