excel - VBScript写入Excel不写入

Question

我想要完成的是在 Windows 系统日志中搜索多台计算机以查找事件代码 41(意外关机)，然后将其写入每台计算机的每个实例的 excel 文件中。

我没有收到任何错误，但没有任何内容写入 excel 文件。我设置了一个回显以确保它到达循环的正确部分(确实如此!)，我设置了一个文字条目以查看变量是否存在错误(它没有写入)。在这一点上，我不知所措。

' 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"

Answer 1

我认为您的主要问题是忽略了 Workbook Object和 Worksheet Object .在这段代码中:

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

实际上没有从工作表中提取任何内容。我不得不猜测一下实际的起源，但语法是正确的；您可能必须对自己的工作表布局进行特定调整。

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"

我已经注释掉了代码行，以隐藏 Excel 应用程序对象以保存并关闭它，以便您可以观察该过程。一旦您对过程感到满意，请取消注释它们。