Beiträge von gmmg

[autoit]

#include <GUIConstants.au3>
#include <ButtonConstants.au3>
#include <WindowsConstants.au3>
#include <WinAPI.au3>
#include <Constants.au3>
#include <File.au3>
#Include <GuiButton.au3>
#Include <GuiComboBox.au3>
#include <GuiEdit.au3>
#include <String.au3>
#include <GUIListBox.au3>
#include <GuiConstantsEx.au3>
#include <Array.au3>

[/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

Dim $aRecords, $sString, $DATAC2, $List1, $query1, $strData_kat_1, $query_name, $strData_Name, $query_name_TB_Text, $strData_TB_Text
Dim $aArray_var, $aArray_list, $aArray_var1[1], $aArray_list1[1]

[/autoit] [autoit][/autoit] [autoit]

#Region DB_Variablen
;=========Datenbank=Variablen========
$dbname = "D:\TB.mdb"
;====================================
$tblname_1 = "TB_Baustein"
$fldname_tbl1_1 = "Name"
$format = "Text(50)"
$fldname_tbl1_2 = "Kategorie"
$format = "Text(50)"
$fldname_tbl1_3 = "TB_Text"
$format = "Memo"
;====================================
$tblname_2 = "TB_Kategorie"
$fldname_tbl2_1 = "ID"
$format = "Text(50)"
$fldname_tbl2_2 = "Kategorie"
$format = "Text(50)"
;====================================
#EndRegion DB_Variablen

[/autoit] [autoit][/autoit] [autoit]

_GUI()

[/autoit] [autoit][/autoit] [autoit]

Func _GUI()

[/autoit] [autoit][/autoit] [autoit]

$gui_tb = GUICreate("TB", 600, 700,-1,-1) ;,0x80000000)
$lb1 = GUICtrlCreateLabel("Kategorie", 10, 20)
;--------------
$cmb = GUICtrlCreateCombo("", 80, 20,280,20) ; create first item
$query_kat = "SELECT Kategorie FROM " & $tblname_2 ; & " WHERE PC = " & "'" & @UserName & "'"
$strData_kat_1 = _ReadOneFld_Combo($query_kat, $dbname, $fldname_tbl2_2)
GUICtrlSetData($cmb, $strData_kat_1, "Alle Kategorien") ; add other item snd set a new default
;----------------
;$telEdit1 = _GUICtrlEdit_Create($gui_tb, "erf", 80, 80, 200, 300)
$lb2 = GUICtrlCreateLabel("Auswahl", 10, 80)
$List1 = GUICtrlCreateList("", 80, 80, 390, 214)
$query_name = "SELECT Name FROM " & $tblname_1
$strData_Name = _ReadOneFld_Combo($query_name, $dbname, $fldname_tbl1_1)
GUICtrlSetData($List1, $strData_Name)
$Edit2 = _GUICtrlEdit_Create($gui_tb, "", 40, 300, 490, 350, $ES_MULTILINE + $ES_READONLY + $WS_VSCROLL)
GUISetState(@SW_SHOW)

[/autoit] [autoit][/autoit] [autoit]

While 1
;$windowtext = _WinAPI_GetWindowText($hWnd)
;IF $windowtext = "TB" Then GUISetState(@SW_SHOW)
$msg = GUIGetMsg()
If $msg = $GUI_EVENT_CLOSE Then
;GUIDelete($gui_tb)
Exit;Loop
EndIf
Switch $msg

[/autoit] [autoit][/autoit] [autoit]

Case $cmb
_GUICtrlListBox_ResetContent($List1)
$wert_cmb = GUICtrlRead($cmb)
;----db lesen---
$query_id = "SELECT ID FROM " & $tblname_2 & " WHERE " & $tblname_2 & "." & $fldname_tbl2_2 & " = '" & $wert_cmb & "'"
$strData_id = _ReadOneFld($query_id, $dbname, $fldname_tbl2_1)
$strData_id = StringStripWS($strData_id,8)
;---------------
; alle einträge der kategorie mit ensprechender id auflisten
IF $strData_id = "1" Then
$query_name = "SELECT Name FROM " & $tblname_1
Else
$query_name = "SELECT Name FROM " & $tblname_1 & " WHERE " & $tblname_1 & "." & $fldname_tbl1_2 & " = '" & $strData_id &"'"
EndIf
$strData_Name = _ReadOneFld_Combo($query_name, $dbname, $fldname_tbl1_1)
GUICtrlSetData($List1, $strData_Name)

[/autoit] [autoit][/autoit] [autoit]

Case $List1
Dim $aArray_var1[1]
Dim $aArray_list1[1]

[/autoit] [autoit][/autoit] [autoit]

$wert_list1 = GUICtrlRead($List1);StringStripWS (GUICtrlRead($List1),1)
;MsgBox(0,"1",$wert_list1)
;---------------
$query_name_TB_Text = "SELECT TB_Text FROM " & $tblname_1 & " WHERE " & $fldname_tbl1_1 & ' = "' & $wert_list1 & '"'
$strData_TB_Text = _ReadOneFld($query_name_TB_Text, $dbname, $fldname_tbl1_3)

[/autoit] [autoit][/autoit] [autoit]

$aArray_var = _StringBetween($strData_TB_Text, '{var:', '}')
;_ArrayDisplay($aArray_var, 'var_test')
;MsgBox(0,"", @error)
If @error <> 1 Then
;_ArrayDisplay($aArray_var, 'var')
;$aArray_var = _ArrayUnique($aArray_var)
;_GUI_Var_List()
For $x = 0 To UBound($aArray_var) -1
$slv = StringLeft($aArray_var[$x],2)
;MsgBox(0,"",$slv)
_ArrayAdd($aArray_var1, $slv & '|var|' & $aArray_var[$x])
Next
;_ArrayDisplay($aArray_var1, "$avArray AFTER _ArrayInsert()")

[/autoit] [autoit][/autoit] [autoit]

EndIf
$aArray_list = _StringBetween($strData_TB_Text, '{list:', '}')
;_ArrayDisplay($aArray_list, 'list')
;MsgBox(0,"", @error)
If @error <> 1 Then
For $x = 0 To UBound($aArray_list) -1
$sll = StringLeft($aArray_list[$x],2)
;MsgBox(0,"",$sll)
_ArrayAdd($aArray_list1, $sll & '|list|' & $aArray_list[$x])
Next
;_ArrayDisplay($aArray_list1, "$avArray AFTER _ArrayInsert()")
EndIf

[/autoit] [autoit][/autoit] [autoit]

_ArrayDelete($aArray_var1,0)
;_ArrayDisplay($aArray_var1, "1")
_ArrayDelete($aArray_list1,0)
;_ArrayDisplay($aArray_list1, "2")

[/autoit] [autoit][/autoit] [autoit]

;----array zusammenführen----

[/autoit] [autoit][/autoit] [autoit]

_ArrayConcatenate($aArray_var1, $aArray_list1)
$aArray_var1 = _ArrayUnique($aArray_var1)
_ArrayDelete($aArray_var1, 0)
_ArraySort($aArray_var1)

[/autoit] [autoit][/autoit] [autoit]

_GUICtrlEdit_SetText($Edit2, $strData_TB_Text)
_ArrayDisplay($aArray_var1, 'all')

[/autoit] [autoit][/autoit] [autoit]

_GUI_Var_List()

[/autoit] [autoit][/autoit] [autoit]

;----
;ClipPut($wert1)

[/autoit] [autoit][/autoit] [autoit]

EndSwitch
WEnd

[/autoit] [autoit][/autoit] [autoit]

EndFunc

[/autoit] [autoit][/autoit] [autoit]

Func _GUI_Var_List()

[/autoit] [autoit][/autoit] [autoit]

Dim $Labels_var[100]
Dim $Inputs_var[100]

[/autoit] [autoit][/autoit] [autoit]

$gui_var = GUICreate("label array", 450, 400)
;----------
$x_l = 20
$y_l = 10
;----------
$x_i = 200
$y_i = 10
;----------
For $n = 0 To UBound($aArray_var1) - 1
$aArray_combo = ""
$result = ""
$res_v = StringInStr($aArray_var1[$n], "|var|")
$res_l = StringInStr($aArray_var1[$n], "|list|")
;MsgBox(0,"test",$res_l & " " & $res_v)
If $res_v > 0 Then
$result = StringTrimLeft($aArray_var1[$n], 7)
$Labels_var[$n] = GUICtrlCreateLabel($result, $x_l, $y_l, 200, 20)
$Inputs_var[$n] = GUICtrlCreateInput("", $x_i, $y_i, 200, 20)
EndIf
If $res_l > 0 Then
$result = StringTrimLeft($aArray_var1[$n],
$aArray_combo = _StringBetween($aArray_var1[$n], '|list|', ':')
$Labels_var[$n] = GUICtrlCreateLabel($aArray_combo[0], $x_l, $y_l, 200, 20)
$Inputs_var[$n] = GUICtrlCreateCombo("", $x_i, $y_i, 200, 20)
EndIf

[/autoit] [autoit][/autoit] [autoit]

;$x += 150
;If $x + 120 > 600 Then
$y_l += 20
$y_i += 20
;$x = 20
;EndIf
Next

[/autoit] [autoit][/autoit] [autoit]

$btn_var = GUICtrlCreateButton("Button", 100, $y_l + 10, 80, 25)

[/autoit] [autoit][/autoit] [autoit]

GUISetState()

[/autoit] [autoit][/autoit] [autoit]

While 1
$msg = GUIGetMsg()
If $msg = $GUI_EVENT_CLOSE Then
GUIDelete($gui_var)
ExitLoop
EndIf

[/autoit] [autoit][/autoit] [autoit]

WEnd

[/autoit] [autoit][/autoit] [autoit]

EndFunc

[/autoit] [autoit][/autoit] [autoit]

#Region Textdatei
#CS
Func _File_01()

[/autoit] [autoit][/autoit] [autoit]

$DATAC2 = ""
$sString = ""
$file = (@ScriptDir & "\TB.txt")

[/autoit] [autoit][/autoit] [autoit]

If Not _FileReadToArray($file,$aRecords) Then
MsgBox(4096,"Error", " Error reading log to Array error:" & @error)
Exit
EndIf

[/autoit] [autoit][/autoit] [autoit]

For $x = 1 to $aRecords[0]
;Msgbox(0,'Record:' & $x, $aRecords[$x])
$sString &= $aRecords[$x]
Next
;MsgBox(0,"", $sString)
$DATAC2 = $sString

[/autoit] [autoit][/autoit] [autoit]

EndFunc
#CE
#EndRegion Textdatei

[/autoit] [autoit][/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

;------access DB auslesen -------------
Func _ReadOneFld($_sql, $_dbname, $_field)
Dim $_output
$adoCon = ObjCreate("ADODB.Connection")
$adoCon.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $_dbname)
$adoRs = ObjCreate("ADODB.Recordset")
$adoRs.CursorType = 1
$adoRs.LockType = 3
$adoRs.Open($_sql, $adoCon)

[/autoit] [autoit][/autoit] [autoit]

With $adoRs
If .RecordCount Then
While Not .EOF
$_output = $_output & .Fields($_field).Value & @CRLF
.MoveNext
WEnd
EndIf
EndWith
$adoCon.Close
Return $_output
EndFunc ;==>_ReadOneFld

[/autoit] [autoit][/autoit] [autoit]

Func _ReadOneFld_Combo($_sql, $_dbname, $_field)
Dim $_output
$adoCon = ObjCreate("ADODB.Connection")
$adoCon.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $_dbname)
$adoRs = ObjCreate("ADODB.Recordset")
$adoRs.CursorType = 1
$adoRs.LockType = 3
$adoRs.Open($_sql, $adoCon)

[/autoit] [autoit][/autoit] [autoit]

With $adoRs
If .RecordCount Then
While Not .EOF
$_output = $_output & .Fields($_field).Value & "|" ;@CRLF
.MoveNext
WEnd
EndIf
EndWith
$adoCon.Close
Return $_output
EndFunc ;==>_ReadOneFld

[/autoit]

[autoit]

#include <GUIConstants.au3>
#include <ButtonConstants.au3>
#include <WindowsConstants.au3>
#include <WinAPI.au3>
#include <Constants.au3>
#include <File.au3>
#Include <GuiButton.au3>
#Include <GuiComboBox.au3>
#include <GuiEdit.au3>
#include <String.au3>
#include <GUIListBox.au3>
#include <GuiConstantsEx.au3>
#include <Array.au3>

[/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

Dim $aRecords, $sString, $DATAC2, $List1, $query1, $strData_kat_1, $query_name, $strData_Name, $query_name_TB_Text, $strData_TB_Text
Local $aArray_var, $aArray_list

[/autoit] [autoit][/autoit] [autoit]

#Region DB_Variablen
;=========Datenbank=Variablen========
$dbname = "D:\TB.mdb"
;====================================
$tblname_1 = "TB_Baustein"
$fldname_tbl1_1 = "Name"
$format = "Text(50)"
$fldname_tbl1_2 = "Kategorie"
$format = "Text(50)"
$fldname_tbl1_3 = "TB_Text"
$format = "Memo"
;====================================
$tblname_2 = "TB_Kategorie"
$fldname_tbl2_1 = "ID"
$format = "Text(50)"
$fldname_tbl2_2 = "Kategorie"
$format = "Text(50)"
;====================================
#EndRegion DB_Variablen

[/autoit] [autoit][/autoit] [autoit]

_GUI()

[/autoit] [autoit][/autoit] [autoit]

Func _GUI()

[/autoit] [autoit][/autoit] [autoit]

$gui_tb = GUICreate("TB", 600, 700,-1,-1) ;,0x80000000)
$lb1 = GUICtrlCreateLabel("Kategorie", 10, 20)
;--------------
$cmb = GUICtrlCreateCombo("", 80, 20,280,20) ; create first item
$query_kat = "SELECT Kategorie FROM " & $tblname_2 ; & " WHERE PC = " & "'" & @UserName & "'"
$strData_kat_1 = _ReadOneFld_Combo($query_kat, $dbname, $fldname_tbl2_2)
GUICtrlSetData($cmb, $strData_kat_1, "Alle Kategorien") ; add other item snd set a new default
;----------------
;$telEdit1 = _GUICtrlEdit_Create($gui_tb, "erf", 80, 80, 200, 300)
$lb2 = GUICtrlCreateLabel("Auswahl", 10, 80)
$List1 = GUICtrlCreateList("", 80, 80, 390, 214)
$query_name = "SELECT Name FROM " & $tblname_1
$strData_Name = _ReadOneFld_Combo($query_name, $dbname, $fldname_tbl1_1)
GUICtrlSetData($List1, $strData_Name)
$Edit2 = _GUICtrlEdit_Create($gui_tb, "", 40, 300, 490, 350, $ES_MULTILINE + $ES_READONLY + $WS_VSCROLL)
GUISetState(@SW_SHOW)

