Hi all,

I have a script to look up specific event ids and export data to a csv
file....which works alright. I'm attempting to instead dump it out to
an excel file - xls for better formatting. Right now the script is a
mess because i was trying to comment out what i had and add new for
the xls dump. Any help would be greatly appreciated!! Thanks in
advance!



*********************************************************************************************
Option Explicit

Dim objExcel, objFso, objCommand, objFolder, objWMI, objItem,
objShell, strEventLog
Dim text1, strFile, strQuery, strComputer, strFolder, strFileName,
strPath, strTimeWritten, dtmTimeWritten, dtmDate, dtmTime
Dim intEvent, intNumberID, intRecordNum, colLoggedEvents

' --------------------------------------------------------
' Set the folder and file name
' Set numbers
intNumberID = 27 ' Event ID Number
intRecordNum = 0

Set objExcel = CreateObject("Excel.Application")
' Create a new workbook.
objExcel.Visible = True
objExcel.Workbooks.Add

strComputer = "."
strFileName = "\Event" & intNumberID & ".xls"
strFolder = "C:\documents and settings\mattij01\desktop"
strPath = strFolder & strFileName
strEventLog = "'application' "

' -----------------------------------------------------
' Section to create folder and hold file.
Set objFso = CreateObject("Scripting.FileSystemObject")
If objFSO.FolderExists(strFolder) Then
Set objFolder = objFSO.GetFolder(strFolder)
Else
Set objFolder = objFSO.CreateFolder(strFolder)
Wscript.Echo "Folder created " & strFolder
End If

'Wscript.Echo " Press OK and Wait 30 seconds (ish)"
Set strFile = objFso.CreateTextFile(strPath, True)
Set objWMI = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" _
& strComputer & "\root\cimv2")
Set colLoggedEvents = objWMI.ExecQuery _
("Select * from Win32_NTLogEvent Where Logfile = " & strEventLog)

' -----------------------------------------
' Next section loops through ID properties

strFile.WriteLine("Date: " & ", Time: " & ", ComputerName: " & ",
EventCode: " & ", Message: ")
For Each objItem in colLoggedEvents

strTimeWritten = objItem.TimeWritten

dtmTimeWritten = CDate(Mid(strTimeWritten, 5, 2) & "/" & _
Mid(strTimeWritten, 7, 2) & "/" & Left(strTimeWritten, 4) _
& " " & Mid (strTimeWritten, 9, 2) & ":" & _
Mid(strTimeWritten, 11, 2) & ":" & Mid(strTimeWritten,
13, 2))
dtmDate = FormatDateTime(dtmTimeWritten, vbShortDate)
dtmTime = FormatDateTime(dtmTimeWritten, vbLongTime)

'If objItem.EventCode = intNumberID Then
text1 = "objItem WHERE objItem.EventCode = intNumberID"
'Setup Excel Spreadsheet w/ data
strQuery = "SELECT dtmDate, dtmTime FROM" & text1
objCommand.CommandText = strQuery
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst

Do Until objRecordSet.EOF
objExcel.Cells(x, 1).Value = _
objRecordSet.Fields("dtmDate").Value
objExcel.Cells(x, 2).Value = _
objRecordSet.Fields("dtmTime").Value
x = x + 1
objRecordSet.MoveNext
Loop

'strFile.WriteLine(dtmDate & "," & dtmTime & "," &
objItem.ComputerName & "," & objItem.EventCode & "," &
objItem.Message)

intRecordNum = intRecordNum + 1
If intRecordNum = 50 Then
exit for
End If
'End If
Next

' Save the spreadsheet and close the workbook.
objExcel.ActiveWorkbook.SaveAs strExcelPath
objExcel.ActiveWorkbook.Close

' Quit Excel.
objExcel.Application.Quit

WScript.quit
**********************************************************************************************

"tiv" <jmattivi@xxxxxx> wrote in message
news:3e6fc20a-ff00-4711-a43b-f5166b1a2d36@xxxxxx

> Hi all,
>
> I have a script to look up specific event ids and export data to a csv
> file....which works alright. I'm attempting to instead dump it out to
> an excel file - xls for better formatting. Right now the script is a
> mess because i was trying to comment out what i had and add new for
> the xls dump. Any help would be greatly appreciated!! Thanks in
> advance!

You're already writing some data into Excel cells. Do you have any specific
questions?

Sorry if the first post was vague. Basically, it's not working trying
to populate the data into excel.

It's bombing out at this line - strQuery = "SELECT dtmDate, dtmTime
FROM" & text1 when trying to select the objects to populate into
excel. I believe it's my for statement and how i'm trying to grab the
variables from it to go to excel, but not positive on how to pipe them
in....

On Sep 25, 5:58 pm, "Pegasus \(MVP\)" <I....@xxxxxx> wrote:

