Call NTreport from Excel 2000, use Excel macro code to create an HTML report

How it works

The Excel macro calls the NTreport script, NTreport writes data to the clipboard, Excel imports data from the clipboard, formats cells and saves the output as HTML.

Version compatibility

This example was written for Excel 2000 but it works on all Excel versions with minor or no modifications. Notice that the macro code syntax can differ between different language versions of Excel.

HTML Output example



NTreport script

·	Export all events with ID 529 from the security log to the clipboard.

·	File name: "NTr_ex.cnu"

[Settings_Begin]
FileType=NTreport
NTinfo.Name.All.Rows=Yes
NTinfo.Show.Scan=Yes
NTinfo.Event.Log=Yes
NTinfo.Event.Log.Log=Security
NTinfo.Event.Log.ID=529
NTinfo.Event.Log.InclBr=Yes
NTreport.Delimiter=%TAB%
[Settings_End]

[Batch_Begin]
Clear.Window Main
Get.Data \\EDU12
If Main
Write.Clipboard Main ; %col1%%TAB%%col3%%TAB%%col12%%TAB%%col14%%TAB%%col16%%TAB%%col24%
Endif
[Batch_End]

Excel Macro

·	Sub Demo() is the main macro

·	HardShell is a Shell command replacement that makes Excel wait until the called process finishes.

Private Const INFINITE = &HFFFFFFFF 'Allows an Infinite timeout
Private Const SYNCHRONIZE = &H100000 'Enables using the process handle in any of the wait functions to wait for the process to terminate.

'The CloseHandle function closes an open object handle.
Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long

'The OpenProcess function returns a handle of an existing process object.
Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long

'The IsWindow function determines whether the specified window handle identifies    an existing window.
Private Declare Function IsWindow& Lib "User32" (ByVal hwnd As    Long)

'The WaitForSingleObject function returns when the specified object is in the    signaled state or when the time-out interval elapses.

Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long
   
Private Sub PauseUntilTerminate(ProcessId As Long)
 Dim phnd&
 ' Get Handle to see if process is running
 phnd = OpenProcess(SYNCHRONIZE, 0, ProcessId)
 If phnd <> 0 Then
  Application.StatusBar = "Waiting for termination"
  ' Wait until finished
  Call WaitForSingleObject(phnd, INFINITE)
  ' Close handle
   Call CloseHandle(phnd)
 End If
 Application.StatusBar = False
End Sub

'HardShell: Thanks to Mr. Andrew Baker 
Sub HardShell(FilePathName As String)
 Dim ProcessId As Long
 ProcessId = Shell(FilePathName, vbMaximizedFocus)
 'The shell function returns a Variant (Double) representing the program's task ID if successful
 Call PauseUntilTerminate(ProcessId)
End Sub

Sub Demo()
 'Clear Worksheet
 Cells.Select
 Selection.Clear
 'Column Headings
 Range("A1").Select
 ActiveCell.FormulaR1C1 = "Server"
 Range("B1").Select
 ActiveCell.FormulaR1C1 = "Date / Time"
 Range("C1").Select
 ActiveCell.FormulaR1C1 = "Description"
 Range("D1").Select
 ActiveCell.FormulaR1C1 = "User"
 Range("E1").Select
 ActiveCell.FormulaR1C1 = "Domain"
 Range("F1").Select
 ActiveCell.FormulaR1C1 = "Workstation"
 Rows("1:1").Select
 Selection.Font.Bold = True
 'Run NTreport, wait until finsihed
 ChDir "c:\ntreport"
 HardShell "admwin.exe ntr_ex.cnu /ntreport"
 'Paste from clipboard
 Range("A2").Select
 ActiveSheet.Paste
 'Select date format
 Columns("B:B").Select
 Selection.NumberFormat = "m/d/yy h:mm"
 'Set column size
 Cells.Select
 Selection.Columns.AutoFit
 'Block the SaveAs Alert
 Application.DisplayAlerts = False
 'Save as HTML
 ActiveWorkbook.SaveAs Filename:="C:\ntreport\Result.htm", FileFormat:=    _
 xlHtml, ReadOnlyRecommended:=False, CreateBackup:=False
End Sub