#include #include #include #include #include #include #include #include #include ;********************************************************************************** ; Dateien Pfad ApiKey evtl. Ändern ;* Global $ApiKey = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" ;* ; Quelldatei von Winitas CSV ;* Global $Quelldatei = "C:\Users\xx2\Documents\\Export\Aufträge.csv" ;* ; Zieldatei AATour.gpx für ITN Converter ;* Global $Zieldatei = "C:\Users\xx2\Desktop\AATour.gpx" ;* ; My Drive Connect ;* Global $DConnect = "C:\Users\xx2\MyDrive Connect\TomTom MyDrive Connect.exe" ;* ;********************************************************************************** ; Fenster_Excel() ; Funktion prüfen ob Excel.exe offen ist If ProcessExists("Excel.exe") Then MsgBox(0,"ExcelDateien schließen","") Endif ; Gui für Tourenplanug #Region ; Ganzer Bildschirm freischalten ;GUICreate($Ersteller, @DesktopWidth, @DesktopHeight, 0, 0,$WS_MAXIMIZEBOX+$WS_MAXIMIZE) Global $Form1 = GUICreate ("Tourenplanung " ,500 ,300 ,800 ,500) Global $Button1 = GUICtrlCreateButton("MyDrive Connect laden", 150, 50 ,200,30) Global $Button2 = GUICtrlCreateButton("Adressen Import", 150, 100 ,200,30) Global $Button3 = GUICtrlCreateButton("Mail mit Telefonnummern", 150, 150 ,200,30) Global $Button4 = GUICtrlCreateButton("Ende", 150, 200 ,200, 30) ; Fenster im Vordergrund ;WinSetOnTop($Form1,"",1) ;Farbe Sibergrau GUISetBkColor($COLOR_Silver) ;GUIStartGroup() GUISetState() #EndRegion While 2 $nMsg = GUIGetMsg() ; X = Ende Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $Button1 Global $DConnect = "C:\Users\xx2\MyDrive Connect\TomTom MyDrive Connect.exe" ShellExecute($DConnect,"-startda","","open",@SW_SHOW) ; My Drive Connect mit Parameter "-startda" starten ;Global $hWnd = WinGetHandle ("[CLASS: Qt5QWindowIcon]") Sleep (5000) WinSetState ( "MyDrive Connect" , "" , @SW_MINIMIZE ) ;If _TomTomConnected() Then ConsoleWrite("... TomTom ist verbunden ..." & @CRLF) If _TomTomConnected() Then Global $Meldung = _TomTomConnected() MsgBox (0 , "TomTom ist verbunden", $Meldung) Else Global $Meldung = _TomTomConnected() MsgBox (0 , "TomTom ist nicht verbunden", $Meldung) Endif Case $Button2 ;Quell_vorhanden() ; Funtion ob alte Quelldatei vorhanden ;Mausklick() ; Funktion Mauscklick ;FileMove ( $Quell , $Ziel ,0) ;FileDelete ($Quell) ; Excel öffnen Global $oExcel = _Excel_Open() Global $oWorkBook = _Excel_BookOpen($oExcel, $Quelldatei) ; Kurz Warten und Excel in den Vordergrund sleep (500) WinActivate ($oWorkbook , "") ;SuchNavi() ; Funtion My Drive Connect mit Parameter "-startda" starten Winitas_Datei() ; Funktion Winitas Datei bearbeiten Lat_Lon() ; Funktion Lat und Lon berechnen AATour() ; Funtion AATour.gpx einlesen Dateien_loeschen() ; alle Daten löschen _Excel_BookSaveAs ( $oWorkbook , "" , $xlHtml , True ) Case $Button3 Global $Quelldatei = "C:\Users\xx2\Documents\\Export\Aufträge.csv" Global $oExcel = _Excel_Open() Global $oWorkBook = _Excel_BookOpen($oExcel, $Quelldatei) Global $EMail = "xxxx@gmx.de" Global $oOutlook = _OL_Open() If @error <> 0 Then Exit MsgBox(16, "OutlookEX UDF", "Error creating a connection to Outlook. @error = " & @error & ", @extended = " & @extended) Global $sMeineMailadresse = $EMail Global $sBetreff = "Telefonnummern Kunden Tour " & _NowDate() Global $aTermine = __ParseCSV($Quelldatei, ';') ; <== Name der Eingabedatei hier anpassen! Global $sBody = "" For $i = 1 to UBound($aTermine) - 1 If $aTermine[$i][0] <> "" Then ; ignoriere leere Zeilen $sBody &= "

