# Excel / Array Problems

## Recommended Posts

```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)
;Variablen =    pos = position im String von links
;               pos2 = position im String von rechts
;               count = Koordinate im Array / A2 = 1
While 1
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 by Siryx

##### Share on other sites

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?

• 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:

##### Share on other sites

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) ...

##### Share on other sites
```#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

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 by Siryx

##### Share on other sites

```#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

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 by water
• 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:

##### Share on other sites

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

##### Share on other sites

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.

• 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:

## Create an account

Register a new account