> "tiv" <jmatt...@xxxxxx> wrote in message
>
> news:3e6fc20a-ff00-4711-a43b-f5166b1a2d36@xxxxxx
>

> > Hi all,

>

> > I have a script to look up specific event ids and export data to a csv
> > file....which works alright. I'm attempting to instead dump it out to
> > an excel file - xls for better formatting. Right now the script is a
> > mess because i was trying to comment out what i had and add new for
> > the xls dump. Any help would be greatly appreciated!! Thanks in
> > advance!

>
> You're already writing some data into Excel cells. Do you have any specific
> questions?

"tiv" <jmattivi@xxxxxx> wrote in message
news:1922cdcc-2fce-4e08-8d34-c17d2ef10a4c@xxxxxx

> Sorry if the first post was vague. Basically, it's not working trying
> to populate the data into excel.
>
> It's bombing out at this line - strQuery = "SELECT dtmDate, dtmTime
> FROM" & text1 when trying to select the objects to populate into
> excel. I believe it's my for statement and how i'm trying to grab the
> variables from it to go to excel, but not positive on how to pipe them
> in....

Your problem has nothing to do with "piping values into Excel" and
it's not failing on the line you quote but on the subsequent line, namely:
objCommand.CommandText = strQuery
and for a simple reason: You never defined an object "objCommand".

Some other observations:
- Since you use "IntNumber" always as a string, you should change
IntNumber = 27 to
StrNumber = "27"
- Having a statement such as
Set strFile = objFso.CreateTextFile(strPath, True)
is highly confusing. "strFile" is an object, not a string.
- I grabbed some code from my box of sample scripts and merged
it with your own code. It shows you how you can extract events
from the Event Logger and write them into spreadsheet cells.

Option Explicit

Dim objExcel, objFso, objCommand, objFolder, objWMI, objItem
Dim strEventLog, oEvent, x
Dim text1, strFile, strQuery, strComputer, strFolder, strFileName
Dim strPath, strTimeWritten, dtmTimeWritten, dtmDate, dtmTime
Dim intEvent, strEventID, intRecordNum, colLoggedEvents

' --------------------------------------------------------
' Set the folder and file name
' Set numbers
strEventID = "27" ' Event ID Number
intRecordNum = 0

Set objExcel = CreateObject("Excel.Application")
' Create a new workbook.
objExcel.Visible = True
objExcel.Workbooks.Add

strComputer = "."
strFileName = "\Event" & strNumberID & ".xls"
strFolder = "C:\"
strPath = strFolder & strFileName
strEventLog = "'application' "

' -----------------------------------------------------
' Section to create folder and hold file.
Set objFso = CreateObject("Scripting.FileSystemObject")
If objFso.FolderExists(strFolder) Then
Set objFolder = objFso.GetFolder(strFolder)
Else
Set objFolder = objFso.CreateFolder(strFolder)
WScript.Echo "Folder created " & strFolder
End If

'Wscript.Echo " Press OK and Wait 30 seconds (ish)"
Set strFile = objFso.CreateTextFile(strPath, True)
Set objWMI = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" _
& strComputer & "\root\cimv2")
' -----------------------------------------
' Next section loops through ID properties

strFile.WriteLine("Date: " & ", Time: " & ", ComputerName: " _
& " EventCode: " & ", Message: ")

For Each objItem In colLoggedEvents
strTimeWritten = objItem.TimeWritten
dtmTimeWritten = CDate(Mid(strTimeWritten, 5, 2) & "/" & _
Mid(strTimeWritten, 7, 2) & "/" & Left(strTimeWritten, 4) _
& " " & Mid (strTimeWritten, 9, 2) & ":" & _
Mid(strTimeWritten, 11, 2) & ":" & Mid(strTimeWritten, 13, 2))
dtmDate = FormatDateTime(dtmTimeWritten, vbShortDate)
dtmTime = FormatDateTime(dtmTimeWritten, vbLongTime)

Set colLoggedEvents = objWMI.ExecQuery _
("Select * from Win32_NTLogEvent " _
& "Where Logfile = " & strEventLog _
& " and TimeWritten >= 20080901" _
& " and EventCode = " & strEventID)

x = 1
For Each oEvent In colLoggedEvents
objExcel.Cells(x, 1).Value = oEvent.message
objExcel.Cells(x, 2).Value = oEvent.TimeGenerated
x = x + 1
Next
Next

' Save the spreadsheet and close the workbook.
objExcel.ActiveWorkbook.SaveAs strExcelPath
objExcel.ActiveWorkbook.Close

' Quit Excel.
objExcel.Application.Quit

WScript.quit

Had to tweak a couple things....but i got it working now. Thanks a
lot!!!!

On Sep 26, 11:18 am, "Pegasus \(MVP\)" <I....@xxxxxx> wrote:

