Excel / Array Problems

[Siryx]

expandcollapse popup

Local $oAppl = _Excel_Open()
If @error Then Exit
;MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oAppl, @ScriptDir & "\namen und emails.xlsx")
If @error Then
    MsgBox(16, "", "ALLES KAPUTT")
    ;MsgBox($MB_SYSTEMMODAL, "Reading of Excel File Failed", "Error opening workbook")
    _Excel_Close($oAppl)
    Exit
EndIf


Sleep(3000)
$array = _Excel_RangeRead($oWorkbook, "Tabelle1", "A2:A789")
;Variablen =    pos = position im String von links
;               pos2 = position im String von rechts
;               count = Koordinate im Array / A2 = 1
While 1
$array = _Excel_RangeRead($oWorkbook, "Tabelle1", "A2:A789")
For $count = 1 To UBound($array) - 1
    $pos = StringInStr($array[$count], ",", 0, 1)
    If $pos <> 0 Then
        $name1 = StringTrimLeft($array[$count], $pos)
        $pos2 = StringLen($array[$count]) - $pos + 1 ;+1 ist für das Komma das
        $name2 = StringTrimRight($array[$count], $pos2)
        Sleep(200)
        MouseClick("", 48, 165, 2, 0)
        Sleep(200)
        Send("A" & $count + 2 & "{ENTER}" & $name1&"{ENTER}")
        Sleep(200)
        MouseClick("", 48, 165, 2, 0)
        Sleep(200)
        Send("A" & $count + 3 & "{ENTER}" & $name2&"{ENTER}")
    EndIf
    $count = $count + 1

Next
WEnd

Hey guys.. first of all, what I like to do. I have an excel file with names in it. It looks like this

 

Name, Name

(empty)

Name

Name

Name,Name,Name

(empty)

(empty)

Name

 

I wanted to write a program to check if there is a comma in the excel field, then divide the value at the comma and put them into the already written field and into the empty one below. It doesnt work really good and it leaves some spaces open .. excel rangewrite doesnt work though.. i dont know why

Edited July 1, 2015 by Siryx

[water]

A few questions - Ein paar Fragen.

Why do you call _Excel_RangeRead twice - Warum rufst Du _Excel_RangeRead zwei Mal auf?
What is the While/WEnd loop for? - Wofür ist die While/WEnd Schleife gut?
And how do you exit the loop - Und wie willst Du die Schleife jemals verlassen?
Why didn't  _Excel_RangeWrite work - Warum funktioniert _Excel_RangeWrite nicht?
What is the value of @error after  _Excel_RangeWrite - Welchen Wert aht @error nach _Excel_RangeWrite?

[Siryx]

I forgot to erase the first time when I defined the array outside the loop.. The While/WEnd because after the first time I did the For $n = 1 to UBound($array) not every entry was edited, some were forgotten. I exit the loop with the Pause Key if I see everything is fininshed. I dont know how to get the @error and the range definition to write it was hard to code it was something like (...,"A:"&$count +2) ...

[Siryx]

expandcollapse popup

#include <Array.au3>
#include <MsgBoxConstants.au3>
#include <Excel.au3>


Local $oAppl = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "_Excel_Open Error", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oAppl, @ScriptDir & "\namen und emails.xlsx")
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Reading of Excel File Failed", "Error opening workbook")
    _Excel_Close($oAppl)
    Exit
EndIf


Sleep(3000)

;Variablen =    pos = position im String von links
;               pos2 = position im String von rechts
;               count = Koordinate im Array / A2 = 1

$array = _Excel_RangeRead($oWorkbook, "Tabelle1", "A2:A789")
For $count = 1 To UBound($array) - 1
    $pos = StringInStr($array[$count], ",", 0, 1)
    If $pos <> 0 Then
        $name1 = StringTrimLeft($array[$count], $pos)
        $pos2 = StringLen($array[$count]) - $pos + 1 ;+1 ist für das Komma da
        $name2 = StringTrimRight($array[$count], $pos2)
        _Excel_RangeWrite($oWorkbook, $vWorksheet, $name1, "A" & $count + 2 & "{ENTER}" & $name1 & "{ENTER}")
        If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error while using _Excel_RangeWrite." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_RangeWrite($oWorkbook, $vWorksheet, $name1, "A" & $count + 3 & "{ENTER}" & $name2 & "{ENTER}")
        If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error while using _Excel_RangeWrite." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    EndIf
    $count = $count + 1
Next
MsgBox(64,"Success","All values corrected.")




I will try this one tomorrow.

Edited July 1, 2015 by Siryx

[water]

How about this:

#include <Array.au3>
#include <MsgBoxConstants.au3>
#include <Excel.au3>

Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "_Excel_Open Error", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\namen und emails.xlsx")
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Reading of Excel File Failed", "Error opening workbook" & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

$aValues = _Excel_RangeRead($oWorkbook, "Tabelle1", "A2:A789")
For $iIndex = 0 To UBound($aValues) - 1
    $aSplit = StringSplit($aValues[$iIndex], ",")
    If @error = 0 Then
        _Excel_RangeWrite($oWorkbook, "Tabelle1", $aSplit[1], "A" & $iIndex + 1)
        If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error while using _Excel_RangeWrite." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        $iIndex = $iIndex + 1
        _Excel_RangeWrite($oWorkbook, "Tabelle1", $aSplit[2], "A" & $iIndex + 1)
        If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error while using _Excel_RangeWrite." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    EndIf
Next
MsgBox(64, "Success", "All values fixed.")

 

Edited July 2, 2015 by water

[Siryx]

Worked just perfect.. Well, I ran it as Admin but it worked without aswell.. maybe I did a stupid mistake using rangewrite.. Tyvm!

[water]

One of the mistakes is in the following line:

_Excel_RangeWrite($oWorkbook, $vWorksheet, $name1, "A" & $count + 2 & "{ENTER}" & $name1 & "{ENTER}")

You can't use "{ENTER}" teh way you use it. If you want a new line then use macro @CRLF.