[/autoit] [autoit][/autoit] [autoit]

While 1
;$windowtext = _WinAPI_GetWindowText($hWnd)
;IF $windowtext = "TB" Then GUISetState(@SW_SHOW)
$msg = GUIGetMsg()
If $msg = $GUI_EVENT_CLOSE Then
;GUIDelete($gui_tb)
Exit;Loop
EndIf
Switch $msg

[/autoit] [autoit][/autoit] [autoit]

Case $cmb
_GUICtrlListBox_ResetContent($List1)
$wert_cmb = GUICtrlRead($cmb)
;----db lesen---
$query_id = "SELECT ID FROM " & $tblname_2 & " WHERE " & $tblname_2 & "." & $fldname_tbl2_2 & " = '" & $wert_cmb & "'"
$strData_id = _ReadOneFld($query_id, $dbname, $fldname_tbl2_1)
$strData_id = StringStripWS($strData_id,8)
;---------------
; alle einträge der kategorie mit ensprechender id auflisten
IF $strData_id = "1" Then
$query_name = "SELECT Name FROM " & $tblname_1
Else
$query_name = "SELECT Name FROM " & $tblname_1 & " WHERE " & $tblname_1 & "." & $fldname_tbl1_2 & " = '" & $strData_id &"'"
EndIf
;$query_name = "SELECT Name FROM TB_Baustein WHERE TB_Baustein.Kategorie = '" & $strData_id &"'"
$strData_Name = _ReadOneFld_Combo($query_name, $dbname, $fldname_tbl1_1)
;MsgBox(0,$strData_id ,$strData_Name)
GUICtrlSetData($List1, $strData_Name)

[/autoit] [autoit][/autoit] [autoit]

Case $List1

[/autoit] [autoit][/autoit] [autoit]

$strData_TB_Text = ""
$wert_list1 = GUICtrlRead($List1);StringStripWS (GUICtrlRead($List1),1)
;MsgBox(0,"1",$wert_list1)
;---------------
$query_name_TB_Text = "SELECT TB_Text FROM " & $tblname_1 & " WHERE " & $fldname_tbl1_1 & ' = "' & $wert_list1 & '"'
;ConsoleWrite ($query_name_TB_Text & @CRLF)
$strData_TB_Text = _ReadOneFld($query_name_TB_Text, $dbname, $fldname_tbl1_3)
;MsgBox(0,"2" ,$strData_TB_Text)
;GUICtrlSetData($Edit2, $strData_TB_Text)
$aArray_var = _StringBetween($strData_TB_Text, '{var:', '}')
;_ArrayDisplay($aArray_var, 'var')
If @error <> 1 Then
;_ArrayDisplay($aArray_var, 'var')
;$aArray_var = _ArrayUnique($aArray_var)
;_GUI_Var_List()
EndIf
$aArray_list = _StringBetween($strData_TB_Text, '{list:', '}')
;_ArrayDisplay($aArray_list, 'list')
If @error <> 1 Then
;_ArrayDisplay($aArray_list, 'list')
;$aArray_list = _ArrayUnique($aArray_list)
;_GUI_Var_List()
EndIf
;array zusammenführen ...
_ArrayConcatenate($aArray_var, $aArray_list)
$aArray_var = _ArrayUnique($aArray_var)
_ArrayDelete($aArray_var, 0)
_ArraySort($aArray_var)
_ArrayDisplay($aArray_var, 'var')
_GUI_Var_List()
_GUICtrlEdit_SetText($Edit2, $strData_TB_Text)
;----
;ClipPut($wert1)

[/autoit] [autoit][/autoit] [autoit]

EndSwitch
WEnd

[/autoit] [autoit][/autoit] [autoit]

EndFunc

[/autoit] [autoit][/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

Func _GUI_Var_List()

[/autoit] [autoit][/autoit] [autoit]

Dim $Labels_var[100]
Dim $Inputs_var[100]

[/autoit] [autoit][/autoit] [autoit]

$gui_var = GUICreate("label array", 450, 400)
;----------
$x_l = 20
$y_l = 10
;----------
$x_i = 200
$y_i = 10
;----------
For $n = 1 To UBound($aArray_var) - 1
$Labels_var[$n] = GUICtrlCreateLabel($aArray_var[$n], $x_l, $y_l, 200, 20)
;If
$Inputs_var[$n] = GUICtrlCreateInput("", $x_i, $y_i, 200, 20)
;$Inputs_var[$n] = GUICtrlCreateCombo("", $x_i, $y_i, 200, 20)
;$x += 150
;If $x + 120 > 600 Then
$y_l += 20
$y_i += 20
;$x = 20
;EndIf
Next

[/autoit] [autoit][/autoit] [autoit]

$btn_var = GUICtrlCreateButton("Button", 100, $y_l + 10, 80, 25)

[/autoit] [autoit][/autoit] [autoit]

GUISetState()

[/autoit] [autoit][/autoit] [autoit]

While 1
$msg = GUIGetMsg()
If $msg = $GUI_EVENT_CLOSE Then
GUIDelete($gui_var)
ExitLoop
EndIf

[/autoit] [autoit][/autoit] [autoit]

WEnd

[/autoit] [autoit][/autoit] [autoit]

EndFunc

[/autoit] [autoit][/autoit] [autoit]

#Region Textdatei
#CS
Func _File_01()

[/autoit] [autoit][/autoit] [autoit]

$DATAC2 = ""
$sString = ""
$file = (@ScriptDir & "\TB.txt")

[/autoit] [autoit][/autoit] [autoit]

If Not _FileReadToArray($file,$aRecords) Then
MsgBox(4096,"Error", " Error reading log to Array error:" & @error)
Exit
EndIf

[/autoit] [autoit][/autoit] [autoit]

For $x = 1 to $aRecords[0]
;Msgbox(0,'Record:' & $x, $aRecords[$x])
$sString &= $aRecords[$x]
Next
;MsgBox(0,"", $sString)
$DATAC2 = $sString

[/autoit] [autoit][/autoit] [autoit]

EndFunc
#CE
#EndRegion Textdatei

[/autoit] [autoit][/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

;------access DB auslesen -------------
Func _ReadOneFld($_sql, $_dbname, $_field)
Dim $_output
$adoCon = ObjCreate("ADODB.Connection")
$adoCon.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $_dbname)
$adoRs = ObjCreate("ADODB.Recordset")
$adoRs.CursorType = 1
$adoRs.LockType = 3
$adoRs.Open($_sql, $adoCon)

[/autoit] [autoit][/autoit] [autoit]

With $adoRs
If .RecordCount Then
While Not .EOF
$_output = $_output & .Fields($_field).Value & @CRLF
.MoveNext
WEnd
EndIf
EndWith
$adoCon.Close
Return $_output
EndFunc ;==>_ReadOneFld

[/autoit] [autoit][/autoit] [autoit]

Func _ReadOneFld_Combo($_sql, $_dbname, $_field)
Dim $_output
$adoCon = ObjCreate("ADODB.Connection")
$adoCon.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $_dbname)
$adoRs = ObjCreate("ADODB.Recordset")
$adoRs.CursorType = 1
$adoRs.LockType = 3
$adoRs.Open($_sql, $adoCon)

[/autoit] [autoit][/autoit] [autoit]

With $adoRs
If .RecordCount Then
While Not .EOF
$_output = $_output & .Fields($_field).Value & "|" ;@CRLF
.MoveNext
WEnd
EndIf
EndWith
$adoCon.Close
Return $_output
EndFunc ;==>_ReadOneFld

[/autoit]

[autoit]

#include <GUIConstants.au3>
#include <ButtonConstants.au3>
#include <WindowsConstants.au3>
#include <WinAPI.au3>
#include <Constants.au3>
#include <File.au3>
#Include <GuiButton.au3>
#Include <GuiComboBox.au3>
#include <GuiEdit.au3>
#include <String.au3>
#include <GUIListBox.au3>
#include <GuiConstantsEx.au3>
#include <Array.au3>

[/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

Dim $aRecords, $sString, $DATAC2, $List1, $query1, $strData_kat_1, $query_name, $strData_Name, $query_name_TB_Text, $strData_TB_Text
Local $aArray_var, $aArray_list

[/autoit] [autoit][/autoit] [autoit]

#Region DB_Variablen
;=========Datenbank=Variablen========
$dbname = "D:\TB.mdb"
;====================================
$tblname_1 = "TB_Baustein"
$fldname_tbl1_1 = "Name"
$format = "Text(50)"
$fldname_tbl1_2 = "Kategorie"
$format = "Text(50)"
$fldname_tbl1_3 = "TB_Text"
$format = "Memo"
;====================================
$tblname_2 = "TB_Kategorie"
$fldname_tbl2_1 = "ID"
$format = "Text(50)"
$fldname_tbl2_2 = "Kategorie"
$format = "Text(50)"
;====================================
#EndRegion DB_Variablen

[/autoit] [autoit][/autoit] [autoit]

_GUI()

[/autoit] [autoit][/autoit] [autoit]

Func _GUI()

[/autoit] [autoit][/autoit] [autoit]

$gui_tb = GUICreate("TB", 600, 700,-1,-1) ;,0x80000000)
$lb1 = GUICtrlCreateLabel("Kategorie", 10, 20)
;--------------
$cmb = GUICtrlCreateCombo("", 80, 20,280,20) ; create first item
$query_kat = "SELECT Kategorie FROM " & $tblname_2 ; & " WHERE PC = " & "'" & @UserName & "'"
$strData_kat_1 = _ReadOneFld_Combo($query_kat, $dbname, $fldname_tbl2_2)
GUICtrlSetData($cmb, $strData_kat_1, "Alle Kategorien") ; add other item snd set a new default
;----------------
;$telEdit1 = _GUICtrlEdit_Create($gui_tb, "erf", 80, 80, 200, 300)
$lb2 = GUICtrlCreateLabel("Auswahl", 10, 80)
$List1 = GUICtrlCreateList("", 80, 80, 390, 214)
$query_name = "SELECT Name FROM " & $tblname_1
$strData_Name = _ReadOneFld_Combo($query_name, $dbname, $fldname_tbl1_1)
GUICtrlSetData($List1, $strData_Name)
$Edit2 = _GUICtrlEdit_Create($gui_tb, "", 40, 300, 490, 350, $ES_MULTILINE + $ES_READONLY + $WS_VSCROLL)
GUISetState(@SW_SHOW)

[/autoit] [autoit][/autoit] [autoit]

While 1
;$windowtext = _WinAPI_GetWindowText($hWnd)
;IF $windowtext = "TB" Then GUISetState(@SW_SHOW)
$msg = GUIGetMsg()
If $msg = $GUI_EVENT_CLOSE Then
;GUIDelete($gui_tb)
Exit;Loop
EndIf
Switch $msg

[/autoit] [autoit][/autoit] [autoit]

Case $cmb
_GUICtrlListBox_ResetContent($List1)
$wert_cmb = GUICtrlRead($cmb)
;----db lesen---
$query_id = "SELECT ID FROM " & $tblname_2 & " WHERE " & $tblname_2 & "." & $fldname_tbl2_2 & " = '" & $wert_cmb & "'"
$strData_id = _ReadOneFld($query_id, $dbname, $fldname_tbl2_1)
$strData_id = StringStripWS($strData_id,8)
;---------------
; alle einträge der kategorie mit ensprechender id auflisten
IF $strData_id = "1" Then
$query_name = "SELECT Name FROM " & $tblname_1
Else
$query_name = "SELECT Name FROM " & $tblname_1 & " WHERE " & $tblname_1 & "." & $fldname_tbl1_2 & " = '" & $strData_id &"'"
EndIf
;$query_name = "SELECT Name FROM TB_Baustein WHERE TB_Baustein.Kategorie = '" & $strData_id &"'"
$strData_Name = _ReadOneFld_Combo($query_name, $dbname, $fldname_tbl1_1)
;MsgBox(0,$strData_id ,$strData_Name)
GUICtrlSetData($List1, $strData_Name)

[/autoit] [autoit][/autoit] [autoit]

Case $List1

[/autoit] [autoit][/autoit] [autoit]

