Get the value of last cell used in row

[water]

This script works just fine for me for entries with multiple, single or no Client Machines. Speed can be enhanced if needed

What's missing in this example is some kind of error checking. But with the code I posted so get the message faster

#include <Excel.au3>

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\test.xlsx")
Global $aWorksheet = _Excel_RangeRead($oWorkbook)
_ArrayDisplay($aWorksheet)
For $i = 1 To UBound($aWorksheet, 1) - 1
    $aMaschines = StringSplit($aWorksheet[$i][2], ",")
    If @error Then ; No separator found
        If $aMaschines[1] <> "" Then ; Client machine found
            _Excel_RangeWrite($oWorkbook, Default, $aMaschines[1], "D" & $i+1)
        EndIf
    Else
        _Excel_RangeWrite($oWorkbook, Default, $aMaschines[$aMaschines[0]], "D" & $i+1)
    EndIf
Next

 

Edited July 13, 2016 by water

[antmar904]

@water

 

The script runs then exit and does nothing.

Here is a snippit of the excel file.

 

LNTest.xlsx

[water]

I grabbed a screenshot of your workbook that consisted of 4 columns.
This now works with your example file:

#include <Excel.au3>

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\LNtest.xlsx")
Global $aWorksheet = _Excel_RangeRead($oWorkbook)
Global $hSplashtext = SplashTextOn("Progress", "Records processed ...", -1, 50)
For $i = 1 To UBound($aWorksheet, 1) - 1
    Sleep(50)
    If Mod($i, 10) = 0 Then ControlSetText($hSplashtext, "", "Static1", "Records processed ... " & $i)
    $aMaschines = StringSplit($aWorksheet[$i][1], ",")
    If @error Then ; No separator found
        If $aMaschines[1] <> "" Then ; Client machine found
            _Excel_RangeWrite($oWorkbook, Default, $aMaschines[1], "D" & $i + 1)
        EndIf
    Else
        _Excel_RangeWrite($oWorkbook, Default, $aMaschines[$aMaschines[0]], "C" & $i + 1)
    EndIf
Next
SplashOff()

 

Edited July 13, 2016 by water

[antmar904]

@water

 

It worked your a genius.  I took about 18 min to complete with 32176 rows. 

[water]

This one is even faster:

#include <Excel.au3>

Global $iTimer = TimerInit()
Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\LNtest.xlsx")
Global $aWorksheet = _Excel_RangeRead($oWorkbook)
Global $hSplashtext = SplashTextOn("Progress", "Records processed ...", -1, 50)
Global $aTemp[UBound($aWorksheet, 1) - 1]
For $i = 1 To UBound($aWorksheet, 1) - 1
    If Mod($i, 10) = 0 Then ControlSetText($hSplashtext, "", "Static1", "Records processed ... " & $i)
    $aMaschines = StringSplit($aWorksheet[$i][1], ",")
    If @error Then ; No separator found
        If $aMaschines[1] <> "" Then ; Client machine found
            $aTemp[$i - 1] = $aMaschines[1]
        EndIf
    Else
        $aTemp[$i - 1] = $aMaschines[$aMaschines[0]]
    EndIf
Next
_Excel_RangeWrite($oWorkbook, Default, $aTemp, "C2")
SplashOff()
MsgBox(0, "Info", "Processing time: " & TimerDiff($iTimer))

Takes 4.3 seconds to process 32176 records compared to the previous version which took 460 seconds here

[antmar904]

Wow 5 sec now!  Thank a million.

Time to look over the script to understand it some more.

[water]

The slower script calls _Excel_RangeWrite for every cell, the faster stores all values in a temporary array and then writes all cells with a single call

Edited July 13, 2016 by water