> "tiv" <jmatt...@xxxxxx> wrote in message
>
> news:1922cdcc-2fce-4e08-8d34-c17d2ef10a4c@xxxxxx
>

> > Sorry if the first post was vague. Basically, it's not working trying
> > to populate the data into excel.

>

> > It's bombing out at this line - strQuery = "SELECT dtmDate, dtmTime
> > FROM" & text1 when trying to select the objects to populate into
> > excel. I believe it's my for statement and how i'm trying to grab the
> > variables from it to go to excel, but not positive on how to pipe them
> > in....

>
> Your problem has nothing to do with "piping values into Excel" and
> it's not failing on the line you quote but on the subsequent line, namely:
> objCommand.CommandText = strQuery
> and for a simple reason: You never defined an object "objCommand".
>
> Some other observations:
> - Since you use "IntNumber" always as a string, you should change
> IntNumber = 27 to
> StrNumber = "27"
> - Having a statement such as
> Set strFile = objFso.CreateTextFile(strPath, True)
> is highly confusing. "strFile" is an object, not a string.
> - I grabbed some code from my box of sample scripts and merged
> it with your own code. It shows you how you can extract events
> from the Event Logger and write them into spreadsheet cells.
>
> Option Explicit
>
> Dim objExcel, objFso, objCommand, objFolder, objWMI, objItem
> Dim strEventLog, oEvent, x
> Dim text1, strFile, strQuery, strComputer, strFolder, strFileName
> Dim strPath, strTimeWritten, dtmTimeWritten, dtmDate, dtmTime
> Dim intEvent, strEventID, intRecordNum, colLoggedEvents
>
> ' --------------------------------------------------------
> ' Set the folder and file name
> ' Set numbers
> strEventID = "27" ' Event ID Number
> intRecordNum = 0
>
> Set objExcel = CreateObject("Excel.Application")
> ' Create a new workbook.
> objExcel.Visible = True
> objExcel.Workbooks.Add
>
> strComputer = "."
> strFileName = "\Event" & strNumberID & ".xls"
> strFolder = "C:\"
> strPath = strFolder & strFileName
> strEventLog = "'application' "
>
> ' -----------------------------------------------------
> ' Section to create folder and hold file.
> Set objFso = CreateObject("Scripting.FileSystemObject")
> If objFso.FolderExists(strFolder) Then
> Set objFolder = objFso.GetFolder(strFolder)
> Else
> Set objFolder = objFso.CreateFolder(strFolder)
> WScript.Echo "Folder created " & strFolder
> End If
>
> 'Wscript.Echo " Press OK and Wait 30 seconds (ish)"
> Set strFile = objFso.CreateTextFile(strPath, True)
> Set objWMI = GetObject("winmgmts:" _
> & "{impersonationLevel=impersonate}!\\" _
> & strComputer & "\root\cimv2")
> ' -----------------------------------------
> ' Next section loops through ID properties
>
> strFile.WriteLine("Date: " & ", Time: " & ", ComputerName: " _
> & " EventCode: " & ", Message: ")
>
> For Each objItem In colLoggedEvents
> strTimeWritten = objItem.TimeWritten
> dtmTimeWritten = CDate(Mid(strTimeWritten, 5, 2) & "/" & _
> Mid(strTimeWritten, 7, 2) & "/" & Left(strTimeWritten, 4) _
> & " " & Mid (strTimeWritten, 9, 2) & ":" & _
> Mid(strTimeWritten, 11, 2) & ":" & Mid(strTimeWritten, 13, 2))
> dtmDate = FormatDateTime(dtmTimeWritten, vbShortDate)
> dtmTime = FormatDateTime(dtmTimeWritten, vbLongTime)
>
> Set colLoggedEvents = objWMI.ExecQuery _
> ("Select * from Win32_NTLogEvent " _
> & "Where Logfile = " & strEventLog _
> & " and TimeWritten >= 20080901" _
> & " and EventCode = " & strEventID)
>
> x = 1
> For Each oEvent In colLoggedEvents
> objExcel.Cells(x, 1).Value = oEvent.message
> objExcel.Cells(x, 2).Value = oEvent.TimeGenerated
> x = x + 1
> Next
> Next
>
> ' Save the spreadsheet and close the workbook.
> objExcel.ActiveWorkbook.SaveAs strExcelPath
> objExcel.ActiveWorkbook.Close
>
> ' Quit Excel.
> objExcel.Application.Quit
>
> WScript.quit

"tiv" <jmattivi@xxxxxx> wrote in message
news:930e7110-fd3f-4e50-9d97-6a5f99fc634e@xxxxxx

> Had to tweak a couple things....but i got it working now. Thanks a
> lot!!!!
>

Thanks for the feedback.