Kait0 Posted May 8 Share Posted May 8 Hello im trying to define input based on Column A and B from the excel list and if something dont have definition i want to open other excel file and write it down. long story short its not going so well..... any idea what would be a clean solution for it ? expandcollapse popup#include <Excel.au3> #include <MsgBoxConstants.au3> #include <WinAPIFiles.au3> ; MsgBox file delete Msgfiledel() Func Msgfiledel() $aBox = MsgBox(4, "Achtung!", "Soll Undefiniert liste Geloscht Werden ?") If $aBox = 6 Then Udelete() ElseIf $aBox = 7 Then MsgBox(0, "Nein", "Liste loschen Ubersprungen") EndIf EndFunc Func Udelete() ; Löscht die temporäre Datei. Local $idDelete = FileDelete("Undefiniert.xlsx") ; Zeigt eine Nachricht, ob die Datei gelöscht wurde. If $idDelete Then MsgBox($MB_SYSTEMMODAL, "", "Die Datei wurde erfolgreich gelöscht.") Else MsgBox($MB_SYSTEMMODAL, "", "Es ist während dem löschen der Datei ein Fehler aufgetreten.") EndIf EndFunc ; MsgBox wawi status MsgWawi() Func MsgWawi() $aBox = MsgBox(4, "Achtung!", "Wawi an und auf verkauf engestellt ?") If $aBox = 6 Then MsgPrint() ElseIf $aBox = 7 Then MsgBox(0, "Nein", "Bitte Wawi vorbereiten und noch mal starten") EndIf EndFunc ; MsgBox Printer status Func MsgPrint() $aBox = MsgBox(4, "Achtung!", "Drucker auf virtuel Umgestellt ?") If $aBox = 6 Then Engabe1() ElseIf $aBox = 7 Then MsgBox(0, "Nein", "Bitte in dosprint drucker umstellen und noch mal starten") EndIf EndFunc ; Open Excel, Pick sheet, Close Excel Func Engabe1() Local $sValue = InputBox("Tag Auswal", "Bitte ein tag fur versand eingeben", "", " M2") MsgBox($MB_SYSTEMMODAL, "Info", "Du Hast Tag " & $sValue & " Ausgwwählt") Local $oExcel = _Excel_Open(False) Local $oBook = _Excel_BookOpen($oExcel, @ScriptDir & '\Aktuell.xls', True, False) Global $aData = _Excel_RangeRead($oBook, $sValue, 'A3:E164', 2, True) _Excel_Close($oExcel) ; Switch focus to WAWI WinActivate("[title:MS-DOS Prompt -]", "") Send('{ENTER}') ; first looop Input Local $bSplit For $Index = 0 To UBound($aData) - 1 If Not $aData[$Index][1] Then ContinueLoop If $aData[$Index][1] = "232109-sz" Then Send( '{i}' & '{2}' & '{3}' & '{ENTER}' & '{2}' & '{1}' & '{0}' & '{9}' & '{ENTER}' & '{s}' &'{z}' & '{ENTER}') ;Sleep ( 3500 ) Send('{UP}' & $aData[$Index][3] & '{ENTER}' & '{ENTER}' & '{END}' & '{ENTER}' & '{v}') If $aData[$Index][1] = "172100-59" Then Send( '{i}' & '{1}' & '{7}' & '{ENTER}' & '{2}' & '{1}' & '{0}' & '{0}' & '{ENTER}' & '{5}' &'{9}' & '{ENTER}') ;Sleep ( 3500 ) Send('{UP}' & $aData[$Index][3] & '{ENTER}' & '{ENTER}' & '{END}' & '{ENTER}' & '{v}') Else If $aData[$Index][1] <> "172100-59" Or $aData[$Index][1] <> "232109-sz" Then Local $sExcel = _Excel_Open() Local $wbook = _Excel_BookOpen($sExcel, @ScriptDir & '\Test.xlsx') Local $NextRow = $wbook.Activesheet.Usedrange.Rows.Count + 1 _Excel_RangeWrite($wbook, $wbook.Activesheet, $aData[$Index][1], "A" & $NextRow) EndIf EndIf EndIf Next EndFunc Aktuell.xls Link to comment Share on other sites More sharing options...
donnyh13 Posted May 8 Share Posted May 8 (edited) I'm not an Excel expert (I don't really use it) but I notice a couple of things in your script. 1: Local $sValue = InputBox("Tag Auswal", "Bitte ein tag fur versand eingeben", "", " M2") If you are entering digits here, you must force them to be digits before using them in RangeRead _Excel_RangeRead($oBook, Int($sValue), 'A3:E164', 2, True) 2. If $aData[$Index][1] <> "172100-59" Or $aData[$Index][1] <> "232109-sz" Then isn't needed because of my following point. 3. See notes in code. If $aData[$Index][1] = "232109-sz" Then .... If $aData[$Index][1] = "172100-59" Then ;<---- This should be "ElseIf" otherwise the above If has to be true before this will be tested. ..... Else If $aData[$Index][1] <> "172100-59" Or $aData[$Index][1] <> "232109-sz" Then ;<---- Comment this out, will be already tested with above "Ifs". EndIf EndIf; <---- Comment this out. EndIf;<---- Comment this out 4. The file '\Test.xlsx' would not work for me until I changed it to '\Test.xls' (But I am using Word 2010, so you might need to change it back) 5. You might want to move Local $sExcel = _Excel_Open() Local $wbook = _Excel_BookOpen($sExcel, @ScriptDir & '\Test.xls') outside of the loop, otherwise you will keep opening the file, and for that matter, all variable declarations too. Try this: expandcollapse popup#include <Excel.au3> #include <MsgBoxConstants.au3> #include <WinAPIFiles.au3> ; MsgBox file delete Msgfiledel() Func Msgfiledel() $aBox = MsgBox(4, "Attention!", "Should undefined list be deleted?") If $aBox = 6 Then Udelete() ElseIf $aBox = 7 Then MsgBox(0, "No", "Clear List Skipped") EndIf EndFunc Func Udelete() ; Deletes the temporary file. Local $idDelete = FileDelete("Undefined.xlsx") ; Shows a message whether the file was deleted. If $idDelete Then MsgBox($MB_SYSTEMMODAL, "", "The file was deleted successfully.") Else MsgBox($MB_SYSTEMMODAL, "", "An error occurred while deleting the file.") EndIf EndFunc ; MsgBox wawi status MsgWawi() Func MsgWawi() $aBox = MsgBox(4, "Attention!", "Wawi on and for sale?") If $aBox = 6 Then MsgPrint() ElseIf $aBox = 7 Then MsgBox(0, "No", "Please prepare Wawi and start again") EndIf EndFunc ; MsgBox Printer status Func MsgPrint() $aBox = MsgBox(4, "Attention!", "Printer switched to virtual?") If $aBox = 6 Then Entry1() ElseIf $aBox = 7 Then MsgBox(0, "No", "Please switch to dosprint printer and start again") EndIf EndFunc ; Open Excel, Pick sheet, Close Excel Func Engabe1() Local $sValue = InputBox("Tag Select", "Please enter a day for shipping", "", " M2") MsgBox($MB_SYSTEMMODAL, "Info", "You Have Tag " & $sValue & " Selected") Local $oExcel = _Excel_Open(False) Local $oBook = _Excel_BookOpen($oExcel, @ScriptDir & '\Aktuell.xls', True, False) Global $aData = _Excel_RangeRead($oBook, Int($sValue), 'A3:E164', 2, True) _Excel_Close($oExcel) ; Switch focus to WAWI WinActivate("[title:MS-DOS Prompt -]", "") Send('{ENTER}') Local $sExcel = _Excel_Open() Local $wbook = _Excel_BookOpen($sExcel, @ScriptDir & '\Test.xls') Local $NextRow = $wbook.Activesheet.Usedrange.Rows.Count + 1 ; first loop input Local $bSplit For $Index = 0 To UBound($aData) - 1 If Not $aData[$Index][1] Then ContinueLoop If $aData[$Index][1] = "232109-sz" Then Send( '{i}' & '{2}' & '{3}' & '{ENTER}' & '{2}' & '{1}' & '{0}' & '{9}' & '{ENTER}' & '{s}' &'{z}' & '{ENTER}') ;Sleep ( 3500 ) Send('{UP}' & $aData[$Index][3] & '{ENTER}' & '{ENTER}' & '{END}' & '{ENTER}' & '{v}') ElseIf $aData[$Index][1] = "172100-59" Then Send( '{i}' & '{1}' & '{7}' & '{ENTER}' & '{2}' & '{1}' & '{0}' & '{0}' & '{ENTER}' & '{5}' & '{9}' & '{ENTER}') ;Sleep ( 3500 ) Send('{UP}' & $aData[$Index][3] & '{ENTER}' & '{ENTER}' & '{END}' & '{ENTER}' & '{v}') Else ;~ If $aData[$Index][1] <> "172100-59" Or $aData[$Index][1] <> "232109-sz" Then _Excel_RangeWrite($wbook, $wbook.Activesheet, $aData[$Index][1], "A" & $NextRow) $NextRow += 1 EndIf ;~EndIf ;~EndIf Next EndFunc Edited May 8 by donnyh13 LibreOffice UDF ; Scite4AutoIt Spell-Checker Using LibreOffice Spoiler "Life is chiefly made up, not of great sacrifices and wonderful achievements, but of little things. It is oftenest through the little things which seem so unworthy of notice that great good or evil is brought into our lives. It is through our failure to endure the tests that come to us in little things, that the habits are molded, the character misshaped; and when the greater tests come, they find us unready. Only by acting upon principle in the tests of daily life can we acquire power to stand firm and faithful in the most dangerous and most difficult positions." Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now