Excel Loop/If help.

[13lack13lade]

Hi Guys!

 

I am using autoit with excel and want to skip blank rows in my excel read cell otherwise run the loop, i also want to run the loop upto a max 10000 lines.

 

The way i am thinking of doing it is:

 

If $TypCellValue = " " THEN skip row ELSE do my send commands.

 

but how do i tell autoit to skip or move to next row in excel?

 

Would it be something like this?

 

If $TypCellValue = "" then $i = 1 (+1) to 10000 ? or something like that

 

 

 

For $i = 1 To 10000 ;Loop
$TypCellValue = _ExcelReadCell($oExcel, $i, 1)
$CityCellValue = _ExcelReadCell($oExcel, $i, 2)
    $PrdCellValue = _ExcelReadCell($oExcel, $i, 3)
   Sleep(1000)
   WinActivate("program")


   Send("C") 
   Sleep(500)
   Send("{TAB}")
   Sleep(500)
   Send($TypCellValue) 
   Sleep(700)
   Send($CityCellValue)
   Sleep(700)
   Send($PrdCellValue)
   Sleep(1000)
   Send("{ENTER}")
   Sleep(700)

Edited August 6, 2013 by 13lack13lade

[GMK]

I would think it would be easier to just run your loop and only perform actions if the cell is not blank.

For $i = 1 To 10000 ;Loop
    $TypCellValue = _ExcelReadCell($oExcel, $i, 1)
    $CityCellValue = _ExcelReadCell($oExcel, $i, 2)
    $PrdCellValue = _ExcelReadCell($oExcel, $i, 3)
    If $TypCellValue <> "" Then
        Sleep(1000)
        WinActivate("program")
        Send("C")
        Sleep(500)
        Send("{TAB}")
        Sleep(500)
        Send($TypCellValue)
        Sleep(700)
        Send($CityCellValue)
        Sleep(700)
        Send($PrdCellValue)
        Sleep(1000)
        Send("{ENTER}")
        Sleep(700)
    EndIf
Next

Edited August 6, 2013 by GMK

[13lack13lade]

Thanks for your reply GMK however doesnt appear to like that at all, im not sure if its because of the other loop i have after it but it still does the same thing

 

seems like its missing the loop completely because some of the rows contain data and therefore skips straight to the second loop sending DN...

i just need it to skip down 1 row untill $typecellvalue contains anything.

  

expandcollapse popup

For $i = 1 To 10000 ;Loop
    $TypCellValue = _ExcelReadCell($oExcel, $i, 1)
    $CityCellValue = _ExcelReadCell($oExcel, $i, 2)
    $PrdCellValue = _ExcelReadCell($oExcel, $i, 3)
sleep (2000)
winactivate("Calypso Host - Test1")
  sleep (700)
    If $TypCellValue <> "" Then
        Send("C")
        Sleep(500)
        Send("{TAB}")
        Sleep(500)
        Send($TypCellValue)
        Sleep(700)
        Send($CityCellValue)
        Sleep(700)
        Send($PrdCellValue)
        Sleep(1000)
        Send("{ENTER}")
        Sleep(700)
EndIf


   
  If $TypCellValue = "H" Then
 Send("{TAB 4}")
  Else
 Send("{TAB 6}")
  EndIf
   
   Sleep(700)
   send("DN")
   Sleep(700)
   send("{ENTER}")
   Sleep(700)
   send("{LSHIFT}+{DEL}")
   Sleep(700)
   send("{ENTER}")
   sleep(700)
   send("{LSHIFT}+{DEL}")
   Sleep(700)
   send("{ENTER}")
   Sleep(700)
   send("{LSHIFT}+{DEL}")
   Sleep(700)
   send("{ENTER}")
   sleep(700)

Edited August 6, 2013 by 13lack13lade

[13lack13lade]

DW - made some VBA code to sort out this issue..

[GMK]

Glad to hear you solved your problem.

[water]

It is a good idea to NOT mix access to Excel (Word, Outlook etc.) via COM and the GUI. That's a nightmare to debug.

Everything (most) you can do via the GUI can be done using COM.