Sign in to follow this  
Followers 0
13lack13lade

Excel Loop/If help.

6 posts in this topic

#1 ·  Posted (edited)

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 by 13lack13lade

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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 by GMK

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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.

  
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 by 13lack13lade

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Glad to hear you solved your problem.

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

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