folgend noch die größenanpassung der gui an die einträge aus dem array ...
[autoit]$gui_var = GUICreate("label array", 420 , UBound($aArray_var1) * 20 + 60, -1, -1, BitXOR($GUI_SS_DEFAULT_GUI, $WS_MINIMIZEBOX))
[/autoit]folgend noch die größenanpassung der gui an die einträge aus dem array ...
[autoit]$gui_var = GUICreate("label array", 420 , UBound($aArray_var1) * 20 + 60, -1, -1, BitXOR($GUI_SS_DEFAULT_GUI, $WS_MINIMIZEBOX))
[/autoit]hallo zusammen,
habe nach listbox auswahl immer die gleichen einträge im array, finde aber den fehler nicht!
beim ersten klick ist noch alles prima, wählt man einen zweiten eintrag aus, werden nicht die einträge der neuen listbox auswahl genommen!
bitte um hilfe ![]()
hab den fehler selbst endeckt ... muss die folgenden zwei arrays neu deklarieren!
Dim $aArray_var1[1]
Dim $aArray_list1[1]
#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>
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]
#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
_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)
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
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)
Case $List1
Dim $aArray_var1[1]
Dim $aArray_list1[1]
$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)
$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()")
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
_ArrayDelete($aArray_var1,0)
;_ArrayDisplay($aArray_var1, "1")
_ArrayDelete($aArray_list1,0)
;_ArrayDisplay($aArray_list1, "2")
;----array zusammenführen----
[/autoit] [autoit][/autoit] [autoit]_ArrayConcatenate($aArray_var1, $aArray_list1)
$aArray_var1 = _ArrayUnique($aArray_var1)
_ArrayDelete($aArray_var1, 0)
_ArraySort($aArray_var1)
_GUICtrlEdit_SetText($Edit2, $strData_TB_Text)
_ArrayDisplay($aArray_var1, 'all')
_GUI_Var_List()
[/autoit] [autoit][/autoit] [autoit];----
;ClipPut($wert1)
EndSwitch
WEnd
EndFunc
[/autoit] [autoit][/autoit] [autoit]Func _GUI_Var_List()
[/autoit] [autoit][/autoit] [autoit]Dim $Labels_var[100]
Dim $Inputs_var[100]
$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
;$x += 150
;If $x + 120 > 600 Then
$y_l += 20
$y_i += 20
;$x = 20
;EndIf
Next
$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
WEnd
[/autoit] [autoit][/autoit] [autoit]EndFunc
[/autoit] [autoit][/autoit] [autoit]#Region Textdatei
#CS
Func _File_01()
$DATAC2 = ""
$sString = ""
$file = (@ScriptDir & "\TB.txt")
If Not _FileReadToArray($file,$aRecords) Then
MsgBox(4096,"Error", " Error reading log to Array error:" & @error)
Exit
EndIf
For $x = 1 to $aRecords[0]
;Msgbox(0,'Record:' & $x, $aRecords[$x])
$sString &= $aRecords[$x]
Next
;MsgBox(0,"", $sString)
$DATAC2 = $sString
EndFunc
#CE
#EndRegion Textdatei
;------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)
With $adoRs
If .RecordCount Then
While Not .EOF
$_output = $_output & .Fields($_field).Value & @CRLF
.MoveNext
WEnd
EndIf
EndWith
$adoCon.Close
Return $_output
EndFunc ;==>_ReadOneFld
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)
With $adoRs
If .RecordCount Then
While Not .EOF
$_output = $_output & .Fields($_field).Value & "|" ;@CRLF
.MoveNext
WEnd
EndIf
EndWith
$adoCon.Close
Return $_output
EndFunc ;==>_ReadOneFld
danke ![]()
gruß gmmg
update!
arrays werden zusammengeführt!
derzeit wird ja nur der text aus stringbetween ins array geschrieben!
hat einer eine idee, wie man hier auch noch den "start search string" & "end search string" mit anfügen kann?
folgend das script ...
#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>
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
#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
_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)
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
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)
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)
EndSwitch
WEnd
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]
$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
$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
WEnd
[/autoit] [autoit][/autoit] [autoit]EndFunc
[/autoit] [autoit][/autoit] [autoit]#Region Textdatei
#CS
Func _File_01()
$DATAC2 = ""
$sString = ""
$file = (@ScriptDir & "\TB.txt")
If Not _FileReadToArray($file,$aRecords) Then
MsgBox(4096,"Error", " Error reading log to Array error:" & @error)
Exit
EndIf
For $x = 1 to $aRecords[0]
;Msgbox(0,'Record:' & $x, $aRecords[$x])
$sString &= $aRecords[$x]
Next
;MsgBox(0,"", $sString)
$DATAC2 = $sString
EndFunc
#CE
#EndRegion Textdatei
;------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)
With $adoRs
If .RecordCount Then
While Not .EOF
$_output = $_output & .Fields($_field).Value & @CRLF
.MoveNext
WEnd
EndIf
EndWith
$adoCon.Close
Return $_output
EndFunc ;==>_ReadOneFld
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)
With $adoRs
If .RecordCount Then
While Not .EOF
$_output = $_output & .Fields($_field).Value & "|" ;@CRLF
.MoveNext
WEnd
EndIf
EndWith
$adoCon.Close
Return $_output
EndFunc ;==>_ReadOneFld
die testdatenbank ist im ersten post ... ![]()
danke
gruß gmmg
chip
ja... an die funktion "StringReplace" hab ich auch gedacht, mir fehlt aber der ansatz,
wie ich die label und inputboxen dynamisch auf die 2. GUI bekomme!
beispiel:
zu ersetzen ist folgender textabschnitt "{var:Kundennachname}"
1. ich müsste ja die funktion $aArray_var = _StringBetween($strData_TB_Text, '{var:', '}') benutzen, um das label "Kundennachname" und die Inputbox "xyz" zu definieren!
2. das ganze auf der 2. GUI platzieren
3. inputfelder ausfüllen und über StringReplace zurück in den text schreiben! um StringReplace benutzen zu können, müsste man aber noch sicherstellen, das der ganze string "{var:Kundennachname}" dann ersetzt wird, sonst bleibt dieser ja im text erhalten! ![]()
danke
soooo ... komme der lösung ein stückchen näher ![]()
replacestring über array & schleife erstellt ... und labels auf GUI plaziert!
#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>
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
#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
_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)
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
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)
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
_GUI_Var_List()
EndIf
;MsgBox(0,"",@error)
;If @error <> 1 Then _GUI_Var_List()
;$aArray_list = _StringBetween($strData_TB_Text, '{list:', '}')
;_ArrayDisplay($aArray_list, 'Default Search')
;$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)
_GUICtrlEdit_SetText($Edit2, $strData_TB_Text)
;----
;ClipPut($wert1)
EndSwitch
WEnd
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]
$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()
While 1
$msg = GUIGetMsg()
If $msg = $GUI_EVENT_CLOSE Then
GUIDelete($gui_var)
ExitLoop
EndIf
WEnd
[/autoit] [autoit][/autoit] [autoit]EndFunc
[/autoit] [autoit][/autoit] [autoit]#Region Textdatei
#CS
Func _File_01()
$DATAC2 = ""
$sString = ""
$file = (@ScriptDir & "\TB.txt")
If Not _FileReadToArray($file,$aRecords) Then
MsgBox(4096,"Error", " Error reading log to Array error:" & @error)
Exit
EndIf
For $x = 1 to $aRecords[0]
;Msgbox(0,'Record:' & $x, $aRecords[$x])
$sString &= $aRecords[$x]
Next
;MsgBox(0,"", $sString)
$DATAC2 = $sString
EndFunc
#CE
#EndRegion Textdatei
;------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)
With $adoRs
If .RecordCount Then
While Not .EOF
$_output = $_output & .Fields($_field).Value & @CRLF
.MoveNext
WEnd
EndIf
EndWith
$adoCon.Close
Return $_output
EndFunc ;==>_ReadOneFld
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)
With $adoRs
If .RecordCount Then
While Not .EOF
$_output = $_output & .Fields($_field).Value & "|" ;@CRLF
.MoveNext
WEnd
EndIf
EndWith
$adoCon.Close
Return $_output
EndFunc ;==>_ReadOneFld
gruß gmmg
Hallo Zusammen,
ich habe einen vielzahl von Textvorlagen /Textbausteine, in denen es bestimmte Platzhalter (siehe Beispiel) gibt!
Dies sollen nun aus dem Text ausgelesen werden ( $aArray_var = _StringBetween($strData_TB_Text, '{var:', '}') und auf einer zusätzlichen GUI als Input oder Listbox dynamisch erstellt werden ... und nach ausfüllen dieser, dann wieder in den Text schreiben ...
wer kann mir einen denkanstoss geben, komme grad nicht weiter?
Textbeispiel: {var:Kundennachname}
{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
hier das script ![]()
#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>
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
#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
_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)
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
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)
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')
;$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)
_GUICtrlEdit_SetText($Edit2, $strData_TB_Text)
;----
;ClipPut($wert1)
EndSwitch
WEnd
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
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]
While 1
$msg = GUIGetMsg()
If $msg = $GUI_EVENT_CLOSE Then
GUIDelete($gui_var)
ExitLoop
EndIf
WEnd
[/autoit] [autoit][/autoit] [autoit]EndFunc
[/autoit] [autoit][/autoit] [autoit]#Region Textdatei
#CS
Func _File_01()
$DATAC2 = ""
$sString = ""
$file = (@ScriptDir & "\TB.txt")
If Not _FileReadToArray($file,$aRecords) Then
MsgBox(4096,"Error", " Error reading log to Array error:" & @error)
Exit
EndIf
For $x = 1 to $aRecords[0]
;Msgbox(0,'Record:' & $x, $aRecords[$x])
$sString &= $aRecords[$x]
Next
;MsgBox(0,"", $sString)
$DATAC2 = $sString
EndFunc
#CE
#EndRegion Textdatei
;------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)
With $adoRs
If .RecordCount Then
While Not .EOF
$_output = $_output & .Fields($_field).Value & @CRLF
.MoveNext
WEnd
EndIf
EndWith
$adoCon.Close
Return $_output
EndFunc ;==>_ReadOneFld
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)
With $adoRs
If .RecordCount Then
While Not .EOF
$_output = $_output & .Fields($_field).Value & "|" ;@CRLF
.MoveNext
WEnd
EndIf
EndWith
$adoCon.Close
Return $_output
EndFunc ;==>_ReadOneFld
im anhang ist die TB.mdb als zip, in dieser sind paar Texte mit Variablen zum testen hinterlegt!
vielleicht kann sich das mal einer ansehen!
danke
Gruß gmmg ![]()
hallo zusammen,
ich habe ein problem bei folgendem vorhaben! das ganze wird in excel geschrieben und ist schon sehr umfangreich ![]()
was bisher funktioniert:
zählen der user pro department
zählen der user pro companie
nun möchte ich aber, die daten so wie im Bild "zuordnung" (siehe anlage) auf dem rechten bildteil zu sehen ist, die companie aufführen und darunter die dazugehörigen departments mit der anzahl der user!
so wie es im linken bildteil zu sehen ist, habe ich es schon!
über die _GetUsers_Summary_xxx funktionen wird ja der entsprechende teilabschnitt erstellt ...
[autoit]
user1 Department1 Firma1
user2 Department3 Firma1
user3 Department2 Firma1
user4 Department1 Firma2
user5 Department3 Firma2
user6 Department1 Firma1
user7 Department3 Firma3
user8 Department1 Firma3
user9 Department3 Firma2
folgend das komplette script
#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>
; excel book erstellen
; user nach sites eintragen
; user nach company eintragen
; user gesamt nach sites eintragen
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
$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")
;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
;---------------------------------
;_ArrayDelete($aOUs, 0) ;löscht 1 eintrag aus dem array
_ArraySort($aOUs)
;_ArrayDisplay($aOUs, "AD all Site OUs!")
$site = InputBox("Site","Input - AD Site Name" & @CRLF & @CRLF & "e.g. xxx01 or xxx02...")
IF @error = 1 Then Exit
;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)
$x = 1
$aOUs[$x] = StringUpper($site)
;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)
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]
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)
$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
#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
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
_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)
#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]
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
_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
;---------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]
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
Next
;---------Ende YGesamt---------------------
#EndRegion YGesamt
_ExcelSheetActivate($oExcel,$yearsheet)
;_ArrayDisplay($alogin)
;$saveas = "C:\User_in Sites"
;_ExcelBookSaveAs($oExcel, $saveas, "xls")
;_ExcelBookClose($oExcel, 1, 0)
#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
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
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
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)
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
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)
Local $aResult1[UBound($aDepartments)][2]
For $i = 0 To UBound($aDepartments) - 1
$aResult1[$i][0] = $aDepartments[$i]
Next
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
;_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
vielleicht hat ja einer eine spontane idee?
danke
gruß gmmg
schau mal im englischen forum
hab dir mal die dateien angehangen!
bei sql 2005 funktioniert es auch!
http://www.autoitscript.com/forum/topic/51…ge__hl__sql+udf
#include <_SQL.au3>
#include <array.au3>
Opt ("trayIconDebug",1)
[/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]$IP = "ipadrese-servername"
$User = "user"
$Pass = "passwd"
$DB = "testdb"
Msgbox(0,"","Start the Script and load the error handler")
_SQL_RegisterErrorHandler();register the error handler to prevent hard crash on COM error
$oADODB = _SQL_Startup()
If $oADODB = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())
If _SQL_Connect(-1,$IP,"",$User,$Pass) = $SQL_ERROR then
Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())
_SQL_Close()
Exit
EndIf
If _SQL_Execute(-1,"Create database My_SQL_Test;") = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())
_SQL_Close()
Msgbox(0,"","Created datatbase logging out and back in again")
$oADODB = _SQL_Startup()
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())
; 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())
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)" )
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)" )
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")
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
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
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" )
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())
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()
Msgbox(0,"","Example Finished")
[/autoit]gruß gmmg
ich schau mir das ganze morgen nochmal an ![]()
danke für deine hilfe!
gruß gmmg
hallo water,
danke erstmal für die antwort! ![]()
ich muss mal sehen, wie mein kollege das jetzt haben möchte!
vielleicht macht es sinn, das für jede einzelne ad site dynamisch umzubauen, da dies jeder siteadmin in seiner site ausführen soll!
hierzu müsste dann ja nur der DC dieser site abgefragt werden und dieser sollte ja dann auch die aktuellsten daten für diese site haben!
gruß gmmg
ich muss das Thema mit dem _AD_GetLastLoginDate nochmal aufgreifen!
Problem hierbei, die Replikation der LastLogin zeiten, der einzelnen Standorte!
Kann man dem Script irgendwie mitteilen, das er den "@LogonServer" benutzt, an dem der user, der das script startet, angemeldet ist?
[autoit]
Local $iLLDate = _AD_GetLastLoginDate($aObjects[$i][0],"unser Sitename",$aDC)
; übergabe des logonservers ...
Local $iLLDate = _AD_GetLastLoginDate($aObjects[$i][0],"unser Sitename",@LogonServer)
gruß gmmg
hallo hela,
hier mal ein beispiel zum lesen einer mysql db!
[autoit][/autoit][autoit][/autoit][autoit]#include <array.au3>
#include <mysql.au3>
; db verbindungsvariablen
$ipadd = "ipadresse" ; server ip
$sname = "xxx" ; user
$spasswort = "xxx" ; passwd
$db = "db_statistiken_daten" ; datenbank
_MySQL_InitLibrary()
If @error Then Exit MsgBox(0, "Fehler", "libmysql.dll nicht gefunden")
$MysqlConn = _MySQL_Init()
[/autoit][autoit][/autoit][autoit]$connected = _MySQL_Real_Connect($MysqlConn,$ipadd,$sname,$spasswort,$db)
If $connected = 0 Then
$errno = _MySQL_errno($MysqlConn)
MsgBox(0,"Fehler","Login Server ist nicht Erreichbar")
Exit
Endif
Local $sQuery = "SELECT * FROM `tab_calpday_agent` WHERE AgentID=7001"
;MsgBox(0,"",$sQuery)
_MySQL_Real_Query($MysqlConn, $sQuery)
[/autoit][autoit][/autoit][autoit][/autoit][autoit];$MySQL= "SELECT * FROM `plattenbeobachtung`"
; _MySQL_Real_Query($MysqlConn, $MySQL)
$res = _MySQL_Store_Result($MysqlConn)
$array = _MySQL_Fetch_Result_StringArray($res)
_MySQL_Free_Result($res)
_ArrayDisplay($array)
; Verbindung beenden
_MySQL_Close($MysqlConn)
; MYSQL beenden
_MySQL_EndLibrary()
; schleife durch das array ...
[/autoit][autoit][/autoit][autoit]For $i = 0 To UBound($array) - 1
; die einzelnen array werte ausgeben
MsgBox(0,"", $array[$i][0] & " " & $array[$i][1] & " " & $array[$i][2] & " " & $array[$i][3])
Next
die einzelnen werte z.b. ($array[$i][0]) , kannst du dann an ein textfeld, eine variable oder was auch immer übergeben ...
hier ein update!
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]) & "'"
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)
und noch das insert ![]()
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] & "'" & ")"
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)
gruß gmmg
schau dir mal den folgenden eitrag an!
MySQL UDF - mit libmySQL.dll (kein ODBC)
und hier gibts auch noch beispiele:
[ gelöst ] Mysql Insert + Update Data
gruß gmmg
hallo zusammen,
folgend meine Lösung, um ein Tabellenblatt zu löschen!
[autoit]
_OOBookDeleteSheet("Tabelle1")
;_OOBookDeleteSheet("sheet1")
Func _OOBookDeleteSheet($sheetname) ;Delete other Sheets (by default Sheet2,Sheet3)
$errormodul = "_OODeleteSheet"
If $odoc.Sheets.hasByName($sheetname) Then $odoc.Sheets.removeByName($sheetname)
EndFunc ;==>_OOBookDeleteSheet
die Funktionen aus dem ersten Post nochmal komplett mit SheetDelete!
;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
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
Func _OOAddNewBook() ; neue Tabellendatei erstellen
$errormodul = "_OOAddNewBook"
$odoc = $oDesk.loadComponentFromURL("private:factory/scalc", "_blank", 0, $OpenPar)
$oSheet = $odoc.CurrentController.ActiveSheet
EndFunc ;==>_OOAddNewBook
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
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
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
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
If $activeflag = 1 Then
$oSheet = $odoc.CurrentController.setActiveSheet($activesheet)
EndIf
$oSheet = $activesheet
EndFunc ;==>_OOSheetActivate
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
Func _OOBookDeleteSheet($sheetname) ;Delete other Sheets (by default Sheet2,Sheet3)
$errormodul = "_OODeleteSheet"
If $odoc.Sheets.hasByName($sheetname) Then $odoc.Sheets.removeByName($sheetname)
EndFunc ;==>_OOBookDeleteSheet
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
Func _OONumberOfSheets() ; Anzahl der Tabellenblätter
$errormodul = "_OONumberofsheets"
Return $odoc.getsheets.getcount
EndFunc ;==>_OONumberOfSheets
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
Func _OOKoordToAddress($row,$col) ;wandelt 2,3 in "C4"
$x = Int($col / 26)
$y = Mod($col, 26)
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
Func _OONameOfActiveSheet()
$errormodul = "_OONameofAvtiveSheet"
Return $oSheet.name
EndFunc ;==>_OONameOfActiveSheet
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
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
Func _OOSheetdeleteRow($startrow, $numberofrows = 1) ;Zeile löschen
$errormodul = "_OOSheetDeleteRow"
$oSheet.getrows.removeByIndex($startrow, $numberofrows)
EndFunc ;==>_OOSheetdeleteRow
Func _OOSheetinsertRow($startrow, $numberofrows = 1) ;Zeile einfügen
$errormodul = "_OOSheetInsertRow"
$oSheet.getrows.insertbyindex($startrow, $numberofrows)
EndFunc ;==>_OOSheetinsertRow
Func _OOSheetdeleteCol($startcol, $numberofcols = 1) ;Spalte löschen
$errormodul = "_OOSheetDeleteCol"
$oSheet.getcolumns.removeByIndex($startcol, $numberofcols)
EndFunc ;==>_OOSheetdeleteCol
Func _OOSheetinsertCol($startcol, $numberofcols = 1) ;spalte einfügen
$errormodul = "_OOSheetInsertCol"
$oSheet.getcolumns.insertbyindex($startcol, $numberofcols)
EndFunc ;==>_OOSheetinsertCol
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
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
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]
EndIf
$ref = StringUpper($ref)
Select
Case $ref = "STRING"
$oCell = $oSheet.getCellByPosition($xkoord, $ykoord)
$oCell.setString($data)
Case $ref = "VALUE"
$oCell = $oSheet.getCellByPosition($xkoord, $ykoord)
$oCell.setvalue($data)
Case $ref = "FORMULA"
$oCell = $oSheet.getCellByPosition($xkoord, $ykoord)
$oCell.setformula($data)
Case Else
$oCell = $oSheet.getCellByPosition($xkoord, $ykoord)
$oCell.setstring($data)
EndSelect
EndFunc ;==>_OOSheetSetCell
Func _OOCloseBook() ;Datei beenden
$errormodul = "_OOCloseBook"
$odoc.close(True)
EndFunc ;==>_OOCloseBook
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
Func _OOSheetSetRowColor($row, $front = Default, $back = Default) ;RRGGBB
$errormodul = "_OOSheetSetRowColor"
$orow = $oSheet.getRows().getByIndex($row)
$orow.CharColor = $front
$orow.CellBackColor = $back
EndFunc ;==>_OOSheetSetRowColor
Func _OOSheetSetColColor($col, $front = Default, $back = Default) ;RRGGBB
$errormodul = "_OOSheetSetColColor"
$ocol = $oSheet.getColumns().getByIndex($col)
$ocol.CharColor = $front
$ocol.CellBackColor = $back
EndFunc ;==>_OOSheetSetColColor
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
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
$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
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
; 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
gruß gmmg ![]()
eine GUI sollte ja kein problem darstellen, da gibts ja auch genug infos im forum!
hallo,
hier ein beispiel zum schreiben ...
[autoit]
#Include <Date.au3>
_access_Logwrite()
[/autoit][autoit][/autoit][autoit]Func _access_Logwrite()
[/autoit][autoit][/autoit][autoit]$s_dbname = "V:\log.mdb"
$s_data01 = _Now()
$s_data02 = @ComputerName
$s_data03 = @UserName
$FULL_MDB_FILE_NAME = $s_dbname
$SQL_CODE = "select * from log"
$CONN = ObjCreate("ADODB.Connection")
$CONN.Open('Driver={Microsoft Access Driver (*.mdb)};Dbq=' & $FULL_MDB_FILE_NAME & ';')
$RecordSet = ObjCreate("ADODB.Recordset")
Local $sQuery = "INSERT INTO log (`Datum`,`Computer`,`User`)" & _
"VALUES ('" & $s_data01 & "',"& _ ;DATUM
"'" & $s_data02 & "'," & _ ;Computer
"'" & $s_data03 & "')" ;User
$CONN.Execute($sQuery)
$CONN.Close
EndFunc
[/autoit]im anhang die datenbank zum testen!
gruß gmmg
@abc-user
ich verwende folgende query's! diese können auch lerrzeichen enthalten!
Beispiel 1
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] & "')"
Beispiel 2
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()"
gruß gmmg
hier mal ein beispiel aus meiner script sammlung!
#Include <_Ani.au3>
#include <GUIConstantsEx.au3>
#include <GUIConstants.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
;#include <WINAPI.au3>
#Region ### START Koda GUI section ###
$pic = @ScriptDir & "\TEMP\construction_animated.gif"
;--$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()
;--$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)
;--$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)
$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ß
;animiere gif derzeit auskommentiert ...
;$gif = GUICtrlCreateGifEx($Parent, $pic, 0, 0);no width/height, always automatic. --> erstelle gif picture
;_Ani_SetAnimationSpeed(1, $gif)
GUISetState(@SW_SHOW)
[/autoit] [autoit][/autoit] [autoit]#EndRegion ### END Koda GUI section ###
[/autoit] [autoit][/autoit] [autoit]While 1
[/autoit] [autoit][/autoit] [autoit]WEnd
[/autoit]vielleicht nützt es dir was ![]()
gruß gmmg
ich würde auf jedenfall die dateien, die verarbeitet wurden, in einen unterordner speichern, damit hast du dann nur die neusten im hauptordner
zum einlesen ...
1. alle entsprechenden dateien in ein array1
2. steht das datum immer in der gleichen zeile? dann könntest du mit FileReadLine($file, line) das datum auslesen
3. schreibe in array2 ( _ArrayAdd(array2, dateiname aus array1 & ";" & Zeile mit datum aus der jeweiligen datei
4. array1 und array2 verarbeiten
vlt. bissl umständlich, aber müsste machbar sein
gruß gmmg
hallo F1109,
anbei mal ein scriptausschnitt ...
vielleicht kannste davon was benutzen ![]()
#Include <File.au3>
#include <array.au3>
DIM $aRecords, $file,
[/autoit] [autoit][/autoit] [autoit];------- Daten einlesen & verarbeiten -------------------------
$filepath = "V:\Reporting\Share\Tkanlage\"
$file =_FileListToArray($filepath,"mail*.001",1)
;_ArrayDisplay($file,"$FileList")
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
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. ...
EndFunc
[/autoit] [autoit][/autoit] [autoit]; beispiel aus meinem script
Func _insert()
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
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)
EndIf
;_ArrayDisplay($aRecords)
;Exit
EndFunc
Func _leeren()
$file = ""
EndFunc
gruß gmmg
ahhh ok!
vielen dank ...
gruß gmmg