" & $aTermine[$i][1] & "
" ; Vorname Name $sBody &= $aTermine[$i][2] & "
" ; Straße If StringLen($aTermine[$i][3]) = 4 Then $sBody &= "0" & $aTermine[$i][3] & " " & $aTermine[$i][4] & "
" ; PLZ Ort 4 stellig mit 0 Else $sBody &= $aTermine[$i][3] & " " & $aTermine[$i][4] & "
" ; PLZ Ort 5 stellig Endif If $aTermine[$i][5] <> "" Then $sBody &= "Privat: " & $aTermine[$i][5] & "
" ; Telefonnr 1 If $aTermine[$i][6] <> "" Then $sBody &= "Firma: " & $aTermine[$i][6] & "
" ; Telefonnr 2 If $aTermine[$i][7] <> "" Then $sBody &= "Mobil: " & $aTermine[$i][7] & "
" ; Handy EndIf Next $sBody &= "" Global $oItem = _OL_ItemCreate($oOutlook, $olMailItem, "", "", "Subject=" & $sBetreff, "BodyFormat=" & $olFormatHTML, "HTMLBody=" & $sBody) If @error <> 0 Then Exit MsgBox(16, "", "Item Create: @error = " & @error & ", @extended = " & @extended) _OL_ItemRecipientAdd($oOutlook, $oItem, Default, $olTo, $sMeineMailadresse) If @error <> 0 Then Exit MsgBox(16, "", "Recipient Add: @error = " & @error & ", @extended = " & @extended) ;$oItem.send ; sendet ohne nachfrage $oItem.display ; zeigt die Mail And Case $Button4 Exit EndSwitch WEnd ;************************************************************** ; Alle Funtionen * ;************************************************************** Func _TomTomConnected() Local $wbemFlagReturnImmediately = 0x10, $wbemFlagForwardOnly = 0x20 Local $colItems = "", $strComputer = "." Local $objWMIService = ObjGet("winmgmts:\\" & $strComputer & "\") If Not IsObj($objWMIService) Then Return SetError(1, 0, 0) $colItems = $objWMIService.ExecQuery('SELECT * FROM Win32_NetworkAdapter Where Name="TomTom" AND NetConnectionStatus=2', "WQL", _ $wbemFlagReturnImmediately + $wbemFlagForwardOnly) For $objItem In $colItems Return $objItem.NetConnectionStatus Next Return SetError(2, 0, 0) EndFunc ;==>_TomTomConnected ;Func SuchNavi() ; Navi anmelden bei TomTom ; ShellExecute($DConnect,"-startda","","open",@SW_Hide) ; My Drive Connect mit Parameter "-startda" starten ; MsgBox(64,"Title","Schließe das Tom Tom Navigationsgerät an USB an"&@crlf&"und warte bis es verbunden ist") ;ShellExecute($DConnect,"-startda","","open",@SW_SHOW) ; My Drive Connect mit Parameter "-startda" starten ;EndFunc Func Winitas_Datei() ; Überflüssige Reihen löschen _Excel_RangeDelete ($oWorkBook.ActiveSheet, "A:A") _Excel_RangeDelete ($oWorkBook.ActiveSheet, "B:B") _Excel_RangeDelete ($oWorkBook.ActiveSheet, "C:C") _Excel_RangeDelete ($oWorkBook.ActiveSheet, "F:F") _Excel_RangeDelete ($oWorkBook.ActiveSheet, "I:M") ; Neue Überschriften $oExcel.ActiveSheet.Cells(1, 1) = "Nummer" $oExcel.ActiveSheet.Cells(1, 2) = "Name" $oExcel.ActiveSheet.Cells(1, 3) = "Straße" $oExcel.ActiveSheet.Cells(1, 4) = "PLZ" $oExcel.ActiveSheet.Cells(1, 5) = "Ort" $oExcel.ActiveSheet.Cells(1, 8) = "Handy" $oExcel.ActiveSheet.Cells(1, 9) = "Lat" $oExcel.ActiveSheet.Cells(1, 10) = "Lon" ; Prüfen ob PLZ Leer dann Zeile löschen Global $Zzahl = $oExcel.ActiveSheet.Cells($oExcel.Rows.Count, 2).End(-4162).Row ; -4162 = xlUp Global $R = 2 For $R = 2 To $Zzahl Global $PLZ = _Excel_RangeRead($oWorkbook, Default, "D" &$R) If $Plz = 0 Then Local $srange = $R &":"& $R _Excel_RangeDelete($oWorkbook.ActiveSheet, $sRange, $xlShiftUp) EndIf Next EndFunc Func Lat_Lon() ; Funktion Lat und Lon berechnen ; Suche letzte Zeile Global $Zzahl = $oExcel.ActiveSheet.Cells($oExcel.Rows.Count, 2).End(-4162).Row ; -4162 = xlUp Global $R = 2 For $R = 2 To $Zzahl Global $Strasse = _Excel_RangeRead($oWorkbook, Default, "C" &$R) Global $PLZ = _Excel_RangeRead($oWorkbook, Default, "D" &$R) If StringLen ($PLZ) = 4 Then Global $PLZ1 = StringLeft ( StringFormat ( '%09s' , $PLZ ) , 9 ) Global $PLZ = StringLeft ( $PLZ1 , 1 ) & StringRight ( $PLZ1 , 4 ) EndIf Global $Ort = _Excel_RangeRead($oWorkbook, Default, "E" &$R) Global $Adresse = $Strasse&","& + $PLZ& " "& + $Ort $oExcel.Cells($R, 9 ).NumberFormat = "@" $oExcel.Cells($R, 10).NumberFormat = "@" ; XML Ausgabe = .xml ; Sprache = &countrySet=DEU ;MsgBox (0, "Url " , $Url) Global $url = ( "https://api.tomtom.com/search/2/geocode/&"&$Adresse&".xml?key="&$ApiKey&"&countrySet=DEU") ;MsgBox (0, "Url " , $Url) ;Sleep ( 100 ) InetGet($Url ,"Koordinaten.xml") ; Internet Seite speichern Global $strXML = FileRead("Koordinaten.xml") ;MsgBox (0, "string " , $strXML) ; Berechnung Lat Global $Pos1 = StringInStr($strXML, "") ; Position von "")im großen String = 1141 ;MsgBox (0, "pos1 " , $pos1) Global $neuPos1 = $Pos1 + 5 ; "!ld" Zeichen entfernen neue Position = 1146 ;MsgBox (0, "neupos " , $neupos1) Global $Pos2 = StringInStr($strXML, "") ; Position von """)")im großen String = 1169 ;MsgBox (0, "pos1 " , $pos1) Global $neuPos1 = $Pos1 + 5 ; "!ld" Zeichen entfernen neue Position = 1174 ;MsgBox (0, "neupos " , $neupos1) Global $Pos2 = StringInStr($strXML, "" & @CRLF $KText &= "" & @CRLF $KText &= " " & @CRLF $KText &= " " & @CRLF $KText &= " TVG-Verlag" & @CRLF $KText &= " " & @CRLF $KText &= " " & @CRLF $KText &= " RouteNavigator Europa - Route" & @CRLF $KText &= " " & @CRLF FileWrite ( $GPXdatei , $KText ) Global $R = 2 For $R = 2 To $Zzahl Global $Nummer = _Excel_RangeRead($oWorkbook, Default, "A" &$R) Global $Name = _Excel_RangeRead($oWorkbook, Default, "B" &$R) Global $Strasse = _Excel_RangeRead($oWorkbook, Default, "C" &$R) Global $Plz = _Excel_RangeRead($oWorkbook, Default, "D" &$R) Global $Ort = _Excel_RangeRead($oWorkbook, Default, "E" &$R) Global $Late = _Excel_RangeRead($oWorkbook, Default, "I" &$R) Global $Lone = _Excel_RangeRead($oWorkbook, Default, "J" &$R) Global $Lati = String ($Late) Global $Loni = String ($Lone) Global $Lona = StringRegExpReplace ( $Loni , "\ r \ n | \ r | \ n" , "" ) Global $Lata = StringRegExpReplace ( $Late , "\ r \ n | \ r | \ n" , "" ) ; String $SText für Lat und Lon Global $SText = "" &@CRLF FileWrite ( $GPXdatei , $SText ) ; String $AText ist Adresse Global $AText = "" &$Nummer &"; " &$Name &"; " &$Plz &" "&$Ort &"" &@CRLF FileWrite ( $GPXdatei , $AText ) ; String $ZText für zwischen den Kunden Global $ZText = "" FileWrite ( $GPXdatei , $ZText ) Next ; String $Etext für Abschlußtext $EText = "" &@CRLF $EText &= "" &@CRLF FileWrite ( $GPXdatei , $EText ) ;Datei AATour.pgx schließen FileClose ( $GPXdatei ) ;Datei nach ITN Converter ShellExecute( $Zieldatei ) Converter() ; ITN Converter Funtion EndFunc Func Converter() ; ITN Converter Fenster WinActivate("ITN Converter", "") WinWaitActive("ITN Converter","") Global $hWnd = WinGetHandle("ITN Converter") ControlClick($hWnd, "", "[CLASS:ComboBox; INSTANCE:1]") ; Exportieren Button ControlClick($hWnd, "", "[Class:Button;Instance:3]") ; Fenster Exportieren WinActivate("Exportieren", "") WinWaitActive("Exportieren","") Global $hWnd = WinGetHandle("Exportieren") Sleep (500) ControlClick($hWnd, "", "[Class:Button;Instance:2]") ; ITN Converter schließen Global $hWnd = WinGetHandle("ITN Converter") ControlClick($hWnd, "", "[Class:Button;Instance:1]") ; Tom Tom Link zum einfügen in Navi öffnen Global $IE = _IECreate("http://169.254.255.1/pcmi/?locale=de_de") _IELoadWait($IE) Sleep (1000) EndFunc Func Dateien_loeschen() FileDelete ("AATour.gpx") FileDelete ("Koordinaten.XML") FileDelete ("AATour.ov2") EndFunc