symadis Posted September 22, 2007 Share Posted September 22, 2007 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 Link to comment Share on other sites More sharing options...
herewasplato Posted September 22, 2007 Share Posted September 22, 2007 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] Link to comment Share on other sites More sharing options...
symadis Posted September 22, 2007 Author Share Posted September 22, 2007 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) Link to comment Share on other sites More sharing options...
Blue_Drache Posted September 22, 2007 Share Posted September 22, 2007 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 Link to comment Share on other sites More sharing options...
herewasplato Posted September 22, 2007 Share Posted September 22, 2007 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] Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now