VBScript Write to Excel not writing

Question

What I'm trying to accomplish is searching multiple computers for event code 41 (unexpected shutdown) in the windows system log, then write that into an excel file for each instance for each computer.

I receive no errors, but nothing is ever written into the excel file. I set up an echo to make sure it was reaching the correct part of the loop (it does!) and I set a literal entry to see if there was an error with the variables (it didn't write). At this point, I'm at a loss.

' https://technet.microsoft.com/library/ee176684.aspx

' http://blogs.technet.com/b/heyscriptingguy/archive/2009/04/06/how-can-i-check-my-event-logs.aspx

' http://stackoverflow.com/questions/21738159/extracting-error-logs-from-windows-event-viewer

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("H:\Chris Created Stuffs\Windows Stuffs\check_error_41.xlsx")
objExcel.Visible = False

i = 1
x = 0
'On error resume next
'This is the code that will read the computer names off of the 
'appropriate spreadhseet

Do Until objExcel.Cells(i, 1).Value = ""
    ReDim Preserve strPC(x)
    strPC(x) = objExcel.Cells(i, 1).Value
    i = i + 1
    x = x + 1
Loop

'And this is the code that will write the success or failure
'data in the Excel spreadsheet

Set objSheet1 = objWorkbook.sheets("Missed")
Set objSheet2 = objWorkbook.sheets("Sheet1")

'Set objSheet1 = objExcel.ActiveWorkbook.Worksheets(1)
'Set objSheet2 = objExcel.ActiveWorkbook.Worksheets(2)

f = 1
m = 1


'Set obj = CreateObject("Scripting.FileSystemObject")
For Each strPC In strPC

Set objWMIService = GetObject("winmgmts:\\" & strPC & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_NTLogEvent WHERE LogFile='System'")

    If Err.Number <> 0 Then
        'objSheet1.Add
        objSheet1.Cells(f, 1).Value = strPC
        objSheet1.Cells(f, 2).Value = err.number
        f = f + 1
        Err.clear
    Else
        For Each objEvent in colItems
            If objEvent.EventCode = 41 Then
                'writeLog "Event Code: " & objEvent.EventCode
                'writeLog "Event Identifier: " & objEvent.EventIdentifier
                'writeLog "Logfile: " & objEvent.Logfile
                'writeLog "Message: " & objEvent.Message
                'writeLog "Record Number: " & objEvent.RecordNumber
                'writeLog "Source Name: " & objEvent.SourceName
                'writeLog "Time Generated: " & objEvent.TimeGenerated
                'writeLog "Time Written: " & objEvent.TimeWritten
                'objSheet2.Add
                objSheet2.Cells(m,1).Value = strPC
                objSheet2.Cells(m,2).Value = objEvent.EventCode
                objSheet2.Cells(m,3).Value = objEvent.EventIdentifier
                objSheet2.Cells(m,4).Value = objEvent.Logfile
                objSheet2.Cells(m,5).Value = objEvent.Message
                objSheet2.Cells(m,6).Value = objEvent.RecordNumber
                objSheet2.Cells(m,7).Value = objEvent.SourceName
                objSheet2.Cells(m,8).Value = objEvent.TimeGenerated
                objSheet2.Cells(m,9).Value = objEvent.TimeWritten
                objSheet2.Cells(m,10).Value = "Listen!"
                m = m + 1
                wscript.echo "We Got One!!!!" 
            Else
                m = m + 1
            End If
        Next
    Err.clear
    End If
Next


objExcel.ActiveWorkbook.Save 
objExcel.Quit
wscript.echo "Done"

Are you sure that `objEvent.EventCode` is numeric? `41 <> "41"` — , Feb 13 '16 at 00:09
Have you tried setting the objExcel.Visible to True and stepping through the code so you can see which workbook is the active one that you are saving? Since you know you want to save objSheet2 maybe you should just call that worksheet instead of the active worksheet. — nfloria, Feb 13 '16 at 00:16

score 1 · Accepted Answer · answered Feb 13 '16 at 07:53

I think your primary problem was ignoring the Workbook Object and Worksheet Object. In this code:

Do Until objExcel.Cells(i, 1).Value = ""
    ReDim Preserve strPC(x)
    strPC(x) = objExcel.Cells(i, 1).Value
    i = i + 1
    x = x + 1
Loop

Nothing is actually being pulled from the worksheet. I've had to guess a little as to the actual origin but the syntax is correct; you may have to make specific adjustments to your own worksheet layout.

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True 'False
Set objWorkbook = objExcel.Workbooks.Open("H:\Chris Created Stuffs\Windows Stuffs\check_error_41.xlsx")

i = 1
x = 0
'On error resume next
'This is the code that will read the computer names off of the appropriate spreadhseet

Do Until objWorkbook.Worksheets(1).Cells(i, 1).Value = ""
    ReDim Preserve strPCs(x)
    strPCs(x) = objWorkbook.Worksheets(1).Cells(i, 1).Value
    'msgbox objWorkbook.Worksheets(1).Cells(i, 1).Value
    i = i + 1
    x = x + 1
Loop

'And this is the code that will write the success or failure data in the Excel spreadsheet

Set objSheet1 = objWorkbook.Worksheets("Missed")
Set objSheet2 = objWorkbook.Worksheets("Sheet1")
f = 1
m = 1

For Each strPC In strPCs

    Set objWMIService = GetObject("winmgmts:\\" & strPC & "\root\cimv2")
    Set colItems = objWMIService.ExecQuery("Select * from Win32_NTLogEvent WHERE LogFile='System'")

    If Err.Number <> 0 Then
        'objSheet1.Add
        objSheet1.Cells(f, 1).Value = strPC
        objSheet1.Cells(f, 2).Value = err.number
        f = f + 1
        Err.clear
    Else
        For Each objEvent in colItems
            If objEvent.EventCode = 41 Then
                'writeLog "Event Code: " & objEvent.EventCode
                'writeLog "Event Identifier: " & objEvent.EventIdentifier
                'writeLog "Logfile: " & objEvent.Logfile
                'writeLog "Message: " & objEvent.Message
                'writeLog "Record Number: " & objEvent.RecordNumber
                'writeLog "Source Name: " & objEvent.SourceName
                'writeLog "Time Generated: " & objEvent.TimeGenerated
                'writeLog "Time Written: " & objEvent.TimeWritten
                'objSheet2.Add
                objSheet2.Cells(m, 1).Value = strPC
                objSheet2.Cells(m, 2).Value = objEvent.EventCode
                objSheet2.Cells(m, 3).Value = objEvent.EventIdentifier
                objSheet2.Cells(m, 4).Value = objEvent.Logfile
                objSheet2.Cells(m, 5).Value = objEvent.Message
                objSheet2.Cells(m, 6).Value = objEvent.RecordNumber
                objSheet2.Cells(m, 7).Value = objEvent.SourceName
                objSheet2.Cells(m, 8).Value = objEvent.TimeGenerated
                objSheet2.Cells(m, 9).Value = objEvent.TimeWritten
                objSheet2.Cells(m, 10).Value = "Listen!"
                m = m + 1
                'wscript.echo "We Got One!!!!" 
            'do not add to m on no-write; it only creates blank rows
            End If
        Next
        Err.clear
    End If
Next


'objWorkbook.Close True
'objExcel.Quit
wscript.echo "Done"

I've commented out the code lines to make the Excel application object hidden as to save asn close it in order that you can observe the process. Uncomment them once you are happy with the process.

After commenting out the M+1 on no-write, issue is resolved. — Finch, Feb 15 '16 at 16:46

VBScript Write to Excel not writing

1 Answers1