Sign in to follow this  
Followers 0
symadis

Loop until the last excel line

5 posts in this topic

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:

$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

Share this post


Link to post
Share on other sites



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.


[size="1"][font="Arial"].[u].[/u][/font][/size]

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

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.


Lofting the cyberwinds on teknoleather wings, I am...The Blue Drache

Share this post


Link to post
Share on other sites

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.


[size="1"][font="Arial"].[u].[/u][/font][/size]

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
Sign in to follow this  
Followers 0