Jump to content
Siryx

Excel / Array Problems

Recommended Posts

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

Share this post


Link to post
Share on other sites
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?

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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) ...

Share this post


Link to post
Share on other sites
Siryx
#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 by Siryx

Share this post


Link to post
Share on other sites
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 by water
  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
Siryx

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

Share this post


Link to post
Share on other sites
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.

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now

×