Loop until the last excel line

[symadis]

Hello,

I would like to open a csv file in excel, and put the line's cell data into a separate variable, then execute a function.

The function repeat until there is no more lines in csv.

I have done this:

expandcollapse popup

$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 0
$oBooks = $oExcel.Workbooks.Open ("c:\SYMADIS\test.csv")
$oSheet = $oBooks.Worksheets (1)

$lines= ???? ;total lines
$line = ???? ;actual line

$reglement = $oSheet.Range ("A" & $line ).value
$reference = $oSheet.Range ("B" & $line ).value
$remarque = $oSheet.Range ("C" & $line ).value
$article = $oSheet.Range ("D" & $line ).value
$quantite = $oSheet.Range ("E" & $line ).value
$prixp = $oSheet.Range ("F" & $line ).value
$port = $oSheet.Range ("G" & $line ).value
$portht = $port / 1.196
$societe = $oSheet.Range ("H" & $line ).value
$societe = StringUpper($societe)
$adresse1 = $oSheet.Range ("I" & $line ).value
$adresse1 = StringUpper($adresse1)
$adresse2 = $oSheet.Range ("J" & $line ).value
$adresse2 = StringUpper($adresse2)
$cp = $oSheet.Range ("K" & $line ).value
$ville = $oSheet.Range ("L" & $line ).value
$ville = StringUpper($ville)

 

For $i = $lines to 1 Step -1
    
WinWait("MGE - TEST", "Nouveau bon de livra")
If Not WinActive("MGE - TEST", "Nouveau bon de livra") Then WinActivate("MGE - TEST", "Nouveau bon de livra")
WinWaitActive("MGE - TEST", "Nouveau bon de livra")

ControlClick("Liste des BL", "Nouveau", 1)
ControlSetText("MGE - TEST", "Nouveau bon de livraison *", "CamEdit43", $portht)
ControlSetText("MGE - TEST", "Nouveau bon de livraison *", "CamEdit14", $societe)
ControlSetText("MGE - TEST", "Nouveau bon de livraison *", "CamEdit20", $adresse1)
ControlSetText("MGE - TEST", "Nouveau bon de livraison *", "CamEdit19", $adresse2)
ControlSetText("MGE - TEST", "Nouveau bon de livraison *", "CamEdit17", $cp)
ControlSetText("MGE - TEST", "Nouveau bon de livraison *", "CamEdit16", $ville)
ControlSetText("MGE - TEST", "Nouveau bon de livraison *", "CamEdit30", $reference)
ControlSetText("MGE - TEST", "Nouveau bon de livraison *", "CamEdit29", $remarque)
ControlClick("Nouveau bon de livraison *", "OK", 1)

Next
???? ;change the excel line and re-do

But i don't know how to define the total lines, the actual line, and tell to excel to change the line each time.

Can somebody help me ?

Thank you

Symadis

[herewasplato]

Can AutoIt do all that you need to do to the CSV (text) file or is it just easier in Excel?

You might try using fileread and the string functions to work with that CSV file directly.

[symadis]

Thank you for fileread! With a StringSplit, i can separate each ";" delimited data.

Here is what i've done & it's working fine!:

$file = FileOpen("test.csv", 0)

While 1
      
    $line = FileReadLine($file)
    If @error = -1 Then ExitLoop
        
    $donnees = StringSplit($line, ";")
   
    $reglement = $donnees[1]
    $reference = $donnees[2]
    $remarque = $donnees[3]
    $article = $donnees[4]
    $quantite = $donnees[5]
    $prixp = $donnees[6]
    $port = $donnees[7]
    $portht = $port / 1.196
    $societe = $donnees[8]
    $societe = StringUpper($societe)
    $adresse1 = $donnees[9]
    $adresse1 = StringUpper($adresse1)
    $adresse2 = $donnees[10]
    $adresse2 = StringUpper($adresse2)
    $cp = $donnees[11]
    $ville = $donnees[12]
    $ville = StringUpper($ville)
Wend

FileClose($file)

[Blue_Drache]

There *IS* a way to do it in VB, but I can't be bothered to figure it out. Once again, try looking in The Extreme Visual Basic forums. They have a nice section on Excel and VBA (basically VB 6.0 lite) in the legacy section. There's several posts on your very subject and with the COM interface, it will take less time to convert it.

[herewasplato]

Thank you for fileread! With a StringSplit, i can separate each ";" delimited data.

Here is what i've done & it's working fine!:...

Great.

Glad to help.