_Excel UDF to ADODB

[iceberg]

Hi Guys,

I have the following code. But it needs Excel program to be installed on the machine in order to execute it successfully.

Can someone guide me as to how I can bypass this using ADODB?

Thank you.

expandcollapse popup

#NoTrayIcon
Global $E = IniReadSection("idcollate.ini", "Events")
If @error Then
    MsgBox(4096, "", "Error occurred, probably no INI file.")
Else
    For $k = 1 To $E[0][0]
        Global $S = IniReadSection("idcollate.ini", "EventID")
        If @error Then
            MsgBox(4096, "", "Error occurred, probably no INI file.")
        Else
            For $j = 1 To $S[0][0]
                Global $var = IniReadSection("idcollate.ini", "Type")
                If @error Then
                    MsgBox(4096, "", "Error occurred, probably no INI file.")
                Else
                    For $i = 1 To $var[0][0]
                        Global $Serve = IniReadSection("idcollate.ini", "Servers")
                        If @error Then
                            MsgBox(4096, "", "Error occurred, probably no INI file.")
                        Else
                            For $V = 1 To $Serve[0][0]
                                $objWMIService = ObjGet("winmgmts:{impersonationLevel=impersonate,(Security)}!\\" & $Serve[$V][1] & "\root\cimv2")
                                ;MsgBox(0, "", "Select * FROM Win32_NTLogEvent WHERE Logfile = " & "'" & $E[$k][1] & "'" & " AND Type = " & "'" & $var[$i][1] & "' " & "AND EventCode = " & $S[$j][1])
                                $Query_Clause = "Select * FROM Win32_NTLogEvent WHERE Logfile = " & "'" & $E[$k][1] & "'" & " AND Type = " & "'" & $var[$i][1] & "' " & "AND EventCode = " & $S[$j][1]

                                If IsObj($objWMIService) Then
                                    $colItems = $objWMIService.ExecQuery($Query_Clause)
                                    If IsObj($colItems) Then
                                        For $objEvent In $colItems
                                            $Output = ""
                                            ;$Output &= "Category: " & $objEvent.Category & @CRLF
                                            ;$Output &= "Computer Name: " & $objEvent.ComputerName & @CRLF
                                            ;$Output &= "Event Code: " & $objEvent.EventCode & @CRLF
                                            ;$Output &= "Message: " & $objEvent.Message & @CRLF
                                            ;$Output &= "Record Number: " & $objEvent.RecordNumber & @CRLF
                                            ;$Output &= "Source Name: " & $objEvent.SourceName & @CRLF
                                            ;$Output &= "Time Written: " & $objEvent.TimeWritten & @CRLF
                                            ;$Output &= "Event Type: " & $objEvent.Type & @CRLF
                                            ;$Output &= "User: " & $objEvent.User & @CRLF

                                            ;MsgBox(0, "", @ScriptDir & "\" & $Serve[$V][1] & ".txt")
                                            Local $file = FileOpen(@ScriptDir & "\" & $Serve[$V][1] & "_" & @MDAY & @MON & @YEAR & ".txt", 1)

                                            ; Check if file opened for writing OK
                                            If $file = -1 Then
                                                MsgBox(0, "Error", "Unable to open file.")
                                                Exit
                                            EndIf

                                            $Msg = StringStripWS($objEvent.Message, 4)
                                            $Msg2 = StringStripCR($Msg)

                                            FileWrite($file, $objEvent.ComputerName & ";" & $objEvent.EventCode & ";" & $Msg2 & ";" & $objEvent.SourceName & ";" & $objEvent.TimeWritten & ";" & $objEvent.Type & @CRLF)
                                            FileClose($file)

                                            ;If MsgBox(64 + 4, "Entry Found:", $Output & @CRLF & @CRLF & "Continue?") = 7 Then Exit
                                        Next
                                    Else
                                        MsgBox(16, "Error", "$colItems is not an object.")
                                    EndIf
                                Else
                                    MsgBox(16, "Error", "$objWMIService is not an object.")
                                EndIf
                            Next
                        EndIf
                    Next
                EndIf
            Next
        EndIf
    Next
EndIf
MsgBox(0, "", "End.")
Exit

[water]

Where do you need Excel in this script?

[iceberg]

ooops.... i posted the wrong code.

here it is......

expandcollapse popup

#include <Array.au3>
#include <Excel.au3>
#include <Date.au3>

Local $sShortMonthName = _DateToMonth(@MON, 1)
Local $avArray[3]
Global $Error, $Warning, $Information

Global $E = IniReadSection("count.ini", "Events")
If @error Then
    MsgBox(4096, "", "Error occurred, probably no INI file.")
Else
    For $k = 1 To $E[0][0]
        Global $S = IniReadSection("count.ini", "Servers")
        If @error Then
            MsgBox(4096, "", "Error occurred, probably no INI file.")
        Else
            For $j = 1 To $S[0][0]
                Global $var = IniReadSection("count.ini", "Type")
                If @error Then
                    MsgBox(4096, "", "Error occurred, probably no INI file.")
                Else
                    For $i = 1 To $var[0][0]
                        $Query_Clause = "Select * FROM Win32_NTLogEvent WHERE Logfile = " & "'" & $E[$k][1] & "'" & " AND Type = " & "'" & $var[$i][1] & "'"
                        $objWMIService = ObjGet("winmgmts:{impersonationLevel=impersonate,(Security)}!\\" & $S[$j][1] & "\root\cimv2")
                        If IsObj($objWMIService) Then
                            $colItems = $objWMIService.ExecQuery($Query_Clause)
                            If IsObj($colItems) Then
                                _ArrayAdd($avArray, $colItems.Count)
                            Else
                                MsgBox(16, "Error", "$colItems is not an object.")
                            EndIf
                        Else
                            MsgBox(16, "Error", "$objWMIService is not an object.")
                        EndIf
                    Next
                EndIf

                _ArrayDelete($avArray, 0)
                _ArrayDelete($avArray, 0)
                _ArrayDelete($avArray, 0)

                Local $sFilePath1 = @ScriptDir & "\" & $S[$j][1] & ".xls"
                Local $oExcel = _ExcelBookOpen($sFilePath1, 0)

                If @error = 1 Then
                    MsgBox(0, "Error!", "Unable to Create the Excel Object")
                    Exit
                ElseIf @error = 2 Then
                    Local $oExcel = _ExcelBookNew(0)
                EndIf

                _ExcelSheetAddNew($oExcel, $sShortMonthName & "-" & @YEAR & "-" & $E[$k][1])
                _ExcelSheetDelete($oExcel, "Sheet1")
                _ExcelSheetDelete($oExcel, "Sheet2")
                _ExcelSheetDelete($oExcel, "Sheet3")
                _ExcelSheetActivate($oExcel, $sShortMonthName & "-" & @YEAR & "-" & $E[$k][1])
                _ExcelWriteArray($oExcel, @MDAY, 1, $avArray)
                _ExcelWriteFormula($oExcel, "=SUM(R1C1:R31C1)", 32, 1)
                _ExcelWriteFormula($oExcel, "=SUM(R1C2:R31C2)", 32, 2)
                _ExcelWriteFormula($oExcel, "=SUM(R1C3:R31C3)", 32, 3)
                _ExcelWriteCell($oExcel, "Total", 32, 4)
                _ExcelWriteCell($oExcel, "Error", 33, 1)
                _ExcelWriteCell($oExcel, "Warning", 33, 2)
                _ExcelWriteCell($oExcel, "Information", 33, 3)
                _ExcelBookSaveAs($oExcel, @ScriptDir & "\" & $S[$j][1] & ".xls", "xls", 0, 1)
                _ExcelBookClose($oExcel)
            Next
        EndIf
    Next
EndIf
MsgBox(0, "", "End.")
Exit

[water]

There is an UDF available that creates xls files - unfortunately only the xlsx format (XML).

I'm not sure you can create Excel files using ADO without having Excel installed.

Microsoft provides some information:

How To Use ADO with Excel Data from Visual Basic or VBA

ExcelADO demonstrates how to use ADO to read and write data in Excel workbooks

[iceberg]

thanks water.

[water]

If you find a solution can you please post here for other users as reference?

[mLipok]

@water

Here is full solutions: