Jump to content
Morphice

copy excel column data to another program to paste them

Recommended Posts

Hello , 

I am new to autoIT, I am wondering if someone could guide me in the correct path for this program. Attached are the steps for the program as well as what I currently have. Any help is greatly appreciated . Thank You. 

* workbooks\sheets will be organized on per level basis , 1st sheet lvl 1 ,2nd sheet lvl 2 etc or workbook 1 = level 1, workbook 2 = level 2 etc. 

; = comments and reminders 

#include <MsgBoxConstants.au3>
#include <EditConstants.au3>
#include<excel.au3>
#include<Array.au3>

Global Const $PatientLookupX = 320
Global Const $PatientLookupY = 64 ; down 1 and enter

Global Const $PatientTextBoxX = 410
Global Const $PatientTextBoxY = 217  ; click , Ctrl + V , Enter

Global Const $PHMhubX = 512
Global Const $PHMhubY = 613   ;click down 1 enter button

Global Const $HealthRiskAssesmetX = 40
Global Const $HealthRiskAssesmetY = 162

Global Const $AddnewAssesmentX = 168
Global Const $AddnewAssesmentY = 98

Global Const $SelectAssesmentX = 342
Global Const $SelectAssesmentY = 98   ; Down 7 and enter Risk score new

Global Const $EmptyAnswerBarX = 465
Global Const $EmptyAnswerBarY = 145

Global Const $LowriskpreventionX = 716
Global Const $LowriskpreventionY = 324

Global Const $MediumriskPreventionX = 716
Global Const $MediumriskPreventionY = 352

Global Const $HighriskPreventionX = 716
Global Const $HighriskPreventionY = 377

Global Const $CatatrosphicPreventionX = 714
Global Const $CatatrosphicPreventionY = 399

Global Const $ClosebuttonX = 1167
Global Const $ClosebuttonY = 666

Global Const $SaveRiskButtonX = 1161
Global Const $SaveRiskButtonY = 692

Global Const $ExitCPScreenX = 1339
Global Const $ExitCPScreenY = 8

Global Const $ExitpatientHubX = 1000
Global Const $ExitpatientHubY = 79

Global Const $sleepMod = 2
Global Const $sleepVal = 5000*$sleepMod
Global Const $sleepLow = 200*$sleepMod
Global Const $sleepMed = 1000*$sleepMod
Global Const $sleepHigh = 3500*$sleepMod

;Function Open excel , read account number in column A
;-------------------------------------------------------------------------------------------------------------------------------------------------------
HotKeySet("{ESC}","stopbaby")

Func _WinWaitActivate($title,$text,$timeout=0)
    $hWnd = WinWait($title,$text,$timeout)
    If Not WinActive($title,$text) Then WinActivate($title,$text)
    WinWaitActive($title,$text,$timeout)
EndFunc

$i=0
While
    $i <=2
$i = $i+1
Local $Open_excel = _Excel_Open()
Local $File_path = "D:\AutoIT\Risk_Test.xlsx"
Local $Open_workbook = _Excel_BookOpen($Open_excel,$File_path)
WinActivate($Open_workbook)
Local $Read_account_number = _Excel_RangeRead($Open_workbook,default,"A" &$i)
_Excel_Close($Open_excel,False)
WEnd
;---------------------------------------------------------------------------------------------------------------------------------------------------

Func NavtoSearch()
    WinActivate(eClinicalWorks (Garcia,Erick)

    Sleep($sleepMed)
    MouseClick("",693,77)
    Send("!p") ; shortcut for patient menu
    Send("{DOWN}") ; down 1
    send ("{Enter}") ; patient lookup
    Sleep($sleepMed)
       ;paste account number  How would I do this???
    Send("{Enter}") ;Once patient is found, + enter = takes you to patient hub
    Sleep($sleepMed)
Next
NavtoPHMHub()
;-------------------------------------------------------------------------------------------------------------------------------------------------------
Func NavtoPHMHub()
    MouseClick("",$PHMhubX,$PHMhubY)
    Sleep($sleepLow)
    Send("{DOWN}")
    Send("{ENTER}") ;takes you to Care Plan HUB
Next
NavtoRiskScore()
;-------------------------------------------------------------------------------------------------------------------------------------------------------
Func NavtoRiskScore()
    MouseClick("",$HealthRiskAssesmetX,$HealthRiskAssesmetY) ;Clicks on HealthRisk assesment
    Sleep($sleepLow)
    MouseClick("",$AddnewAssesmentX,$AddnewAssesmentY) ; Click addnew assesment
    Sleep($sleepLow)
    MouseClick("",$SelectAssesmentX,$SelectAssesmentY) ;click select assesment tab
    Sleep($sleepLow)
    Send("{DOWN 7}")
    Send("{ENTER}")
    Sleep($sleepLow)
    MouseClick("",$EmptyAnswerBarX,$EmptyAnswerBarY) ;Click on Empty answer bar
    Sleep($sleepLow)
    MouseClick("",$LowriskpreventionX,$LowriskpreventionY) ; selects Risk Score, Change for other types 1-6
Next
NavtoNextPatient()

Func NavtoNextPatient
    MouseClick("",$ClosebuttonX,$ClosebuttonY)
    MouseClick("",$SaveRiskButtonX,$SaveRiskButtonY)
    MouseClick("",$ExitCPScreenX,$ExitCPScreenY)
    MouseClick("",$ExitpatientHubX,$ExitpatientHubY)
EndFunc
;Function should loop back to excel sheet, copy next account number, activate eclinicalworks, and repeat the steps
;---------------------------------------------------------------------------------------------------------------------------------------------------------
Func stopbaby()
   exit
EndFunc

Best Regards,

Morphice

steps for program.docx

Share this post


Link to post
Share on other sites

@Morphice

Instead of using mouse clicks and send keys, I suggest you to use Word UDF and Excel UDF, whi h you can find in the UDF secrion on the Forum.

To send the data to another application, then you can use Control* functions :)


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites

Hello F.DiMuro ,

 

I will be only using Excel and a program called eclinical works. My current problem is how to incrementally copy data that is in one column. and Paste it in another program, and continue doing this until there is no more data in that column. If you could provide me with some hints as to how to go about this? It would be greatly appreciated. Thank You. 

Share this post


Link to post
Share on other sites

@Morphice

The Help file is full of examples :)

Take a look at Excel_RangeCopyPaste() to copy your data from Excel to the Clipboard (third example).

From the Clipboard, then, you can manage your data as you want.

Take a look at AutoItWindowInfoTool to get the information about the control you are going to paste the data, using Control* functions or others.


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites

Hello, I am wondering if you could help me. The program works, except I do not know how to increment it when it goes back to excel. I am wondering if you could provide guidance on this? 

currently the program runs, clicks on cell A1 copies the information , sends esc to get rid of the dashed box in excel that states this cell has been selected. And runs through the rest of the program fine. But I do not know how to get it to step down 1 cell without clicking on cell A1 again. Any help is appreciated. 

#include <Excel.au3>
#include <File.au3>
#include <Array.au3>

Global Const $PatientLookupX = 320
Global Const $PatientLookupY = 64 ; down 1 and enter

Global Const $PatientTextBoxX = 410
Global Const $PatientTextBoxY = 217  ; click , Ctrl + V , Enter

Global Const $PHMhubX = 512
Global Const $PHMhubY = 613   ;click down 1 enter button

Global Const $HealthRiskAssesmetX = 40
Global Const $HealthRiskAssesmetY = 162

Global Const $AddnewAssesmentX = 168
Global Const $AddnewAssesmentY = 98

Global Const $SelectAssesmentX = 342
Global Const $SelectAssesmentY = 98   ; Down 7 and enter Risk score new

Global Const $EmptyAnswerBarX = 465
Global Const $EmptyAnswerBarY = 145

Global Const $LowriskpreventionX = 716
Global Const $LowriskpreventionY = 324

Global Const $MediumriskPreventionX = 716
Global Const $MediumriskPreventionY = 352

Global Const $HighriskPreventionX = 716
Global Const $HighriskPreventionY = 377

Global Const $CatatrosphicPreventionX = 714
Global Const $CatatrosphicPreventionY = 399

Global Const $ClosebuttonX = 1167
Global Const $ClosebuttonY = 666

Global Const $SaveRiskButtonX = 1161
Global Const $SaveRiskButtonY = 692

Global Const $ExitCPScreenX = 1339
Global Const $ExitCPScreenY = 8

Global Const $ExitpatientHubX = 1000
Global Const $ExitpatientHubY = 79

Global Const $sleepMod = 2
Global Const $sleepVal = 5000*$sleepMod
Global Const $sleepLow = 200*$sleepMod

Global Const $sleepMed = 1000*$sleepMod
Global Const $sleepHigh = 3500*$sleepMod

$nPatients = InputBox("Question","Number of patients?")
$jmp = InputBox("Question","Begin on what patient? (Default should be 1)")
$jmp = $jmp - 1
sleep(3000)

For $i = $jmp to $nPatients Step 1
WinActivate("Risk_Test")
Sleep(3000)
MouseClick("",61,236) ;clicks A1
sleep(3000)
Send("{CTRLDOWN}c{CTRLUP}")
Sleep($sleepLow)
Send("{ESC}")
sleep(3000)
   For $y = 0 To ($i-1) Step 1
      Send("{DOWN}")
      sleep($sleepLow)
   Next
   NavtoSearch()
Next
MsgBox($MB_SYSTEMMODAL,"","Program Complete!")


Func NavtoSearch()
WinActivate("eClinicalWorks (Garcia,Erick )  ")
    Sleep($sleepMed)
    MouseClick("",693,77)
    Send("!p") ; shortcut for patient menu
    Send("{DOWN}") ; down 1
    send ("{Enter}") ; patient lookup
    Sleep($sleepMed)
    Send("{CTRLDOWN}v{CTRLUP}")
    Send("{Enter}") ;Once patient is found, + enter = takes you to patient hub
    Sleep($sleepMed)
    ;start PHM HUb---------------------------------------------------------------------------------------------------------
    MouseClick("",$PHMhubX,$PHMhubY)
    Sleep($sleepLow)
    Send("{DOWN}")
    Send("{ENTER}") ;takes you to Care Plan HUB
    sleep($sleepMed)
    ;Start Nav to risk score ---------------------------------------------------------------------------------------------
    MouseClick("",$HealthRiskAssesmetX,$HealthRiskAssesmetY) ;Clicks on HealthRisk assesment
    Sleep($sleepMed)
    MouseClick("",$AddnewAssesmentX,$AddnewAssesmentY) ; Click addnew assesment
    Sleep($sleepMed)
    MouseClick("",$SelectAssesmentX,$SelectAssesmentY) ;click select assesment tab
    Sleep($sleepMed)
    Send("{DOWN 7}")
    Send("{ENTER}")
    Sleep($sleepMed)
    MouseClick("",$EmptyAnswerBarX,$EmptyAnswerBarY) ;Click on Empty answer bar
    Sleep($sleepMed)
    MouseClick("",$LowriskpreventionX,$LowriskpreventionY) ; selects Risk Score, Change for other types 1-6
    Sleep($sleepHigh)
    ;Start Next Patient-------------------------------------------------------------------------------------------------
    MouseClick("",$ClosebuttonX,$ClosebuttonY)
    Sleep($sleepMed)
    MouseClick("",$SaveRiskButtonX,$SaveRiskButtonY)
    Sleep($sleepMed)
    MouseClick("",$ExitCPScreenX,$ExitCPScreenY)
    Sleep($sleepMed)
    MouseClick("",$ExitpatientHubX,$ExitpatientHubY)
    Sleep($sleepMed)
EndFunc

;Func stopbaby()
 ;  exit
;EndFunc

 

Share this post


Link to post
Share on other sites

Use the _Excel* UDF instead of clicking and copying/pasting from the screen.  MouseClick and Send should be the last resort, not the first.


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites

@Morphice

Didn't you look at Excel UDF as I've already suggested you?


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites

@FrancescoDimuro

@BrewManNH

Sorry I did not attach the other part of the code.  It is copying cell A1 at the moment. I am having trouble on how to go about incremental copy the cell (column) + paste in another program. Sorry I should have uploaded this earlier. 

#include <Excel.au3>
#include <File.au3>
#include <Array.au3>



Local $oExcel=_Excel_Open()
Local $sWorkBook = "D:\AutoIT\Risk_Test.xlsx"
Local $oWorkBook = _Excel_BookOpen($oExcel,$sWorkBook)
WinActivate($oWorkBook)
sleep(3000)
$test = _Excel_RangeCopyPaste($oWorkBook.Activesheet,"A1")

 

Share this post


Link to post
Share on other sites

@FrancescoDiMuro

@BrewManNH

Below is the updated code

Progress- 

The code compiles and runs , however the loop doesn't stop because it can't get to the stop criteria. And I'm not too sure how to get the loop to increment to the next cell. Any help is appreciated. Thank you.  

#include <Excel.au3>
#include <File.au3>
#include <Array.au3>
HotKeySet("{ESC}","stopbaby")

Global Const $sleepMod = 2
Global Const $sleepVal = 5000*$sleepMod
Global Const $sleepLow = 200*$sleepMod

Global Const $sleepMed = 1000*$sleepMod
Global Const $sleepHigh = 3500*$sleepMod

Local $oExcel=_Excel_Open()
Local $sWorkBook = "D:\AutoIT\Risk_Test.xlsx"
Local $oWorkBook = _Excel_BookOpen($oExcel,$sWorkBook)
WinActivate($oWorkBook)
sleep(3000)

Do
WinActivate("Risk_Test")
$test = _Excel_RangeCopyPaste($oWorkBook.Activesheet,"A1")
sleep(3000)
 ;Send("{CTRLDOWN}c{CTRLUP}") ;Copy cell
 ;------------------------------------------------Excel statements above to manipulate data
WinActivate("eClinicalWorks (Garcia,Erick )  ")
Sleep($sleepMed)
    MouseClick("",693,77)
    Send("!p") ; shortcut
    Send("{DOWN}") ; down 1
    send ("{Enter}") ; patient lookup
    Sleep($sleepMed)
Send("{CTRLDOWN}v{CTRLUP}") ;Paste cell
Send("{Enter}") ;Once patient is found, + enter = takes you to patient hub
Sleep($sleepMed)
;--------------------------------------------------Eclinical workflow
WinActivate("Risk_Test") ;Go down a cell
Send("{ENTER}{ENTER}")
    until $test =""
;---------------------------------------Go back to excel and repeat the process, or put the process at the start $i +=[$a]


;for $i =0 to UBound($test)-1
; eclinical workflow---
;Next
; _Excel_Close($oWorkBook)


;Send("{CTRLDOWN}v{CTRLUP}")
;Run("D:\AutoIT\Risk_Test.xlsx")
;Send("{CTRLDOWN}v{CTRLUP}")


Func stopbaby()
   exit
EndFunc

 

Share this post


Link to post
Share on other sites

Version 2 

I tried using the Ubound feature, could you provide advice on which route to go with? Thank You. 

#include <Excel.au3>
#include <File.au3>
#include <Array.au3>
#include <MsgBoxConstants.au3>
HotKeySet("{ESC}","stopbaby")

Global Const $sleepMod = 2
Global Const $sleepVal = 5000*$sleepMod
Global Const $sleepLow = 200*$sleepMod

Global Const $sleepMed = 1000*$sleepMod
Global Const $sleepHigh = 3500*$sleepMod

Local $oExcel=_Excel_Open()
Local $sWorkBook = "D:\AutoIT\Risk_Test.xlsx"
Local $oWorkBook = _Excel_BookOpen($oExcel,$sWorkBook)
WinActivate($oWorkBook)
sleep(3000)
WinActivate("Risk_Test")

Local $testarray = _Excel_RangeCopyPaste($oWorkBook.Activesheet,"A1")
If @error Then Exit MsgBox(0, "", "Error when copying WorkbookIn. @error = " & @error)

for $i = 0 To UBound($testarray)-1
Next
WinActivate("eClinicalWorks (Garcia,Erick )")
    Sleep($sleepMed)
    MouseClick("",693,77)
    Send("!p") ; shortcut
    Send("{DOWN}") ; down 1
    send ("{Enter}") ; patient lookup
    Sleep($sleepMed)
Send("{CTRLDOWN}v{CTRLUP}") ;Paste cell
Send("{Enter}") ;Once patient is found, + enter = takes you to patient hub
Sleep($sleepMed)
if $testarray[$i]= "" Then MsgBox($MB_ICONERROR, "Error!!!", "Range A" & $i & " is Blank")


#cs
For $i = 0 to UBound($testarray, 1) - 1
If $testarray[$i]= " " then stopbaby()
Next
WinActivate("Test_RiskScore")
Send("{CTRLDOWN}v{CTRLUP}") ;Paste cell
Send("{Enter}")
Sleep($sleepMed)
_Excel_Close($oWorkBook)
#ce
 Func stopbaby()
   exit
EndFunc

 

Share this post


Link to post
Share on other sites

@Earthshine

Am I on the right track? Or do you suggest restarting it with a different approach?  The current program works , I require assistance with the loop function. 

Share this post


Link to post
Share on other sites

I think your problem resides in that line : 

Local $testarray = _Excel_RangeCopyPaste($oWorkBook.Activesheet,"A1")

If you read the help file :  Success:    the object of the target range if $vTargetRange <> Default, else 1.  So in all situations, the return value is NOT an array.  It is either an object or the value 1.  In your case it is the value 1.  And like @Earthshine said you should try to eliminate all send () commands and use the functions inside the UDF.

PS. winactivate () with an object is an error.  If you test the result, you will see it...

Edited by Nine

Share this post


Link to post
Share on other sites

Using _Excel_RangeCopyPaste copies to the clipboard so you would need to ClipGet() to obtain the information.  What you probably want to use is _Excel_RangeRead, you can then use this within a loop (Only if you use more than 1 cell, if you only point it at A1 for example it will only return the string not an array).

Correct me if I'm wrong but "eClinicalWorks (Garcia,Erick )" is a third-party program, if so, using the Autoit WIndow Info Tool (installed with Autoit), point it at "eClinicalWorks (Garcia,Erick )" and get the Control id information, then use ControlSend to send the data to each field.

Just for reference, in your code above you would need to move the "Next" below the line "if $testarray[$i]=..." otherwise it wouldn't loop, although as I mentioned above it wouldn't work because you were copying the data to clipboard so the "For $i = 0 To Ubound($testarray[$i]) - 1" wouldn't work as the latter wasn't an array.

Share this post


Link to post
Share on other sites
2 hours ago, Morphice said:

@Earthshine

Am I on the right track? Or do you suggest restarting it with a different approach?  The current program works , I require assistance with the loop function. 

just look at @Subz and @Nine comments above me here.

Edited by Earthshine

My resources are limited. You must ask the right questions

 

Share this post


Link to post
Share on other sites

@Subz did summarize it well.  Instead of using a single cell "A1", try using the whole range for your needs like "A1:A20" :

Local $aRange = _Excel_RangeRead ($sWorkBook,Default,"A1:A20")

 That way you would create an array of 20 elements. Then you can use a loop to transfert information directly from the array without having to read the clipboard...

Share this post


Link to post
Share on other sites

@Nine @Subz

Hello, Below is the updated code. The process of the third party application (eclinicalWorks ) functions . However I'm still stuck on how to create a loop for an incremental copy paste of cells and paste into the appropriate field in the application.  If you could provide feedback on this, it would be greatly appreciated. 

#include <Excel.au3>
#include <File.au3>
#include <Array.au3>
#include <MsgBoxConstants.au3>

HotKeySet("{ESC}","stopbaby")

Global Const $PatientLookupX = 320
Global Const $PatientLookupY = 64 ; down 1 and enter

Global Const $PatientTextBoxX = 410
Global Const $PatientTextBoxY = 217  ; click , Ctrl + V , Enter

Global Const $PHMhubX = 512
Global Const $PHMhubY = 613   ;click down 1 enter button

Global Const $HealthRiskAssesmetX = 40
Global Const $HealthRiskAssesmetY = 162

Global Const $AddnewAssesmentX = 168
Global Const $AddnewAssesmentY = 98

Global Const $SelectAssesmentX = 342
Global Const $SelectAssesmentY = 98   ; Down 7 and enter Risk score new

Global Const $EmptyAnswerBarX = 465
Global Const $EmptyAnswerBarY = 145

Global Const $LowriskpreventionX = 716
Global Const $LowriskpreventionY = 324

Global Const $MediumriskPreventionX = 716
Global Const $MediumriskPreventionY = 352

Global Const $HighriskPreventionX = 716
Global Const $HighriskPreventionY = 377

Global Const $CatatrosphicPreventionX = 714
Global Const $CatatrosphicPreventionY = 399

Global Const $ClosebuttonX = 1167
Global Const $ClosebuttonY = 666

Global Const $SaveRiskButtonX = 1161
Global Const $SaveRiskButtonY = 692

Global Const $ExitCPScreenX = 1339
Global Const $ExitCPScreenY = 8

Global Const $ExitpatientHubX = 1000
Global Const $ExitpatientHubY = 79

Global Const $sleepMod = 2
Global Const $sleepVal = 5000*$sleepMod
Global Const $sleepLow = 200*$sleepMod
Global Const $sleepMed = 1000*$sleepMod
Global Const $sleepHigh = 3500*$sleepMod
;--------------------------------------Static Variables-----------------------------------------------------
Local $oExcel=_Excel_Open()
Local $sWorkBook = "D:\AutoIT\Risk_Test.xlsx"
Local $oWorkBook = _Excel_BookOpen($oExcel,$sWorkBook)
WinActivate($oWorkBook)
WinActivate("Risk_Test")

;-------------------------------------Open workbook---------------------------------
Local $aRange = _Excel_RangeRead($oWorkBook,Default,"A1:A5")

for $i = 0 To UBound($aRange)-1 step +1
    Sleep($sleepMed)
    WinActivate("eClinicalWorks (Garcia,Erick )  ")
    Sleep($sleepMed)
    MouseClick("",693,77)
    Send("!p") ; shortcut
    Send("{DOWN}") ; down 1
    send ("{Enter}") ; patient lookup
    Sleep($sleepMed)
    Send("{CTRLDOWN}v{CTRLUP}")
    Send("{Enter}") ;Once patient is found, + enter = takes you to patient hub
    Sleep($sleepMed)
    MouseClick("",$PHMhubX,$PHMhubY)
    Sleep($sleepLow)
    Send("{DOWN}")
    Send("{ENTER}") ;takes you to Care Plan HUB
    MouseClick("",$HealthRiskAssesmetX,$HealthRiskAssesmetY) ;Clicks on HealthRisk assesment
    Sleep($sleepLow)
    MouseClick("",$AddnewAssesmentX,$AddnewAssesmentY) ; Click addnew assesment
    Sleep($sleepLow)
    MouseClick("",$SelectAssesmentX,$SelectAssesmentY) ;click select assesment tab
    Sleep($sleepLow)
    Send("{DOWN 7}")
    Send("{ENTER}")
    Sleep($sleepLow)
    MouseClick("",$EmptyAnswerBarX,$EmptyAnswerBarY) ;Click on Empty answer bar
    Sleep($sleepLow)
    MouseClick("",$LowriskpreventionX,$LowriskpreventionY) ; selects Risk Score, Change for other types 1-6
    MouseClick("",$ClosebuttonX,$ClosebuttonY)
    MouseClick("",$SaveRiskButtonX,$SaveRiskButtonY)
    MouseClick("",$ExitCPScreenX,$ExitCPScreenY)
    MouseClick("",$ExitpatientHubX,$ExitpatientHubY)
if $aRange[$i]= "" Then Exit
Next
MsgBox($MB_SYSTEMMODAL,"","Program Complete!")
;-------------------------------------------------------------------------------------------------------------------------------------------------------

 Func stopbaby()
   exit
EndFunc

ex 

Copy cell A1

run script

paste 

complete script

Copy call A2 

run script

paste 

complete script

etc... 

Share this post


Link to post
Share on other sites

@Morphice

You have been already suggested to use Control* and Win* functions, plus the Excel UDF to retrieve data from Excel, and interact later with your application.

In this way, you avoid to use Mouse* and Send functions, which are surely great functions, but when more reliability is needed, better use Win* and Control* functions :)

Have you already checked the AutoItWindowInfoTool?


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

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

  • Similar Content

    • By TheDcoder
      Hello everyone, I am working on a project which requires reading a few values from Excel, the catch is that I need it to be very fast... unfortunatley I found out that read operations using the supplied Excel UDF are very slow, more than 150 ms for each operation on average
      Here is my testing setup that I made:
      #include <Excel.au3> #include <MsgBoxConstants.au3> Global $iTotalTime = 0 Test() Func Test() Local $oExcel = _Excel_Open() Local $oBook = _Excel_BookAttach("Test.xlsx", "FileName", $oExcel) Local $sSheet = "Sheet1" If @error Then Return MsgBox($MB_ICONERROR, "Excel Failed", "Failed to attach to Excel") Local $iNum For $iRow = 1 To 6 Time() Local $iNum = Number(_Excel_RangeRead($oBook, $sSheet, "A" & $iRow)) If ($iNum = 1) Then ConsoleWrite("Row " & $iRow & " is 1 and value of column B is " & _Excel_RangeRead($oBook, $sSheet, "B" & $iRow)) Else ConsoleWrite("Row " & $iRow & " is not 1") EndIf ConsoleWrite(". Reading took: ") Time() Next ConsoleWrite("The whole operation took " & $iTotalTime & " milliseconds." & @CRLF) EndFunc Func Time() Local Static $hTimer Local Static $bRunning = False If $bRunning Then Local $iTime = Round(TimerDiff($hTimer), 2) $iTotalTime += $iTime ConsoleWrite($iTime & @CRLF) Else $hTimer = TimerInit() EndIf $bRunning = Not $bRunning EndFunc And Test.xlsx in CSV format:
      1,-1 -1,1 1,-1 1,1 -1,-1 1,1 Here is the actual xlsx but it should expire in a week: https://we.tl/t-EVkxGp1kc6
      And finally output from my script:
      Row 1 is 1 and value of column B is -1. Reading took: 276.06 Row 2 is not 1. Reading took: 163.36 Row 3 is 1 and value of column B is -1. Reading took: 302.58 Row 4 is 1 and value of column B is 1. Reading took: 294.65 Row 5 is not 1. Reading took: 152.33 Row 6 is 1 and value of column B is 1. Reading took: 284.92 The whole operation took 1473.9 milliseconds.  
      Taking ~1.5 seconds for reading 6 rows of data is bad for my script, which needs to run as fast as possible . It would be nice if I can bring this down to 100 ms somehow, I am not very experienced working with MS office so I thought about asking you folks for help and advice on how I can optimize my script to squeeze out every bit of performance that I can get from this script
       
      Thanks for the help in advance!
    • By SlackerAl
      I have been using some AutoIt scripts to manipulate Excel for a few weeks now. Today they stopped working. I have rebooted the PC and I'm not aware of any other significant changes. I can start and use Excel conventionally without a problem, but any attempt to create an excel object from AutoIt fails. E.g
      #include <MsgBoxConstants.au3> #include <Excel.au3> Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_Open Example 1", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Produces:
      @error = 1
      @extended = -2147221005
      I can't find that COM error listed anywhere... Anyone have any ideas?
    • By Eggie6
      Hi,
      I'm trying to write an formula to the excel, but it actually does not write anything into the cell, any ideas?
      $formula1="=MID(B"&$utakmice&",SEARCH("-",B"&$utakmice&")-1,1)" _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $formula1, "C"&$smth)  
    • By XinYoung
      Hello all,
      Preface: Column A is full of courses. Column B is full of usernames. If course (A1) exists, we check to see if username (B1) is enrolled. If user is found, the user is un-enrolled. Then Loop.
      I am working on a script that loops through an Excel file and pastes the content from A1 into a textbox in IE, does some stuff, then pastes the content from B1 into a different textbox. Then it loops around until all used rows in columns A and B have been accounted for.
      For some reason, column A loops properly but column B doesn't. B1 is pasted over and over again. So, as it loops, B1 is constantly being pasted, first accompanying A1's loop, then A2, and so on.
      The OpenExcel() func opens the Excel file the user specifies in an earlier function. It's supposed to gather the entire used range of columns A and B. The SearchCourse() func only uses column A, pasting its content into a Search tool in IE. This seems to be working fine. SearchResult() puts "Course Not Found" into column C if the search fails. If the search is successful, however, we move onto... EnterCourse(). This simply gets us to the place where column B's content comes into play. UnenrollNow(). Here, we paste the variable $_userName into a textbox. I don't know why it's always B1  Func OpenExcel() If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else $oExcel = _Excel_Open() Global $oWorkbook = _Excel_BookOpen($oExcel, $ChosenFileName, Default, True, True) $oExcel.Sheets("CopyCourses").Activate ;~ Get all used cells in columns A and B $aSearchItems = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.Sheets("CopyCourses").Usedrange.Columns("A:B")) ;~ Create the $aSearchResult array ReDim $aSearchResult[UBound($aSearchItems)] ;~ Loop through the array starting at 0 until the end of the array which is (Ubound($aSearchItems) - 1) For $i = 0 To UBound($aSearchItems) - 1 ;~ Column 0 ConsoleWrite($aSearchItems[$i][0] & @CRLF) ;~ Column 1 ConsoleWrite($aSearchItems[$i][1] & @CRLF) $aSearchResult[$i] = SearchCourse($aSearchItems[$i][0], $aSearchItems[$i][1]) Next _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") ;~ When the loop is complete, run the Finished function. Finished() EndIf EndFunc ;==>OpenExcel Func SearchCourse($_sSearchResult, $_userName) If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else _IENavigate($oIE, $urlBBCourseSearch) _IELoadWait($oIE) Local $oForm = _IEGetObjByName($oIE, "courseManagerFormSearch") Local $oSearchString = _IEFormElementGetObjByName($oForm, "courseInfoSearchText") _IEFormElementSetValue($oSearchString, $_sSearchResult) _IEFormSubmit($oForm) _IELoadWait($oIE) Local $oBBTable = _IETableGetCollection($oIE, 2) $aBBTableData = _IETableWriteToArray($oBBTable) Return SearchResult() EndIf EndFunc ;==>SearchCourse Func SearchResult() If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else Local $sResult $iSearchIndex = _ArraySearch($aBBTableData, "Course ID", 0, 0, 0, 1, 1, 0) If $iSearchIndex = -1 Then $sResult = "Course Not Found" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") Else EnterCourse() $sResult = "UnEnrolled!" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") EndIf Return $sResult EndIf EndFunc ;==>SearchResult Func EnterCourse() If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else Local $clickFail = "DATE CREATED" _IELinkClickByIndex($oIE, 34) _IELoadWait($oIE) $sourceCode = _IEBodyReadHTML($oIE) If StringInStr($sourceCode, $clickFail) <> 0 Then _IELinkClickByIndex($oIE, 35) _IELoadWait($oIE) EndIf $oLink = _IEGetObjById($oIE, "controlpanel.users.and.groups_groupExpanderLink") _IEAction($oLink, "click") Sleep(500) _IELinkClickByText($oIE, "Users") _IELoadWait($oIE) $aSearchResult[$i] = UnenrollNow($aSearchItems[$i][0], $aSearchItems[$i][1]) EndIf EndFunc ;==>EnterCourse Func UnenrollNow($_sourceCourseId, $_userName) If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else Local $UserError = "No users found" Local $sResult $criteriaUsername = _IEGetObjById($oIE, "userInfoSearchKeyString") _IEFormElementOptionSelect($criteriaUsername, 0, 1, 'byIndex') $criteriaUsername = _IEGetObjById($oIE, "userInfoSearchOperatorString") _IEFormElementOptionSelect($criteriaUsername, 0, 1, 'byIndex') ;Paste whats copied from column B into the Username text box. Local $oForm = _IEGetObjByName($oIE, "userManagerSearchForm") Local $oSearchString = _IEFormElementGetObjByName($oForm, "userInfoSearchText") ;PROBLEM HERE... _IEFormElementSetValue($oSearchString, $_userName) ;^^^^^^ WHY IS $_userName ALWAYS B1 ??? Sleep(1000) _IEFormSubmit($oForm) _IELoadWait($oIE) $sourceCode = _IEBodyReadHTML($oIE) If StringInStr($sourceCode, $UserError) <> 0 Then $sResult = "User Not Found" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") Return $sResult Else MsgBox(0, "Unenrollment READY!", "We're ready to unenroll foreal") $sResult = "DUN" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") Return $sResult EndIf EndIf EndFunc ;==>UnenrollNow Please let me know if any further information is needed.
      If you see other problems or redundancies in my code, please let me know.
      Thank you!
       
    • By SlackerAl
      I have an issue when starting Excel with the following code
      #include <Excel.au3> #include <GUIConstantsEx.au3> Opt("GUIOnEventMode", 1) GUICreate("Excel Test", 600, 440) GUISetOnEvent($GUI_EVENT_CLOSE, "MenuExit") GUISetState(@SW_SHOW) ; Create application object Local $oExcel = _Excel_Open() If @error Then Exit MsgBox(0, "Excel UDF: _Excel_RangeFind Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) ; sit here forever with an option to react every 10ms While 1 Sleep(10) WEnd Exit Func MenuExit() GUIDelete() Exit EndFunc If the Excel is a standard install, everything is OK. If Excel has the Kutools add-in (https://www.extendoffice.com/product/kutools-for-excel.html) installed and active the excel process runs (and is visible in task manager until killed), but it never displays. Disabling the add-in restores normal functionality.
      If I add some additional code to interact with the excel application then still nothing happens if the add-in is active. However, if Excel is started first and then the AutoIt code is run, it is able to interact with the Excel session as normal.
      Summary: The Excel add-in Kutools prevents excel being started with the _Excel_Open() command from AutoIt. Any AutoIt side work-arounds for this?
×
×
  • Create New...