define input based on excel sheet.

[Kait0]

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

[donnyh13]

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