$strData_TB_Text = ""
$wert_list1 = GUICtrlRead($List1);StringStripWS (GUICtrlRead($List1),1)
;MsgBox(0,"1",$wert_list1)
;---------------
$query_name_TB_Text = "SELECT TB_Text FROM " & $tblname_1 & " WHERE " & $fldname_tbl1_1 & ' = "' & $wert_list1 & '"'
;ConsoleWrite ($query_name_TB_Text & @CRLF)
$strData_TB_Text = _ReadOneFld($query_name_TB_Text, $dbname, $fldname_tbl1_3)
;MsgBox(0,"2" ,$strData_TB_Text)
;GUICtrlSetData($Edit2, $strData_TB_Text)
$aArray_var = _StringBetween($strData_TB_Text, '{var:', '}')
;_ArrayDisplay($aArray_var, 'var')
If @error <> 1 Then
#CS _ArrayDisplay($aArray_var, 'variablen')
;schleife durch das array um den replacestring zu generieren
For $r = 0 To UBound($aArray_var) - 1
$replacestring = '{var:' & $aArray_var[$r] & '}'
MsgBox(0,"",$replacestring)
Next
#CE

[/autoit] [autoit][/autoit] [autoit]

_GUI_Var_List()
EndIf

[/autoit] [autoit][/autoit] [autoit]

;MsgBox(0,"",@error)
;If @error <> 1 Then _GUI_Var_List()
;$aArray_list = _StringBetween($strData_TB_Text, '{list:', '}')
;_ArrayDisplay($aArray_list, 'Default Search')

[/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

;$before = "fexp=1234,5678&algorithm"
;$replace_with = "907605"
;$after = StringReplace(StringRegExpReplace($before, "(fexp=)(.*)(&algorithm)", "$1 " & $replace_with & "$3"), "= ", "=")
;$after = StringRegExpReplace($before, "(fexp=)(.*)(&algorithm)", "${1}" & $replace_with & "$3")
;MsgBox(0, "Test", $after)

[/autoit] [autoit][/autoit] [autoit]

_GUICtrlEdit_SetText($Edit2, $strData_TB_Text)
;----
;ClipPut($wert1)

[/autoit] [autoit][/autoit] [autoit]

EndSwitch
WEnd

[/autoit] [autoit][/autoit] [autoit]

EndFunc

[/autoit] [autoit][/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

Func _GUI_Var_List()

[/autoit] [autoit][/autoit] [autoit]

$aArray_var = _ArrayUnique($aArray_var)
Dim $Labels[100]

[/autoit] [autoit][/autoit] [autoit]

$gui_var = GUICreate("label array", 600, 400)

[/autoit] [autoit][/autoit] [autoit]

$x = 20
$y = 70
For $n = 1 To UBound($aArray_var) - 1
$Labels[$n] = GUICtrlCreateLabel($aArray_var[$n], $x, $y, 200, 20)
;$x += 150
;If $x + 120 > 600 Then
$y += 20
$x = 20
;EndIf
Next
GUISetState()

[/autoit] [autoit][/autoit] [autoit]

While 1
$msg = GUIGetMsg()
If $msg = $GUI_EVENT_CLOSE Then
GUIDelete($gui_var)
ExitLoop
EndIf

[/autoit] [autoit][/autoit] [autoit]

WEnd

[/autoit] [autoit][/autoit] [autoit]

EndFunc

[/autoit] [autoit][/autoit] [autoit]

#Region Textdatei
#CS
Func _File_01()

[/autoit] [autoit][/autoit] [autoit]

$DATAC2 = ""
$sString = ""
$file = (@ScriptDir & "\TB.txt")

[/autoit] [autoit][/autoit] [autoit]

If Not _FileReadToArray($file,$aRecords) Then
MsgBox(4096,"Error", " Error reading log to Array error:" & @error)
Exit
EndIf

[/autoit] [autoit][/autoit] [autoit]

For $x = 1 to $aRecords[0]
;Msgbox(0,'Record:' & $x, $aRecords[$x])
$sString &= $aRecords[$x]
Next
;MsgBox(0,"", $sString)
$DATAC2 = $sString

[/autoit] [autoit][/autoit] [autoit]

EndFunc
#CE
#EndRegion Textdatei

[/autoit] [autoit][/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

;------access DB auslesen -------------
Func _ReadOneFld($_sql, $_dbname, $_field)
Dim $_output
$adoCon = ObjCreate("ADODB.Connection")
$adoCon.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $_dbname)
$adoRs = ObjCreate("ADODB.Recordset")
$adoRs.CursorType = 1
$adoRs.LockType = 3
$adoRs.Open($_sql, $adoCon)

[/autoit] [autoit][/autoit] [autoit]

With $adoRs
If .RecordCount Then
While Not .EOF
$_output = $_output & .Fields($_field).Value & @CRLF
.MoveNext
WEnd
EndIf
EndWith
$adoCon.Close
Return $_output
EndFunc ;==>_ReadOneFld

[/autoit] [autoit][/autoit] [autoit]

Func _ReadOneFld_Combo($_sql, $_dbname, $_field)
Dim $_output
$adoCon = ObjCreate("ADODB.Connection")
$adoCon.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $_dbname)
$adoRs = ObjCreate("ADODB.Recordset")
$adoRs.CursorType = 1
$adoRs.LockType = 3
$adoRs.Open($_sql, $adoCon)

[/autoit] [autoit][/autoit] [autoit]

With $adoRs
If .RecordCount Then
While Not .EOF
$_output = $_output & .Fields($_field).Value & "|" ;@CRLF
.MoveNext
WEnd
EndIf
EndWith
$adoCon.Close
Return $_output
EndFunc ;==>_ReadOneFld

[/autoit]

{list:Anrede:Frau=Sehr geehrte Frau#Herr=Sehr geehrter Herr} {var:Kundennachname},

vielen Dank für Ihre Email.

Bitte fügen Sie uns immer die vorangegangene eMail-Korrespondenz ein, nur so
können wir eine schnelle Bearbeitung Ihrer eMail gewährleisten.

Vielen Dank für Ihre Unterstützung.

Gern beantworten wir Ihnen weitere Fragen auch telefonisch.
Sie erreichen uns täglich von 08.00 - 24.00 Uhr.

Freundliche Grüße

[autoit]

#include <GUIConstants.au3>
#include <ButtonConstants.au3>
#include <WindowsConstants.au3>
#include <WinAPI.au3>
#include <Constants.au3>
#include <File.au3>
#Include <GuiButton.au3>
#Include <GuiComboBox.au3>
#include <GuiEdit.au3>
#include <String.au3>
#include <GUIListBox.au3>
#include <GuiConstantsEx.au3>
#include <Array.au3>

[/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

Dim $aRecords, $sString, $DATAC2, $List1, $query1, $strData_kat_1, $query_name, $strData_Name, $query_name_TB_Text, $strData_TB_Text
Local $aArray_var, $aArray_list

[/autoit] [autoit][/autoit] [autoit]

#Region DB_Variablen
;=========Datenbank=Variablen========
$dbname = "D:\TB.mdb"
;====================================
$tblname_1 = "TB_Baustein"
$fldname_tbl1_1 = "Name"
$format = "Text(50)"
$fldname_tbl1_2 = "Kategorie"
$format = "Text(50)"
$fldname_tbl1_3 = "TB_Text"
$format = "Memo"
;====================================
$tblname_2 = "TB_Kategorie"
$fldname_tbl2_1 = "ID"
$format = "Text(50)"
$fldname_tbl2_2 = "Kategorie"
$format = "Text(50)"
;====================================
#EndRegion DB_Variablen

[/autoit] [autoit][/autoit] [autoit]

_GUI()

[/autoit] [autoit][/autoit] [autoit]

Func _GUI()

[/autoit] [autoit][/autoit] [autoit]

$gui_tb = GUICreate("TB", 600, 700,-1,-1) ;,0x80000000)
$lb1 = GUICtrlCreateLabel("Kategorie", 10, 20)
;--------------
$cmb = GUICtrlCreateCombo("", 80, 20,280,20) ; create first item
$query_kat = "SELECT Kategorie FROM " & $tblname_2 ; & " WHERE PC = " & "'" & @UserName & "'"
$strData_kat_1 = _ReadOneFld_Combo($query_kat, $dbname, $fldname_tbl2_2)
GUICtrlSetData($cmb, $strData_kat_1, "Alle Kategorien") ; add other item snd set a new default
;----------------
;$telEdit1 = _GUICtrlEdit_Create($gui_tb, "erf", 80, 80, 200, 300)
$lb2 = GUICtrlCreateLabel("Auswahl", 10, 80)
$List1 = GUICtrlCreateList("", 80, 80, 390, 214)
$query_name = "SELECT Name FROM " & $tblname_1
$strData_Name = _ReadOneFld_Combo($query_name, $dbname, $fldname_tbl1_1)
GUICtrlSetData($List1, $strData_Name)
$Edit2 = _GUICtrlEdit_Create($gui_tb, "", 40, 300, 490, 350, $ES_MULTILINE + $ES_READONLY + $WS_VSCROLL)
GUISetState(@SW_SHOW)

[/autoit] [autoit][/autoit] [autoit]

While 1
;$windowtext = _WinAPI_GetWindowText($hWnd)
;IF $windowtext = "TB" Then GUISetState(@SW_SHOW)
$msg = GUIGetMsg()
If $msg = $GUI_EVENT_CLOSE Then
;GUIDelete($gui_tb)
Exit;Loop
EndIf
Switch $msg

[/autoit] [autoit][/autoit] [autoit]

Case $cmb
_GUICtrlListBox_ResetContent($List1)
$wert_cmb = GUICtrlRead($cmb)
;----db lesen---
$query_id = "SELECT ID FROM " & $tblname_2 & " WHERE " & $tblname_2 & "." & $fldname_tbl2_2 & " = '" & $wert_cmb & "'"
$strData_id = _ReadOneFld($query_id, $dbname, $fldname_tbl2_1)
$strData_id = StringStripWS($strData_id,8)
;---------------
; alle einträge der kategorie mit ensprechender id auflisten
IF $strData_id = "1" Then
$query_name = "SELECT Name FROM " & $tblname_1
Else
$query_name = "SELECT Name FROM " & $tblname_1 & " WHERE " & $tblname_1 & "." & $fldname_tbl1_2 & " = '" & $strData_id &"'"
EndIf
;$query_name = "SELECT Name FROM TB_Baustein WHERE TB_Baustein.Kategorie = '" & $strData_id &"'"
$strData_Name = _ReadOneFld_Combo($query_name, $dbname, $fldname_tbl1_1)
;MsgBox(0,$strData_id ,$strData_Name)
GUICtrlSetData($List1, $strData_Name)

[/autoit] [autoit][/autoit] [autoit]

Case $List1

[/autoit] [autoit][/autoit] [autoit]

$strData_TB_Text = ""
$wert_list1 = GUICtrlRead($List1);StringStripWS (GUICtrlRead($List1),1)
;MsgBox(0,"1",$wert_list1)
;---------------
$query_name_TB_Text = "SELECT TB_Text FROM " & $tblname_1 & " WHERE " & $fldname_tbl1_1 & ' = "' & $wert_list1 & '"'
;ConsoleWrite ($query_name_TB_Text & @CRLF)
$strData_TB_Text = _ReadOneFld($query_name_TB_Text, $dbname, $fldname_tbl1_3)
;MsgBox(0,"2" ,$strData_TB_Text)
;GUICtrlSetData($Edit2, $strData_TB_Text)
$aArray_var = _StringBetween($strData_TB_Text, '{var:', '}')
_ArrayDisplay($aArray_var, 'var')
MsgBox(0,"",@error)
If @error <> 1 Then _GUI_Var_List()
$aArray_list = _StringBetween($strData_TB_Text, '{list:', '}')
;_ArrayDisplay($aArray_list, 'Default Search')

[/autoit] [autoit][/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

;$before = "fexp=1234,5678&algorithm"
;$replace_with = "907605"
;$after = StringReplace(StringRegExpReplace($before, "(fexp=)(.*)(&algorithm)", "$1 " & $replace_with & "$3"), "= ", "=")
;$after = StringRegExpReplace($before, "(fexp=)(.*)(&algorithm)", "${1}" & $replace_with & "$3")
;MsgBox(0, "Test", $after)

[/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

_GUICtrlEdit_SetText($Edit2, $strData_TB_Text)
;----
;ClipPut($wert1)

[/autoit] [autoit][/autoit] [autoit]

EndSwitch
WEnd

[/autoit] [autoit][/autoit] [autoit]

EndFunc

[/autoit] [autoit][/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

Func _GUI_Var_List()

[/autoit] [autoit][/autoit] [autoit]

_ArrayDisplay($aArray_var, 'Default Search')
$gui_var = GUICreate("TEST", 400, 300, -1, -1)
;GUICtrlSetData($Labels[$aArray_var])
GUISetState(@SW_SHOW)
;Dim $Labels[$objItem]
Dim $objItem, $Labels, $i, $l, $n

[/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

If IsObj($aArray_var) Then
MsgBox(0,"","test1")
For $objItem In $aArray_var
MsgBox(0,"",$objItem)
;$aLabels[$i][0] = $objItem.Name
;$aLabels[$i][1] = $objItem.VolumeName
;GUICtrlCreateLabel($aLabels[$i][0] & " -> " & $aLabels[$i][1], 50, $l)
$i += 1
$l += 25
Next
EndIf
;ReDim $aLabels[$i][2]

[/autoit] [autoit][/autoit] [autoit]

While 1
$msg = GUIGetMsg()
If $msg = $GUI_EVENT_CLOSE Then
GUIDelete($gui_var)
ExitLoop
EndIf

[/autoit] [autoit][/autoit] [autoit]

WEnd

[/autoit] [autoit][/autoit] [autoit]

EndFunc

[/autoit] [autoit][/autoit] [autoit]

#Region Textdatei
#CS
Func _File_01()

[/autoit] [autoit][/autoit] [autoit]

$DATAC2 = ""
$sString = ""
$file = (@ScriptDir & "\TB.txt")

[/autoit] [autoit][/autoit] [autoit]

If Not _FileReadToArray($file,$aRecords) Then
MsgBox(4096,"Error", " Error reading log to Array error:" & @error)
Exit
EndIf

[/autoit] [autoit][/autoit] [autoit]

For $x = 1 to $aRecords[0]
;Msgbox(0,'Record:' & $x, $aRecords[$x])
$sString &= $aRecords[$x]
Next
;MsgBox(0,"", $sString)
$DATAC2 = $sString

[/autoit] [autoit][/autoit] [autoit]

EndFunc
#CE
#EndRegion Textdatei

[/autoit] [autoit][/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

;------access DB auslesen -------------
Func _ReadOneFld($_sql, $_dbname, $_field)
Dim $_output
$adoCon = ObjCreate("ADODB.Connection")
$adoCon.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $_dbname)
$adoRs = ObjCreate("ADODB.Recordset")
$adoRs.CursorType = 1
$adoRs.LockType = 3
$adoRs.Open($_sql, $adoCon)

[/autoit] [autoit][/autoit] [autoit]

With $adoRs
If .RecordCount Then
While Not .EOF
$_output = $_output & .Fields($_field).Value & @CRLF
.MoveNext
WEnd
EndIf
EndWith
$adoCon.Close
Return $_output
EndFunc ;==>_ReadOneFld

[/autoit] [autoit][/autoit] [autoit]

Func _ReadOneFld_Combo($_sql, $_dbname, $_field)
Dim $_output
$adoCon = ObjCreate("ADODB.Connection")
$adoCon.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $_dbname)
$adoRs = ObjCreate("ADODB.Recordset")
$adoRs.CursorType = 1
$adoRs.LockType = 3
$adoRs.Open($_sql, $adoCon)

[/autoit] [autoit][/autoit] [autoit]

With $adoRs
If .RecordCount Then
While Not .EOF
$_output = $_output & .Fields($_field).Value & "|" ;@CRLF
.MoveNext
WEnd
EndIf
EndWith
$adoCon.Close
Return $_output
EndFunc ;==>_ReadOneFld

[/autoit]

[autoit]

#include <Excel.au3>
#Include <File.au3>
;#include <AD.au3>
#include <D:\Data\scripte\AD_1.1.0_Functions\AD.au3>
#include <Array.au3>
#include <GUIConstantsEx.au3>
#Include <Date.au3>

[/autoit] [autoit][/autoit] [autoit]

; excel book erstellen
; user nach sites eintragen
; user nach company eintragen
; user gesamt nach sites eintragen

[/autoit] [autoit][/autoit] [autoit]

DIM $oMyRet, $oMyError, $oExcel, $saveas, $sOU, $ld
Local $avArray[1] ,$avArray_01[1], $alogin[1], $aObjects , $aObjects_01,$aObjects_02, $avArray_10[1],$avArray_11[1], $sValue

[/autoit] [autoit][/autoit] [autoit]

$longonserver = StringReplace(@LogonServer,"\\","")

[/autoit] [autoit][/autoit] [autoit]

_AD_Open()

[/autoit] [autoit][/autoit] [autoit]

;------list dc's----
Local $aDC = _AD_ListDomainControllers()
_ArraySort($aDC)
;_ArrayDisplay($aDC, "Active Directory Functions - Example 1 - All Domain Controllers, distinguished name, DNS host name, and the site name")

[/autoit] [autoit][/autoit] [autoit]

;auslesen der einzelnen OU's
$aOUs = _AD_GetObjectsInOU($sOU, "(objectCategory=organizationalUnit)", 1, "name") ;"distinguishedName")
If @error > 0 Then
MsgBox(64, "Active Directory Functions - Example 2", "No OUs could be found")
Else
;_ArrayDisplay($aOUs, "Active Directory Functions - Example 2 - All OUs starting with: '" & $sOU & "'")
EndIf

[/autoit] [autoit][/autoit] [autoit]

;---------------------------------
;_ArrayDelete($aOUs, 0) ;löscht 1 eintrag aus dem array
_ArraySort($aOUs)
;_ArrayDisplay($aOUs, "AD all Site OUs!")

[/autoit] [autoit][/autoit] [autoit]

$site = InputBox("Site","Input - AD Site Name" & @CRLF & @CRLF & "e.g. xxx01 or xxx02...")
IF @error = 1 Then Exit

[/autoit] [autoit][/autoit] [autoit]

;erstelle excel dokument
Local $oExcel = _ExcelBookNew() ;Create new book, make it visible
_ExcelSheetDelete($oExcel, "Sheet1") ;Delete Sheet by string name of SheetName
_ExcelSheetDelete($oExcel, "Sheet2") ;Delete Sheet by string name of SheetName
_ExcelSheetDelete($oExcel, "Sheet3") ;Delete Sheet by string name of SheetName
_ExcelSheetDelete($oExcel, "Tabelle1") ;Delete Sheet by string name of SheetName
_ExcelSheetDelete($oExcel, "Tabelle2") ;Delete Sheet by string name of SheetName
;-----------------------
Sleep(1000)

[/autoit] [autoit][/autoit] [autoit]

$x = 1
$aOUs[$x] = StringUpper($site)

[/autoit] [autoit][/autoit] [autoit]

;For $x = 1 to $aOUs[0]
$ld = ""
$aObjects = _AD_GetObjectsInOU("OU=User,OU="&$aOUs[$x]&",DC=xx,DC=int", "(&(objectcategory=person)(objectclass=user)(mail=*)(|(userAccountControl=512)(msExchHideFromAddressLists="")(msExchHideFromAddressLists=FALSE)(userAccountControl=66048)))", 2, "name,department,company")
$aObjects_01 = $aObjects
$aObjects_02 = $aObjects
$aObjects_03 = $aObjects
_ArrayDisplay($aObjects_01)

[/autoit] [autoit][/autoit] [autoit]

If @error > 0 Then
;MsgBox(64, $aOUs[$x], "Nichts gefunden Error: " & @error)
Else
;$bench = TimerInit() ; laufzeittest
;--user pro company in site zählen-----
$Array_05 = _GetUsers_Summary1($aObjects_01) ;dieses Array enthält die Ergebnisse
$Array_06 = _GetUsers_Summary2($aObjects_02) ;dieses Array enthält die Ergebnisse
$Array_07 = _GetUsers_Summary3($aObjects_03) ;dieses Array enthält die Ergebnisse
If Not @error Then _ArrayDisplay($Array_05)
;If Not @error Then _ArrayDisplay($Array_06)
;If Not @error Then _ArrayDisplay($Array_07)
;If Not @error Then _ArrayDisplay($avArray_10)
;ConsoleWrite(Round(TimerDiff($bench) / 1000, 4) & " s" & @CRLF) ; laufzeittest ausgabe
;--------------------------------------
_ExcelSheetAddNew($oExcel, $aOUs[$x])
_ExcelWriteCell($oExcel, "User", 1, 1) ;Write to the Cell
_ExcelWriteCell($oExcel, "Department", 1, 2) ;Write to the Cell
_ExcelWriteCell($oExcel, "Company", 1, 3) ;Write to the Cell
;Zusätzliche Spalten
_ExcelWriteCell($oExcel, "Exist Yes", 1, 4) ;Write to the Cell
_ExcelWriteCell($oExcel, "Exist No", 1, 5) ;Write to the Cell
_ExcelWriteCell($oExcel, "Comments", 1, 6) ;Write to the Cell
$oExcel.Columns("A:A").ColumnWidth = "30" ;setze spaltenbreite
$oExcel.Columns("B:B").ColumnWidth = "50" ;setze spaltenbreite
$oExcel.Columns("C:C").ColumnWidth = "25" ;setze spaltenbreite
;---------------------------------------------------------------
$oExcel.Columns("D:D").ColumnWidth = "10" ;setze spaltenbreite
$oExcel.Columns("E:E").ColumnWidth = "10" ;setze spaltenbreite
$oExcel.Columns("F:F").ColumnWidth = "13" ;setze spaltenbreite
;---------------------------------------------------------------
$oExcel.Range("A1:F1") .Font.Bold = TRUE ; setze schriftgrad
$oExcel.Range("A1:F1") .Font.Size = 14 ; setze schriftgröße
;$oExcel.Range("A1:D1") .Font.Color = 0xffffff
$oExcel.Range("A1:F1") .Interior.ColorIndex = 43 ; setze Farbe
;$oExcel.Name = $aOUs[$x]

[/autoit] [autoit][/autoit] [autoit]

For $i = 1 To $aObjects[0][0]
;MsgBox(0,"", $aObjects[$i][0])
$zeile = $i + 1
IF $aObjects[0][0] <> "" Then
;MsgBox(0,"", $aObjects[$i][0])
_ExcelWriteCell($oExcel, $aObjects[$i][0], $zeile, 1)
_ExcelWriteCell($oExcel, $aObjects[$i][1], $zeile, 2)
_ExcelWriteCell($oExcel, $aObjects[$i][2], $zeile, 3)
$oExcel.Range("A"& $zeile &":F"& $zeile) .Interior.ColorIndex = 27 ; setze Farbe gelb
$oExcel.Range("A1:F"&$zeile).Borders.LineStyle = 1 ; setze einen Rahmen um die Zellen
EndIf
Next
;_ArrayAdd($avArray, $aOUs[$x] & ";" & $aObjects[0][0] - $ld) ; füge in array für Excelsheet "ZGesamt" ein
_ArrayAdd($avArray, $aOUs[$x] & ";" & $aObjects[0][0] & ";" & $ld & ";" & $aObjects[0][0] - $ld) ; füge in array für Excelsheet "ZGesamt" ein
;_ArrayDisplay($avArray)
_ExcelWriteCell($oExcel, $aObjects[0][0], $zeile + 2, 2)
_ExcelWriteCell($oExcel, "All USER " & $aOUs[$x] & ":", $zeile + 2, 1)

[/autoit] [autoit][/autoit] [autoit]

$oExcel.Range("A"& $zeile +2 &":B"& $zeile+2) .Interior.ColorIndex = 27 ; setze Farbe
$oExcel.Range("A1:B"&$zeile +2).Borders.LineStyle = 1 ; setze einen Rahmen um die Zellen
$oExcel.Range("A"& $zeile +2 &":B"& $zeile+2) .Font.Bold = TRUE ; setze schriftgrad

[/autoit] [autoit][/autoit] [autoit]

#Region ----Excel_Sheet_Summary ----
;-------schreibe user pro company in excelsheet = sheet summary-------
$yearsheet = "Summary" & _NowDate()
_ExcelSheetAddNew($oExcel, $yearsheet) ; erstelle neues sheet "jahresdatum"
$oExcel.Range("A1:B1").Select ; cell selection
$oExcel.Selection.Merge ; merge cell selection
$oExcel.Selection.HorizontalAlignment = -4108 ; setze schrift in zelle zentriert
$zeile = 1
_ExcelWriteCell($oExcel, "Active User Validation " & $aOUs[$x], $zeile, 1)
$oExcel.Range("A1:A1").Interior.ColorIndex = 36 ; setze Farbe hellgelb
$oExcel.Range("A1:A1").Font.Bold = TRUE ; setze schriftgrad
$oExcel.Range("A1:A1").Font.Size = 12 ; setze schriftgröße
;------------------
_ExcelWriteCell($oExcel, "Total User " & $aOUs[$x] & ":", $zeile + 2, 1)
_ExcelWriteCell($oExcel, $aObjects[0][0], $zeile + 2, 2)
$oExcel.Range("A" & $zeile + 2 & ":B" & $zeile + 2).Interior.ColorIndex = 36 ; setze Farbe hellgelb
$oExcel.Range("A" & $zeile + 2 & ":B" & $zeile + 2).Font.Bold = TRUE ; setze schriftgrad
;$oExcel.Range("A" & $zeile + 2 & ":B" & $zeile + 2).Font.Size = 12 ; setze schriftgröße
;--------------------------------------------------------
For $i = 0 To UBound($Array_05) - 1
;MsgBox(0,"", $Array_05[$i][0] & " " &$Array_05[$i][1])
IF $Array_05[$i][0] = "" Then
;MsgBox(0,"",$Array_05[$i][1])
_ExcelWriteCell($oExcel,"No Department", $zeile + 4 + $i, 1)
Else
_ExcelWriteCell($oExcel, $Array_05[$i][0], $zeile + 4 + $i, 1)
EndIf
_ExcelWriteCell($oExcel, $Array_05[$i][1], $zeile + 4 + $i, 2)
;$oExcel.Range("A"& $zeile + 4 + $i &":B"& $zeile + 4 + $i) .Interior.ColorIndex = 27 ; setze Farbe gelb
$oExcel.Range("A1:B"&$zeile + 4 + $i).Borders.LineStyle = 1 ; setze einen Rahmen um die Zellen
$oExcel.Range("A"& $zeile + 4 +$i &":B"& $zeile + 4 +$i) .Font.Bold = TRUE ; setze schriftgrad
$oExcel.Columns("A:A").ColumnWidth = "50" ;setze spaltenbreite
IF $aObjects[0][0] <> "" Then _ArrayAdd($avArray_01, $aOUs[$x] & ";" & $Array_05[$i][0] & ";" & $Array_05[$i][1]) ; füge daten in array für YGesamt
Next
$zeile = $zeile + 4 + $i

[/autoit] [autoit][/autoit] [autoit]

If $aObjects <> "" Then _ArrayAdd($avArray_01, ";;")
;-------------------------------------------------------
;MsgBox(0,"",$zeile)
;--------companie zeilen schreiben ----------
For $i = 0 To UBound($Array_07) -1
IF $Array_07[$i][0] = "" Then
;MsgBox(0,"","test")
_ExcelWriteCell($oExcel,"No Companie", $zeile + 2 + $i, 1)
_ExcelWriteCell($oExcel, $Array_07[$i][1], $zeile + 2 + $i, 2)
$oExcel.Range("A"& $zeile + 2 + $i &":B"& $zeile + 2 + $i) .Interior.ColorIndex = 36 ; setze Farbe gelb
Else
_ExcelWriteCell($oExcel, $Array_07[$i][0], $zeile + 2 + $i, 1)
_ExcelWriteCell($oExcel, $Array_07[$i][1], $zeile + 2 + $i, 2)
$oExcel.Range("A"& $zeile + 2 + $i &":B"& $zeile + 2 + $i) .Interior.ColorIndex = 36 ; setze Farbe gelb
EndIf
;_ExcelWriteCell($oExcel, $Array_07[$i][0], $zeile + 2 + $i, 1)
;_ExcelWriteCell($oExcel, $Array_07[$i][1], $zeile + 2 + $i, 2)
;$oExcel.Range("A"& $zeile + 4 + $i &":B"& $zeile + 4 + $i) .Interior.ColorIndex = 27 ; setze Farbe gelb
$oExcel.Range("A1:B"&$zeile + 2 + $i).Borders.LineStyle = 1 ; setze einen Rahmen um die Zellen
$oExcel.Range("A"& $zeile + 2 +$i &":B"& $zeile + 2 +$i) .Font.Bold = TRUE ; setze schriftgrad
$oExcel.Columns("A:A").ColumnWidth = "50" ;setze spaltenbreite
Next
#EndRegion ----Excel_Sheet_Summary----
EndIf
;Next

[/autoit] [autoit][/autoit] [autoit]

_AD_Close()

[/autoit] [autoit][/autoit] [autoit]

;_ExcelSort($oExcel, "A2","A1",1,1,1,1) ; to sort just the A column A->Z

[/autoit] [autoit][/autoit] [autoit]

_ExcelSheetDelete($oExcel, "Tabelle3") ;Delete Sheet by string name of SheetName
_ExcelSheetAddNew($oExcel, "YGesamt")
_ExcelSheetAddNew($oExcel, "ZGesamt")
_ExcelSheetOrderByName($oExcel, 0)

[/autoit] [autoit][/autoit] [autoit]

#Region ZGesamt
;---------ZGesamt-------------------------
_ExcelWriteCell($oExcel, "Site", 1, 1) ;Write to the Cell
_ExcelWriteCell($oExcel, "User", 1, 2) ;Write to the Cell
;_ExcelWriteCell($oExcel, "User inaktiv", 1, 3) ;Write to the Cell
;_ExcelWriteCell($oExcel, "Anzahl bereinigt", 1, 4) ;Write to the Cell
_ExcelSheetActivate($oExcel, "ZGesamt")
$oExcel.Columns("A:A").ColumnWidth = "30" ;setze spaltenbreite
$oExcel.Columns("B:B").ColumnWidth = "30" ;setze spaltenbreite
;$oExcel.Columns("C:C").ColumnWidth = "30" ;setze spaltenbreite
;$oExcel.Columns("D:D").ColumnWidth = "30" ;setze spaltenbreite
$oExcel.Range("A1:B1") .Font.Bold = TRUE ; setze schriftgrad
$oExcel.Range("A1:B1") .Font.Size = 14 ; setze schriftgröße
;$oExcel.Range("A1:D1") .Font.Color = 0xffffff
$oExcel.Range("A1:B1") .Interior.ColorIndex = 43 ; setze Farbe
;$oExcel.Name = $aOUs[$x]

[/autoit] [autoit][/autoit] [autoit]

For $i = 1 To _ArrayMaxIndex($avArray, 0, 1)
;MsgBox(0,"", $avArray[$i])
$zeile_1 = $i + 1
$eintrag = StringSplit($avArray[$i], ";")
_ExcelWriteCell($oExcel, $eintrag[1], $zeile_1, 1)
_ExcelWriteCell($oExcel, $eintrag[2], $zeile_1, 2)
;_ExcelWriteCell($oExcel, $eintrag[3], $zeile_1, 3)
;_ExcelWriteCell($oExcel, $eintrag[4], $zeile_1, 4)
$oExcel.Range("A"& $zeile_1 &":B"& $zeile_1) .Interior.ColorIndex = 27 ; setze Farbe
$oExcel.Range("A1:B"&$zeile_1).Borders.LineStyle = 1 ; setze einen Rahmen um die Zellen
Next

[/autoit] [autoit][/autoit] [autoit]

_ExcelWriteCell($oExcel, "=Summe(B2:B"& $zeile_1 ,$zeile_1 + 1 , 2) ;Write formula another way Uses A1 referencing, not R1C1
;_ExcelWriteCell($oExcel, "=Summe(C2:C"& $zeile_1 ,$zeile_1 + 1 , 2) ;Write formula another way Uses A1 referencing, not R1C1
;_ExcelWriteCell($oExcel, "=Summe(D2:D"& $zeile_1 ,$zeile_1 + 1 , 2) ;Write formula another way Uses A1 referencing, not R1C1
_ExcelWriteCell($oExcel, "All User",$zeile_1 + 1 , 1) ;Write formula another way Uses A1 referencing, not R1C1
; formatiert letzte Zeile
$oExcel.Range("A"& $zeile_1 +1 &":B"& $zeile_1 + 1) .Interior.ColorIndex = 27 ; setze Farbe
$oExcel.Range("A"& $zeile_1 +1 &":B"& $zeile_1 + 1).Borders.LineStyle = 1 ; setze einen Rahmen um die Zellen
$oExcel.Range("A"& $zeile_1 +1 &":B"& $zeile_1 + 1) .Font.Bold = TRUE ; setze schriftgrad

[/autoit] [autoit][/autoit] [autoit]

;---------Ende ZGesamt---------------------
#EndRegion ZGesamt
#Region YGesamt
;---------YGesamt--------------------------
_ExcelSheetActivate($oExcel, "YGesamt")
_ExcelWriteCell($oExcel, "Site", 1, 1) ;Write to the Cell
_ExcelWriteCell($oExcel, "Departments", 1, 2) ;Write to the Cell
;_ExcelWriteCell($oExcel, "User", 1, 3) ;Write to the Cell
_ExcelSheetActivate($oExcel, "YGesamt")
$oExcel.Columns("A:A").ColumnWidth = "25" ;setze spaltenbreite
$oExcel.Columns("B:B").ColumnWidth = "30" ;setze spaltenbreite
;$oExcel.Columns("C:C").ColumnWidth = "20" ;setze spaltenbreite
$oExcel.Range("A1:B1") .Font.Bold = TRUE ; setze schriftgrad
$oExcel.Range("A1:B1") .Font.Size = 14 ; setze schriftgröße
;$oExcel.Range("A1:D1") .Font.Color = 0xffffff
$oExcel.Range("A1:B1") .Interior.ColorIndex = 43 ; setze Farbe
;$oExcel.Name = $aOUs[$x]

[/autoit] [autoit][/autoit] [autoit]

For $i = 1 To _ArrayMaxIndex($avArray_10, 0, 1)
;IF $aObjects[0][0] <> "" Then
$zeile_1 = $i + 1
$eintrag = StringSplit($avArray_10[$i], ";")
_ExcelWriteCell($oExcel, $eintrag[1], $zeile_1, 1)
_ExcelWriteCell($oExcel, $eintrag[2], $zeile_1, 2)
;_ExcelWriteCell($oExcel, $eintrag[3], $zeile_1, 3)
If $eintrag[1] = "" Then
$oExcel.Range("A"& $zeile_1 &":B"& $zeile_1).Interior.ColorIndex = 43 ; setze Farbe
_ExcelWriteCell($oExcel, $eintrag[2], $zeile_1, 3)
Else
$oExcel.Range("A"& $zeile_1 &":B"& $zeile_1).Interior.ColorIndex = 27 ; setze Farbe
EndIf
$oExcel.Range("A1:B"&$zeile_1).Borders.LineStyle = 1 ; setze einen Rahmen um die Zellen

[/autoit] [autoit][/autoit] [autoit]

Next
;---------Ende YGesamt---------------------
#EndRegion YGesamt

[/autoit] [autoit][/autoit] [autoit]

_ExcelSheetActivate($oExcel,$yearsheet)
;_ArrayDisplay($alogin)

[/autoit] [autoit][/autoit] [autoit]

;$saveas = "C:\User_in Sites"
;_ExcelBookSaveAs($oExcel, $saveas, "xls")
;_ExcelBookClose($oExcel, 1, 0)

[/autoit] [autoit][/autoit] [autoit]

#Region Funtions
;===============================================================================
; Function Name: _ExcelSheetOrderByName($oExcel, $iDesc=0)
; Description:: Order ExcelBook-Sheets by name ascending (default) or descending
; Parameter(s): $oExcel - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
; $iDesc - order descending on=1/off=0 (default)
; Requirement(s): #Include <Array.au3>
; Return Value(s): On Success - Returns 1
; On Failure - Returns 0 and sets @error=1 - Specified object does not exist
; Author(s): BugFix ([email='bugfix@autoit.de'][/email])
;===============================================================================
Func _ExcelSheetOrderByName($oExcel, $iDesc=0)
If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
If $iDesc <> 1 Then $iDesc = 0
Local $aList = _ExcelSheetList($oExcel)
_ArraySort($aList, $iDesc)
Local $pos = 1
For $i = 0 To UBound($aList) -1
If $i = 0 Then
_ExcelSheetMove($oExcel, $aList[$i], $pos, True)
Else
_ExcelSheetMove($oExcel, $aList[$i], $pos, False)
$pos += 1
EndIf
Next
Return 1
EndFunc

[/autoit] [autoit][/autoit] [autoit]

Func _GetUsers_Summary1(ByRef $aObjects_01)
If Not IsArray($aObjects_01) Then SetError(1, 0, 0)
_ArrayDelete($aObjects_01, 0)
;_ArrayDisplay($aObjects_01)
If Not IsArray($aObjects_01) Then SetError(2, 0, 0)
Local $aCompanies = _ArrayUnique($aObjects_01, 2) ; prüfe feld xy
_ArrayDelete($aCompanies, 0)
_ArraySort($aCompanies)
_ArrayDisplay($aCompanies)
Local $aResult[UBound($aCompanies)][2]
For $i = 0 To UBound($aCompanies) - 1
$aResult[$i][0] = $aCompanies[$i]
Next

[/autoit] [autoit][/autoit] [autoit]

For $i = 0 To UBound($aResult) - 1
;$aTemp = _ArrayFindAll($aObjects_01, $aResult[$i][0], 0, 0, 0, 0, 2)
$aTemp = _ArrayFindAll($aObjects_01, $aResult[$i][0], 0, 0, 0, 0, 1)
$aResult[$i][1] = UBound($aTemp)
Next
Return $aResult
EndFunc ;==>_GetUsers_Summary 1

[/autoit] [autoit][/autoit] [autoit]

Func _GetUsers_Summary2(ByRef $aObjects_02) ; Companies
If Not IsArray($aObjects_02) Then SetError(1, 0, 0)
_ArrayDelete($aObjects_02, 0)
;_ArrayDisplay($aObjects_02)
If Not IsArray($aObjects_02) Then SetError(2, 0, 0)
Local $aCompanies = _ArrayUnique($aObjects_02, 2) ; prüfe feld xy
_ArrayDelete($aCompanies, 0)
_ArraySort($aCompanies)
;_ArrayDisplay($aCompanies)

[/autoit] [autoit][/autoit] [autoit]

For $i = 0 To UBound($aCompanies) - 1
_ArrayAdd($avArray_10,$aOUs[$x] & ";" & $aCompanies[$i]) ;erstelle array "$avArray_10"
Next
If $aObjects_02 <> "" Then _ArrayAdd($avArray_10, ";;")
EndFunc ;==>_GetUsers_Summary 2

[/autoit] [autoit][/autoit] [autoit]

Func _GetUsers_Summary3(ByRef $aObjects_03) ;Departments zählen
If Not IsArray($aObjects_03) Then SetError(1, 0, 0)
_ArrayDelete($aObjects_03, 0)
;_ArrayDisplay($aObjects_03)
If Not IsArray($aObjects_03) Then SetError(2, 0, 0)
Local $aDepartments = _ArrayUnique($aObjects_03, 3) ; prüfe feld xy
_ArrayDelete($aDepartments, 0)
_ArraySort($aDepartments)
;_ArrayDisplay($aDepartments)

[/autoit] [autoit][/autoit] [autoit]

Local $aResult1[UBound($aDepartments)][2]
For $i = 0 To UBound($aDepartments) - 1
$aResult1[$i][0] = $aDepartments[$i]
Next

[/autoit] [autoit][/autoit] [autoit]

For $i = 0 To UBound($aResult1) - 1
;$aTemp1 = _ArrayFindAll($aObjects_01, $aResult[$i][0], 0, 0, 0, 0, 2)
$aTemp1 = _ArrayFindAll($aObjects_03, $aResult1[$i][0], 0, 0, 0, 0, 2)
$aResult1[$i][1] = UBound($aTemp1)
Next
Return $aResult1
EndFunc ;==>_GetUsers_Summary 3

[/autoit] [autoit][/autoit] [autoit]

;_ExcelSort($oExcel, "A1","A1",1,1,1,1) ; to sort just the A column A->Z

[/autoit] [autoit][/autoit] [autoit]

;===============================================================================
;
; Description: Performs a simplified sort on a range.
; Syntax: _ExcelSort($oExcel, $sKey, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iDirection = 2)
; Parameter(s): $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
; $sKey - The key column or row to sort by (a letter for column, a number for row)
; $sRangeOrRowStart - Either an A1 range, or an integer row number to start from if using R1C1
; $iColStart - The starting column for the number format(left) (default=1)
; $iRowEnd - The ending row for the number format (bottom) (default=1)
; $iColEnd - The ending column for the number format (right) (default=1)
; $iDirection - Sort direction (1=Ascending, 2=Descending) (default=descending)
; Requirement(s): None
; Return Value(s): On Success - Returns 1
; On Failure - Returns 0 and sets @error on errors:
; @error=1 - Specified object does not exist
; @error=2 - Starting row or column invalid
; @extended=0 - Starting row invalid
; @extended=1 - Starting column invalid
; @error=3 - Ending row or column invalid
; @extended=0 - Ending row invalid
; @extended=1 - Ending column invalid
; Author(s): SEO <locodarwin at yahoo dot com>, many thanks to DaLiMan
; Note(s): This sort routine will not function properly with pivot tables. Please
; use the pivot table sorting functions instead.
;
;===============================================================================
Func _ExcelSort($oExcel, $sKey, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iDirection = 2)
If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
If NOT StringRegExp($sRangeOrRowStart, "[A-Z,a-z]", 0) Then
If $sRangeOrRowStart < 1 Then Return SetError(2, 0, 0)
If $iColStart < 1 Then Return SetError(2, 1, 0)
If $iRowEnd < $sRangeOrRowStart Then Return SetError(3, 0, 0)
If $iColEnd < $iColStart Then Return SetError(3, 1, 0)
$oExcel.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Sort _
($oExcel.Range($sKey), $iDirection)
Else
$oExcel.Range($sRangeOrRowStart).Sort ($oExcel.Range($sKey), $iDirection)
EndIf
Return 1
EndFunc ;==>_ExcelSort
#EndRegion Funtions

[/autoit]

[autoit]

#include <_SQL.au3>
#include <array.au3>

[/autoit] [autoit][/autoit] [autoit]

Opt ("trayIconDebug",1)

[/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

$IP = "ipadrese-servername"
$User = "user"
$Pass = "passwd"
$DB = "testdb"

[/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

Msgbox(0,"","Start the Script and load the error handler")
_SQL_RegisterErrorHandler();register the error handler to prevent hard crash on COM error

[/autoit] [autoit][/autoit] [autoit]

$oADODB = _SQL_Startup()
If $oADODB = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())

[/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

If _SQL_Connect(-1,$IP,"",$User,$Pass) = $SQL_ERROR then
Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())
_SQL_Close()
Exit
EndIf

[/autoit] [autoit][/autoit] [autoit]

If _SQL_Execute(-1,"Create database My_SQL_Test;") = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())
_SQL_Close()

[/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

Msgbox(0,"","Created datatbase logging out and back in again")
$oADODB = _SQL_Startup()

[/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

If _SQL_Connect(-1,$IP,$DB,$User,$Pass) = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())
If _SQL_Execute(-1, "CREATE TABLE BBKS (ID INT NOT NULL IDENTITY(1,1),ComputerName VARCHAR(20) UNIQUE,Status VARCHAR(10),Error VARCHAR(10)Primary Key (ID));") = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())
If _SQL_Execute(-1,"INSERT INTO BBKS (ComputerName,Status,Error) VALUES ('"& @computername & "','On;li''ne','None');") = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())
If _SQL_Execute(-1,"INSERT INTO BBKS (ComputerName,Status,Error) VALUES ('1"& @computername & "','On;li''ne','None');") = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())
If _SQL_Execute(-1,"INSERT INTO BBKS (ComputerName,Status,Error) VALUES ('2"& @computername & "','Online','None');") = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())

[/autoit] [autoit][/autoit] [autoit]

; this one will cause an error because the computername is not unique!
If _SQL_Execute(-1,"INSERT INTO BBKS (ComputerName,Status,Error) VALUES ('2"& @computername & "','Online','None');") = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error","Example Error this was meant to happen!" & @crlf & @crlf & _SQL_GETErrMsg())

[/autoit] [autoit][/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

Msgbox(0,"","Created table and added data so lets get some data out first as a 2dArray")

[/autoit] [autoit][/autoit] [autoit]

Local $aData,$iRows,$iColumns;Variables to store the array data in to and the row count and the column count
$iRval = _SQL_GetTable2D(-1,"SELECT * FROM BBKS;",$aData,$iRows,$iColumns)
If $iRval = $SQL_OK then _arrayDisplay($aData,"2D (" & $iRows & " Rows) (" & $iColumns & " Columns)" )

[/autoit] [autoit][/autoit] [autoit][/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

Msgbox(0,"","Next as a 1dArray")

[/autoit] [autoit][/autoit] [autoit]

Local $aData,$iRows,$iColumns;Variables to store the array data in to and the row count and the column count
$iRval = _SQL_GetTable(-1,"SELECT * FROM BBKS;",$aData,$iRows,$iColumns)
If $iRval = $SQL_OK then _arrayDisplay($aData,"1D (" & $iRows & " Rows) (" & $iColumns & " Columns)" )

[/autoit] [autoit][/autoit] [autoit][/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

Msgbox(0,"","And now the same data returned 1 row at a time")

[/autoit] [autoit][/autoit] [autoit]

$hData = _SQL_Execute(-1,"SELECT * FROM BBKS;")

[/autoit] [autoit][/autoit] [autoit]

Local $aNames;Variable to store the array data in to
$iRval = _SQL_FetchNames ($hData, $aNames); Read out Column Names
If $iRval = $SQL_OK then ConsoleWrite(StringFormat(" %-10s %-10s %-10s %-10s ", $aNames[0], $aNames[1], $aNames[2], $aNames[3]) & @CR)
_ArrayDisplay($aNames,"Column Names")

[/autoit] [autoit][/autoit] [autoit]

Local $aRow;Variable to store the array data in to
While _SQL_FetchData ($hData, $aRow) = $SQL_OK; Read Out the next Row
ConsoleWrite(StringFormat(" %-10s %-10s %-10s %-10s ", $aRow[0], $aRow[1], $aRow[2], $aRow[3]) & @CR)
_ArrayDisplay($aRow,"Single Row of Data")
WEnd

[/autoit] [autoit][/autoit] [autoit][/autoit] [autoit][/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

Msgbox(0,"","And now the same data returned as a string")

[/autoit] [autoit][/autoit] [autoit]

Local $vString
If _SQL_GetTableAsString(-1,"SELECT * FROM BBKS;",$vString) = $SQL_OK then
Msgbox(0,"Data as a String",$vString)
Else
Msgbox(0 + 16 +262144,"SQL Error",_SQL_GetErrMsg() )
EndIf

[/autoit] [autoit][/autoit] [autoit][/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

Msgbox(0,"","Now just a single row")

[/autoit] [autoit][/autoit] [autoit]

Local $aRow;Variable to store the row array data in
$iRval = _SQL_QuerySingleRow(-1,"SELECT * FROM BBKS;",$aRow)
If $iRval = $SQL_OK then _arrayDisplay($aRow,"1 Row" )

[/autoit] [autoit][/autoit] [autoit][/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

Msgbox(0,"","Now drop the tables and the database")
If _SQL_Execute(-1, "DROP TABLE BBKS;") = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())

[/autoit] [autoit][/autoit] [autoit]

If _SQL_Close() <> $SQL_OK then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg() )

[/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

;Just being lazy and not putting any error checking in now!
$oADODB = _SQL_Startup()
_SQL_Connect(-1,$IP,$DB,$User,$Pass)
_SQL_Execute(-1,"DROP database My_SQL_Test;")
_SQL_Close()

[/autoit] [autoit][/autoit] [autoit]

Msgbox(0,"","Example Finished")

[/autoit]

[autoit]

Local $sQuery = "UPDATE tab_vorgang SET `UMSATZ isoliert EUR` = '" & _MySQL_Real_Escape_String($MysqlConn,$s_Row_txt_t1[2]) & _
"',`Stornodatum` = '" & _MySQL_Real_Escape_String($MysqlConn,$s_Row_txt_t1[4]) & _
"' WHERE `Bu-Nr` = '" & _MySQL_Real_Escape_String($MysqlConn,$s_Row_txt_t1[1]) & "'"

[/autoit] [autoit][/autoit] [autoit]

If _MySQL_Real_Query($MysqlConn, $sQuery) Then ; Fehler
MsgBox(0,"","Fehlernummer: " & _MySQL_Errno($MySQLConn) & @CRLF & _MySQL_Error($MysqlConn))
EndIf
If @error <> 0 Then MsgBox(0,"",$x & " / " & $sQuery)

[/autoit]

[autoit]

Local $sQuery = "INSERT INTO tab_vorgang (`Bu-Nr`,`UMSATZ isoliert EUR`,`DATUM`,`Stornodatum`) " & _
"VALUES ('" & $s_Row_txt_t1[1] & "',"& _
"'" & $s_Row_txt_t1[2] & "',"& _
"'" & $s_Row_txt_t1[3] & "',"& _
"'" & $s_Row_txt_t1[4] & "')"& _
" ON DUPLICATE KEY UPDATE "& _
"`UMSATZ isoliert EUR` = (" & "'" & $s_Row_txt_t1[2] & "'" & ") ," & _
"`DATUM` = (" & "'" & $s_Row_txt_t1[3] & "'" & ")," & _
"`Stornodatum` = (" & "'" & $s_Row_txt_t1[4] & "'" & ")"

[/autoit] [autoit][/autoit] [autoit]

If _MySQL_Real_Query($MysqlConn, $sQuery) Then ; Fehler
MsgBox(0,"","Fehlernummer: " & _MySQL_Errno($MySQLConn) & @CRLF & _MySQL_Error($MysqlConn))
EndIf
If @error <> 0 Then MsgBox(0,"",$x & " / " & $sQuery)

[/autoit]

[autoit]

;Functions
Func _OOSetProp($cName, $uValue) ;Eigenschaften in struct übergeben
$errormodul = "_OOSetProp"
$osm = ObjCreate("com.sun.star.ServiceManager")
$oPropertyValue = $osm.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
$oPropertyValue.Name = $cName
$oPropertyValue.Value = $uValue
$setOOoProp = $oPropertyValue
Return $setOOoProp
EndFunc ;==>_OOSetProp

[/autoit] [autoit][/autoit] [autoit]

Func _OOInit($pass = "", $readonly = False, $hidden = False) ;verbindung zu OO herstellen
$errormodul = "_OOINIT"
$osm = ObjCreate("com.sun.star.ServiceManager")
$oDesk = $osm.createInstance("com.sun.star.frame.Desktop")
$OpenPar[0] = _OOSetProp("ReadOnly", $readonly)
$OpenPar[1] = _OOsetProp("Password", $pass) ;setzt das passwort des dokuments
$OpenPar[2] = _OOsetProp("Hidden", $hidden)
EndFunc ;==>_OOInit

[/autoit] [autoit][/autoit] [autoit]

Func _OOAddNewBook() ; neue Tabellendatei erstellen
$errormodul = "_OOAddNewBook"
$odoc = $oDesk.loadComponentFromURL("private:factory/scalc", "_blank", 0, $OpenPar)
$oSheet = $odoc.CurrentController.ActiveSheet
EndFunc ;==>_OOAddNewBook

[/autoit] [autoit][/autoit] [autoit]

Func _OOOpenFile($fname) ; bestehende *.Datei öffnen, OO importiert mit Filter wenn möglich
$errormodul = "_OOOpenFile"
$fname = StringReplace($fname, ":", "|")
$fname = StringReplace($fname, " ", "%20")
$fname = "file:///" & StringReplace($fname, "\", "/")
$odoc = $oDesk.loadComponentFromURL($fname, "_blank", 0, $OpenPar)
EndFunc ;==>_OOOpenFile

[/autoit] [autoit][/autoit] [autoit]

Func _OOOpenBook($fname) ; bestehende Tabellendatei öffnen
$errormodul = "_OOOpenBook"
$fname = StringReplace($fname, ":", "|")
$fname = StringReplace($fname, " ", "%20")
$fname = "file:///" & StringReplace($fname, "\", "/")
$odoc = $oDesk.loadComponentFromURL($fname, "_blank", 0, $OpenPar)
$oSheet = $odoc.CurrentController.ActiveSheet ;auskommentieren, um alle importierbaren Formate zu öffnen
EndFunc ;==>_OOOpenBook

[/autoit] [autoit][/autoit] [autoit]

Func _OOStoreBook($fname, $filter = "StarOffice XML (Calc)") ;speichert Datei , angewandt wird der Dateifilter
;Alle EXPORT-Filter von dieser Page sind anwendbar, es gelten die internen Namen!
;http://wiki.services.openoffice.org/wiki/Framework…FilterList_SO_7
$errormodul = "_OOStoreBook"
$fname = StringReplace($fname, ":", "|")
$fname = StringReplace($fname, " ", "%20")
$fname = "file:///" & StringReplace($fname, "\", "/")
;MsgBox(0, "File saved", $filter)
$oSave = _ArrayCreate(_OOsetProp("FilterName", $filter))
$odoc.storetourl($fname, $oSave)
EndFunc ;==>_OOStoreBook

[/autoit] [autoit][/autoit] [autoit]

Func _OOSheetActivate($sheetnameornumber, $activeflag = 1) ;Tabellenname oder nummer zum schreiben/lesen aktivieren
;mit $activeflag=1 kommt die Tabelle in den Vordergrund
;mit $activeflag=0 wird die Tabelle verdeckt aktiviert z.B.um im Hintergrund Daten zu ändern
$errormodul = "_OOSheetactivate"
If IsString($sheetnameornumber) Then
$activesheet = $odoc.sheets.getbyname($sheetnameornumber)
Else
$activesheet = $odoc.sheets.getbyindex($sheetnameornumber) ;index starts with 0
EndIf

[/autoit] [autoit][/autoit] [autoit]

If $activeflag = 1 Then
$oSheet = $odoc.CurrentController.setActiveSheet($activesheet)
EndIf
$oSheet = $activesheet
EndFunc ;==>_OOSheetActivate

[/autoit] [autoit][/autoit] [autoit]

Func _OOBookAddNewSheet($sheetname) ;erstellt neues Tabellenblatt mit dem Namen $sheetname
$errormodul = "_OOAddNewsheet"
$eSheets = $odoc.getSheets.createEnumeration
$flag = 0
While $eSheets.hasMoreElements ;abfrage, ob sheet schon existiert
$oElement = $eSheets.nextElement()
If $oElement = $sheetname Then $flag = 1 ;wenn ja, merken
WEnd
If $flag = 0 Then
$Inst = $odoc.createInstance("com.sun.star.sheet.Spreadsheet")
$odoc.Sheets.insertByName($sheetname, $Inst)
EndIf
EndFunc ;==>_OOBookAddNewSheet

[/autoit] [autoit][/autoit] [autoit]

Func _OOBookDeleteSheet($sheetname) ;Delete other Sheets (by default Sheet2,Sheet3)
$errormodul = "_OODeleteSheet"
If $odoc.Sheets.hasByName($sheetname) Then $odoc.Sheets.removeByName($sheetname)
EndFunc ;==>_OOBookDeleteSheet

[/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

Func _OOSheetGetCellValue($col, $row) ;Inhalt der Zelle bsp; "A3" oder F22
$errormodul = "_OOSheetGetCellValue"
If IsString($col) Then
_OOAdress2Koord($col)
$col = $cellkoord[0]
$row = $cellkoord[1]
EndIf
$oCell = $oSheet.getCellByPosition($col, $row).value
Return $oCell
EndFunc ;==>_OOSheetGetCellValue

[/autoit] [autoit][/autoit] [autoit]

Func _OONumberOfSheets() ; Anzahl der Tabellenblätter
$errormodul = "_OONumberofsheets"
Return $odoc.getsheets.getcount
EndFunc ;==>_OONumberOfSheets

[/autoit] [autoit][/autoit] [autoit]

Func _OOAdress2Koord($cellname) ;wandelt "C1" in $cellkoord[0]=2 und $cellkoord[1]=0
$errormodul = "_OOAdress2Koord"
Local $textchar[3]
Local $numchar[3]
$cellname = StringUpper($cellname)
$numchar = StringRegExp($cellname, '\d+', 1) ;y-Koordinate der Zelle, findet Zahlen im Zellennamen;
; msgbox (0,$cellname,$textchar[0]&" "&$numchar[0])
$cellkoord[1] = $numchar[0] - 1
$textchar = StringRegExp($cellname, '[[:alpha:]]{0,2}', 1) ;findet A oder AA im Zellennamen
$x = (Asc(StringMid($textchar[0], 1, 1)) - 65) ;ascii erster Buchstabe
If StringLen($textchar[0]) = 2 Then
$x = (($x + 1) * 26) + (Asc(StringMid($textchar[0], 2, 1)) - 65)
EndIf
$cellkoord[0] = $x
Return $cellkoord
EndFunc ;==>_OOAdress2Koord

[/autoit] [autoit][/autoit] [autoit]

Func _OOKoordToAddress($row,$col) ;wandelt 2,3 in "C4"
$x = Int($col / 26)
$y = Mod($col, 26)

[/autoit] [autoit][/autoit] [autoit]

If $x = 0 Then
$char = Chr(64 + $y) ;A B C
Else
$char = Chr(64 + $x) & Chr(65 + $y) ; AA AB AC...
EndIf
Return $char & String($row + 1)
EndFunc ;==>_OOKoordToAddress

[/autoit] [autoit][/autoit] [autoit]

Func _OONameOfActiveSheet()
$errormodul = "_OONameofAvtiveSheet"
Return $oSheet.name
EndFunc ;==>_OONameOfActiveSheet

[/autoit] [autoit][/autoit] [autoit]

Func _OOSheetSetRowProperties($row, $height, $optheight = Default, $visible = Default, $newpage = Default) ;Eigenschaften der Zeile
; $optheight, $visible $newpage all OO_true or OO_False
$errormodul = "_OOSheetSetRowProperties"
$orow = $oSheet.getRows().getByIndex($row)
If IsNumber($height) Then $orow.Height = $height ;column height (in 100ths of mm)
$orow.OptimalWidth = Number($optheight)
$orow.IsVisible = Number($visible)
$orow.IsStartOfNewPage = Number($newpage)
EndFunc ;==>_OOSheetSetRowProperties

[/autoit] [autoit][/autoit] [autoit]

Func _OOSheetSetColProperties($col, $width, $optwidth = Default, $visible = Default, $newpage = Default) ;Eigenschaften Spalte
; $opt, $visible $newpage all OO_true or OO_False
$errormodul = "_OOSheetSetColProperties"
If IsString($col) Then
$ocol = $oSheet.getColumns().getByName($col)
Else
$ocol = $oSheet.getColumns().getByIndex($col)
EndIf
$ocol.Width = $width ;column width (in 100ths of mm)
$ocol.OptimalWidth = Number($optwidth)
$ocol.IsVisible = Number($visible)
$ocol.IsStartOfNewPage = Number($newpage)
EndFunc ;==>_OOSheetSetColProperties

[/autoit] [autoit][/autoit] [autoit]

Func _OOSheetdeleteRow($startrow, $numberofrows = 1) ;Zeile löschen
$errormodul = "_OOSheetDeleteRow"
$oSheet.getrows.removeByIndex($startrow, $numberofrows)
EndFunc ;==>_OOSheetdeleteRow

[/autoit] [autoit][/autoit] [autoit]

Func _OOSheetinsertRow($startrow, $numberofrows = 1) ;Zeile einfügen
$errormodul = "_OOSheetInsertRow"
$oSheet.getrows.insertbyindex($startrow, $numberofrows)
EndFunc ;==>_OOSheetinsertRow

[/autoit] [autoit][/autoit] [autoit]

Func _OOSheetdeleteCol($startcol, $numberofcols = 1) ;Spalte löschen
$errormodul = "_OOSheetDeleteCol"
$oSheet.getcolumns.removeByIndex($startcol, $numberofcols)
EndFunc ;==>_OOSheetdeleteCol

[/autoit] [autoit][/autoit] [autoit]

Func _OOSheetinsertCol($startcol, $numberofcols = 1) ;spalte einfügen
$errormodul = "_OOSheetInsertCol"
$oSheet.getcolumns.insertbyindex($startcol, $numberofcols)
EndFunc ;==>_OOSheetinsertCol

[/autoit] [autoit][/autoit] [autoit]

Func _OOSheetGetCellType($row, $col) ;Rückgabe: Zellentyp: value, string, formula, empty
$errormodul = "_OOSheetGetCellType"
If IsString($row) Then
$cell = _OOAdress2Koord($row)
$row = $cell[0]
$col = $cell[1]
EndIf
$oCell = $oSheet.getcellbyposition($row, $col)
Select
Case $oCell.type = 1
$celltype = "VALUE"
Case $oCell.type = 2
$celltype = "STRING"
Case $oCell.type = 3
$celltype = "FORMULA"
Case $oCell.type = 0
$celltype = "EMPTY"
Case Else
$celltype = "UNKNOWN"
EndSelect
Return $celltype
EndFunc ;==>_OOSheetGetCellType

[/autoit] [autoit][/autoit] [autoit]

Func _OOSheetMoveOrCopyRange($fromsheetnameornumber, $fromrange, $tosheetnameornumber, $tocell, $flag) ;$fromrange="B2:c4" $tocell="F4" flag: 0=move, 1=copy
$errormodul = "_OOSheetMoveOrCopyRange"
If IsString($fromsheetnameornumber) Then
$fromsheet = $odoc.sheets.getbyname($fromsheetnameornumber)
Else
$fromsheet = $odoc.sheets.getbyindex($fromsheetnameornumber) ;index starts with 0
EndIf
If IsString($tosheetnameornumber) Then
$tosheet = $odoc.sheets.getbyname($tosheetnameornumber)
Else
$tosheet = $odoc.sheets.getbyindex($tosheetnameornumber) ;index starts with 0
EndIf
$oRangeOrg = $fromsheet.getCellRangeByName($fromrange).RangeAddress ; copy range
$oCellCpy = $tosheet.getCellrangeByname($tocell).CellAddress ; insert position
If $flag = 0 Then ;move
$oSheet.MoveRange($oCellCpy, $oRangeOrg)
Else
$oSheet.CopyRange($oCellCpy, $oRangeOrg)
EndIf
EndFunc ;==>_OOSheetMoveOrCopyRange

[/autoit] [autoit][/autoit] [autoit]

Func _OOSheetSetCell($xkoord, $ykoord, $data, $ref = "string") ;$ref= angabe, ob "string" "value" "formula"
$errormodul = "_OOSheetSetCell"
If IsString($xkoord) Then
$cell = _OOAdress2Koord($xkoord)
$xkoord = $cell[0]
$ykoord = $cell[1]

[/autoit] [autoit][/autoit] [autoit]

EndIf
$ref = StringUpper($ref)
Select
Case $ref = "STRING"
$oCell = $oSheet.getCellByPosition($xkoord, $ykoord)
$oCell.setString($data)

[/autoit] [autoit][/autoit] [autoit]

Case $ref = "VALUE"
$oCell = $oSheet.getCellByPosition($xkoord, $ykoord)
$oCell.setvalue($data)

[/autoit] [autoit][/autoit] [autoit]

Case $ref = "FORMULA"
$oCell = $oSheet.getCellByPosition($xkoord, $ykoord)
$oCell.setformula($data)

[/autoit] [autoit][/autoit] [autoit]

Case Else
$oCell = $oSheet.getCellByPosition($xkoord, $ykoord)
$oCell.setstring($data)
EndSelect
EndFunc ;==>_OOSheetSetCell

[/autoit] [autoit][/autoit] [autoit]

Func _OOCloseBook() ;Datei beenden
$errormodul = "_OOCloseBook"
$odoc.close(True)
EndFunc ;==>_OOCloseBook

[/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

Func _OOSheetSetCellColor($col, $row, $front = Default, $back = Default) ;RRGGBB
$errormodul = "_OOSheetSetCell"
If IsString($col) Then
$cell = _OOAdress2Koord($col)
$col = $cell[0]
$row = $cell[1]
EndIf
$oCell = $oSheet.getCellByPosition($col, $row)
$oCell.CharColor = $front
$oCell.CellBackColor = $back
EndFunc ;==>_OOSheetSetCellColor

[/autoit] [autoit][/autoit] [autoit]

Func _OOSheetSetRowColor($row, $front = Default, $back = Default) ;RRGGBB
$errormodul = "_OOSheetSetRowColor"
$orow = $oSheet.getRows().getByIndex($row)
$orow.CharColor = $front
$orow.CellBackColor = $back
EndFunc ;==>_OOSheetSetRowColor

[/autoit] [autoit][/autoit] [autoit]

Func _OOSheetSetColColor($col, $front = Default, $back = Default) ;RRGGBB
$errormodul = "_OOSheetSetColColor"
$ocol = $oSheet.getColumns().getByIndex($col)
$ocol.CharColor = $front
$ocol.CellBackColor = $back
EndFunc ;==>_OOSheetSetColColor

[/autoit] [autoit][/autoit] [autoit]

Func _OOSheetRangeToArray($sheetnameornumber, $startcell, $endcell, $all = "ALL") ;gibt ein Array der Daten aus dem Bereich des Tabellenblatts
$errormodul = "_OOSheetRangeToArray"
If IsString($sheetnameornumber) Then
$oSheet = $odoc.sheets.getbyname($sheetnameornumber)
Else
$oSheet = $odoc.sheets.getbyindex($sheetnameornumber) ;index starts with 0
EndIf

[/autoit] [autoit][/autoit] [autoit]

If StringUpper($all) = "ALL" Then ;used data of the whole sheet
$oCursor = $oSheet.createCursor()
$oCursor.GotoStartOfUsedArea(0) ;von der ersten ausgefüllten Zelle
$start = $oCursor.getrangeaddress()
$Start_row = $start.startRow
$Start_col = $start.startColumn
$oCursor.GotoEndOfUsedArea(1);bis zur letzten ausgefüllten Zelle
$end = $oCursor.getrangeaddress()
$end_row = $end.endRow
$end_col = $end.endColumn
Else
If IsString($startcell) Then
$cell = _OOAdress2Koord($startcell)
$Start_row = $cell[1]
$Start_col = $cell[0]
EndIf
If IsString($endcell) Then
$cell = _OOAdress2Koord($endcell)
$end_row = $cell[1]
$end_col = $cell[0]
EndIf
EndIf

[/autoit] [autoit][/autoit] [autoit]

$ooarray = $oSheet.getCellRangeByPosition($Start_col, $Start_row, $end_col, $end_row).getDataArray() ;verschachteltes array, in [0] ist die erste zeile, in [2] die 2. usw

[/autoit] [autoit][/autoit] [autoit]

Dim $array[UBound($ooarray)][UBound($ooarray[0])]
For $rows = 0 To UBound($ooarray) - 1
$row = $ooarray[$rows]
For $cols = 0 To UBound($row) - 1
$array[$rows][$cols] = $row[$cols]
Next
Next
Return $array
;ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $GetUsedRangeAddress = ' & $GetUsedRangeAddress & @crlf & '>Error code: ' & @error & @crlf) ;### Debug Console
;~ ; return $Range
EndFunc ;==>_OOSheetRangeToArray

[/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

Func _OOSheetArrayToRange($sheetnameornumber, $cellname, $array) ;Array ins Tabellenblatt am Position cellname ("B4")
$errormodul = "_OOSheetArrayToRange"
If IsString($sheetnameornumber) Then
$oSheet = $odoc.sheets.getbyname($sheetnameornumber) ;"Tabelle1"
Else
$oSheet = $odoc.sheets.getbyindex($sheetnameornumber) ;index starts with 0
EndIf
If IsString($cellname) Then
$cell = _OOAdress2Koord($cellname)
$row = $cell[1]
$col = $cell[0]
Else
return seterror(1,0,0)
EndIf
$rangestring = $cellname & ":" & _OOKoordToAddress($row + UBound($array, 1) - 1, $col + UBound($array, 2)) ;calculate the cellname of the last arrayitem in the sheet
;build ooArray
$dimensions = UBound($array, 0)
If $dimensions > 2 Then Return SetError(1, 0, 0) ;nur maximal 2-dimensionale arrays
If $dimensions = 1 Then ;eine zeile im Array wird zu einer spalte im sheet!!!
Dim $ooarray[1]
$ooarray[0] = $array
Else
Dim $ooarray[UBound($array, 1)] ;anzahl der Zeilen
Dim $arows[UBound($array, 2)] ;länge der zeilen
For $row = 0 To UBound($array, 1) - 1 ;alle Zeilen abarbeiten
For $col = 0 To UBound($array, 2) - 1 ;alle Spalteneinträge in dieser Zeile
$arows[$col] = $array[$row][$col] ;die Spalteneinträge als Reihe ins array
Next
$ooarray[$row] = $arows ;das array der gesamten Zeile in das ooarray an index $row
Next
EndIf
$oRange = $oSheet.getCellRangeByName($rangestring)
$oData = $oRange.setDataArray($ooarray)
EndFunc ;==>_OOSheetArrayToRange

[/autoit] [autoit][/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

; Das ist unser eigener Error-Handler
Func _OOErrFunc() ;COM-Error-Handler
$HexNumber = Hex($oMyError.number,
#CS MsgBox(0, "COM-Error OpenOffice Script", "Ein COM-Fehler wurde abgefangen!" & @CRLF & _
"Fehlernummer: " & $HexNumber & @CRLF & _
"WinDescription: " & $oMyError.windescription & @CRLF & _
"Error in Modul " & $errormodul)
#CE
;SetError(1)
EndFunc ;==>_OOErrFunc

[/autoit]

[autoit]

Local $sQuery = "INSERT INTO tab_calpday_agent (`Datum`,`AgentID`,`Calls_p_H`,`Calls`,`Date`)" & _
"VALUES ('" & $t1_split_z & "',"& _ ;DATUM
"'" & $s_Row_txt_t1[11] & "'," & _ ;AgentID
"'" & $s_Row_txt_t1[12] & "'," & _ ;Calls ph
"'" & $s_Row_txt_t1[13] & "'," & _ ;Calls
"'" & $s_Row_txt_t1[10] & "')"

[/autoit]

[autoit]

Local $sQuery = "INSERT INTO tab_vorgang (`DATUM`,`SB Name`,`SB Kürzel`,`Option`,`Ag-Nr`,`Ag-Nr-Brutto`,`Bu-Nr`,`Pax`,`RRKV Umsatz`,`RRKV Umsatz Brutto`, " & _
"`TourLST`,`TourLST-Brutto`,`UMSATZ EUR`,`UMSATZ isoliert EUR`,`REISE-DAT`,`REISE-DAT-Brutto`,`VA`,`WVLKZ`,`ULA_DAT`,`WVL-DAT`,`WVLKZ-Brutto`) " & _
"VALUES ('" & _MySQL_Real_Escape_String($MysqlConn,$s_Row_txt_t1[1]) & "',"& _ ;DATUM
"'" & _MySQL_Real_Escape_String($MysqlConn,$s_Row_txt_t1[2]) & "'," & _ ;SB Name
"'" & _MySQL_Real_Escape_String($MysqlConn,$s_Row_txt_t1[3]) & "'," & _ ;SB Kürzel
"'" & _MySQL_Real_Escape_String($MysqlConn,$s_Row_txt_t1[4]) & "'," & _ ;Option
"'" & _MySQL_Real_Escape_String($MysqlConn,$s_Row_txt_t1[5]) & "'," & _ ;Ag-Nr
"'" & _MySQL_Real_Escape_String($MysqlConn,$s_Row_txt_t1[5]) & "'," & _ ;Ag-Nr (feld --> Ag-Nr-Brutto)
"'" & _MySQL_Real_Escape_String($MysqlConn,$s_Row_txt_t1[6]) & "'," & _ ;Bu-Nr
"'" & _MySQL_Real_Escape_String($MysqlConn,$s_Row_txt_t1[7]) & "'," & _ ;Pax
"'" & _MySQL_Real_Escape_String($MysqlConn,$s_Row_txt_t1[8]) & "'," & _ ;RRKV Umsatz
"'" & _MySQL_Real_Escape_String($MysqlConn,$s_Row_txt_t1[8]) & "'," & _ ;RRKV Umsatz (feld --> RRKV Umsatz Brutto)
"'" & $s_Row_txt_t1[12] & "'," & _ ;TourLST
"'" & $s_Row_txt_t1[12] & "'," & _ ;TourLST (feld --> TourLST-Brutto)
"'" & _MySQL_Real_Escape_String($MysqlConn,$s_Row_txt_t1[9]) & "'," & _ ;UMSATZ EUR
"'" & _MySQL_Real_Escape_String($MysqlConn,$s_Row_txt_t1[9]) & "'," & _ ;UMSATZ EUR (feld --> UMSATZ isoliert EUR)
"'" & _MySQL_Real_Escape_String($MysqlConn,$s_Row_txt_t1[10]) & "'," & _ ;REISE-DAT
"'" & _MySQL_Real_Escape_String($MysqlConn,$s_Row_txt_t1[10]) & "'," & _ ;REISE-DAT (feld --> REISE-DAT-Brutto)
"'" & _MySQL_Real_Escape_String($MysqlConn,$s_Row_txt_t1[11]) & "'," & _ ;VA
"'" & _MySQL_Real_Escape_String($MysqlConn,$s_Row_txt_t1[13]) & "'," & _ ;WVLKZ
"'" & _MySQL_Real_Escape_String($MysqlConn,$s_Row_txt_t1[14]) & "'," & _ ;ULA_DAT
"'" & _MySQL_Real_Escape_String($MysqlConn,$s_Row_txt_t1[15]) & "'," & _ ;WVL-DAT
"'" & _MySQL_Real_Escape_String($MysqlConn,$s_Row_txt_t1[13]) & "')" & _ ;WVLKZ (feld --> WVLKZ-Brutto)
" ON DUPLICATE KEY UPDATE "& _
"`Insert_Error` = NOW()"

[/autoit] [autoit][/autoit] [autoit][/autoit]

[autoit]

#Include <_Ani.au3>
#include <GUIConstantsEx.au3>
#include <GUIConstants.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
;#include <WINAPI.au3>

[/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

#Region ### START Koda GUI section ###
$pic = @ScriptDir & "\TEMP\construction_animated.gif"

[/autoit] [autoit][/autoit] [autoit]

;--$Parent = GUICreate("", 280, 375,-1,-1, $WS_POPUP + $WS_EX_LAYERED +0x00800000);270,137
$Parent = GUICreate("", 320, 297,-1,-1, $WS_POPUP + $WS_EX_LAYERED +0x00800000);270,137
GUISetBkColor(0xFFFFFF) ; setzt hintergrundfarbe
$TRANSPARENCY = 0
WinSetTrans($Parent,"",$TRANSPARENCY)
GUISetState()

[/autoit] [autoit][/autoit] [autoit]

;--$pic2 = GUICtrlCreatePic($Scriptdir & "\" & $pic, 0, 0, 300, 350, BitOR(0x0100, $WS_GROUP, $WS_CLIPSIBLINGS))
$pic2 = GUICtrlCreatePic($pic, 0, 0, 320, 297, BitOR(0x0100, $WS_GROUP, $WS_CLIPSIBLINGS))
;DllCall("uxtheme.dll", "none", "SetThemeAppProperties", "int", 7)

[/autoit] [autoit][/autoit] [autoit]

;--$labelversion = GUICtrlCreateLabel("Version 1.2.0.9", 0, 346, 280, 14, 0x01)
$labelversion = GUICtrlCreateLabel("Version 1.2.1.2", 112, 260, 95, 14, 0x01)
GUICtrlSetFont($labelversion, 8, 80, 0, "Arial")
;GUICtrlSetColor($labelversion, 0xF76E12)
GUICtrlSetBkColor($labelversion, $GUI_BKCOLOR_TRANSPARENT)

[/autoit] [autoit][/autoit] [autoit]

$label = GUICtrlCreateLabel("... Programm wird vorbereitet ...", -2, 274, 320, 13, 0x01)
GUICtrlSetFont($label, 8, 80, 0, "Arial")
GUICtrlSetBkColor($label, $GUI_BKCOLOR_TRANSPARENT)
;GUICtrlSetBkColor($label, $GUI_BKCOLOR_TRANSPARENT)
;GUICtrlSetBkColor($label, 0x000000)
;_WinAPI_SetLayeredWindowAttributes($Parent, 0xFFFFFF) ;setzt transparente farbe, hier weiß

[/autoit] [autoit][/autoit] [autoit]

;animiere gif derzeit auskommentiert ...
;$gif = GUICtrlCreateGifEx($Parent, $pic, 0, 0);no width/height, always automatic. --> erstelle gif picture
;_Ani_SetAnimationSpeed(1, $gif)

[/autoit] [autoit][/autoit] [autoit]

GUISetState(@SW_SHOW)

[/autoit] [autoit][/autoit] [autoit]

#EndRegion ### END Koda GUI section ###

[/autoit] [autoit][/autoit] [autoit]

While 1

[/autoit] [autoit][/autoit] [autoit]

WEnd

[/autoit]

[autoit]

#Include <File.au3>
#include <array.au3>

[/autoit] [autoit][/autoit] [autoit]

DIM $aRecords, $file,

[/autoit] [autoit][/autoit] [autoit]

;------- Daten einlesen & verarbeiten -------------------------
$filepath = "V:\Reporting\Share\Tkanlage\"

[/autoit] [autoit][/autoit] [autoit]

$file =_FileListToArray($filepath,"mail*.001",1)
;_ArrayDisplay($file,"$FileList")

[/autoit] [autoit][/autoit] [autoit]

If @Error = 1 Then
MsgBox (0,"","No Folders Found.",1)
;Exit
$a = 1
Else
If @Error = 4 Then
MsgBox (0,"","No Files Found.",1)
Else
For $x = 1 To $file[0]
;MsgBox(0,"",$file[$x])
$a = 0
einlesen()
Next
EndIf
EndIf

[/autoit] [autoit][/autoit] [autoit]

Func einlesen()

[/autoit] [autoit][/autoit] [autoit]

$file = $filepath & $file[$x]
;MsgBox(0,"",$file)
_file_array() ;liest file in ein array ...
_insert() arbeitet das eingelesene array ab ...
_leeren() ; leert variablen usw. ...

[/autoit] [autoit][/autoit] [autoit]

EndFunc

[/autoit] [autoit][/autoit] [autoit]

; beispiel aus meinem script
Func _insert()

[/autoit] [autoit][/autoit] [autoit]

For $x = 1 to $aRecords[0] -1

[/autoit] [autoit][/autoit] [autoit]

; file formatieren ...
$aRecords[$x] = StringReplace($aRecords[$x], @LF, "|")
$aRecords[$x] = StringReplace($aRecords[$x], " ", "|")
$aRecords[$x] = StringReplace($aRecords[$x], '"|"', "|")
$aRecords[$x] = StringReplace($aRecords[$x], ' |', "|")
$aRecords[$x] = StringReplace($aRecords[$x], '| ', "|")
$aRecords[$x] = StringReplace($aRecords[$x], '"', "")
$aRecords[$x] = StringReplace($aRecords[$x], '#', "")
;Msgbox(0,'Record1 x:', $aRecords[$x])
;$aRecords[$x] = stringstripws($aRecords[$x],1) ;alle aufeinanderfolgenden leerzeichen auf eins reduzieren($aRecords[$x])
Next

[/autoit] [autoit][/autoit] [autoit]

EndFunc

[/autoit] [autoit][/autoit] [autoit]

;formatiert file für weitere verarbeitung ungeändert aus meinem script
Func _file_array()
Dim $inhalt
If Not _FileReadToArray($file,$aRecords) Then
MsgBox(4096,"Error", " Error reading log to Array error:" & @error,1)
Exit
Else
; file formatieren ...
For $x = 1 to $aRecords[0]
;Msgbox(0,'Record:' & $x, $aRecords[$x])
$inhalt &= $aRecords[$x] & @CRLF
Next
;$inhalt = StringReplace($inhalt, " ", "#")
;Leerzeilen & Zeichenumbrüche entfernen
$inhalt = StringStripWS($inhalt,4)
;$inhalt = StringReplace($inhalt, "||", " ")
;$inhalt = StringReplace($inhalt, @CR, "##")
$inhalt = StringReplace($inhalt, " ", @LF)
;ConsoleWrite($inhalt)
$aRecords = StringSplit($inhalt,@CR,1)

[/autoit] [autoit][/autoit] [autoit]

EndIf
;_ArrayDisplay($aRecords)
;Exit
EndFunc

[/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

Func _leeren()
$file = ""
EndFunc

[/autoit] [autoit][/autoit] [autoit][/autoit]