Data Retrieval from Excel - (Moved)

[ShrutiW]

Hi all,

I'm trying to make an AutoIt script for our windows application ,in which I need to extract values from multiple cells in excel and use those values for inserting in the text boxes and combo boxes in the application. Can anyone please tell me how should I make it happen? 

[water]

I suggest you have a look at the Excel UDF that comes with AutoIt. Function _Excel_RangeRead should do what you are looking for.
If you need to extract a lot of cells it is best to read the whole worksheet into an array and then retrieve the needed information from the array - that's much faster compared to countless calls to _Excel_RangeRead

[Melba23]

Moved to the appropriate forum.

ShrutiW,

I am getting tired of moving your threads in to the correct section - you might find them vanishing in the future if you continue posting in inappropriate places.

M23

Edited March 18, 2019 by Melba23
Made it personal

[ShrutiW]

Hi @water, Thanks for the help, I have used excel UDF and now I am able to read the whole excel sheet.  But I am stuck in the next part, where I have to choose particular values from cells , say- Now I have to choose the row 1, I need values from cells- E1, F1, G1, P1 and R1 , and then retrieving these values and sending them to text boxes and combo boxes of another script.  I tried searching for it but no luck. Can you please help me in this please?

 

Just posting the screenshot for better understanding . As there are multiple columns with headings like member Id , Inquire for .etc., What I want is, I need to check the first row, in which I will check for member Id first , and then check for next columns i.e. Inquire for and inquired about, if those fields contains yes, then retrieve that member Id, and paste it in another script (in the textbox field). Next going further, I will check for Inquiry type and contact type, will retrieve the values in the cells and selecting the given value matching with the comboBox in another script. I am not getting any idea how to do it. 😑 Please help?

Edited March 20, 2019 by ShrutiW

[ShrutiW]

Hey @Melba23, Sorry for posting in the inappropriate place.. Could you please suggest  me a correct path to post my queries?

[water]

Let's say you have read the whole worksheet into an array named $aUsedRange (and the used range in the sheet starts with column "A") then you will find the data you mentioned in

$sE1 = $aUsedRange[0][4] ; Cell E1
$sF1 = $aUsedRange[0][5] ; Cell F1
...

 

[FrancescoDiMuro]

@ShrutiW

If you use _Excel_RangeRead() that reads the UsedRange from E to R columns, the function returns a 2-Dimension array, which you can iterate through, and extract only the values you're interested of.

On the other side, if you only need to extract 5 values (E1, F1, G1, P1 and R1), use _Excel_RangeRead() specifying the cells you want to read, and you're done.

18 minutes ago, ShrutiW said:

Could you please suggest  me a correct path to post my queries?

From the Forum Etiquette:

Quote

forum.

• AutoIt Specific
  • General Help and Support (Most support questions)
  • GUI Support (Support for the Graphical User Interface)
  • AutoItX Support (Support for the COM, DLL, PowerShell and .NET add-ons for AutoIt)
• Non-AutoIt
  • Chat
  • Developer Chat

[ShrutiW]

Hi @water, 

I tried using usedRange function, but I am not getting how do I send the parameters retrieved from the cells in the another script? I am not getting any clue.  I have written a draft code, Can you please suggest me changes on how do I send values in particular cells to another script, and how will call the values in the new script , I use those values at runtime? 😞 

; Read the formulas of a cell range (all used cells in column A:R)
Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("1:18"), 1)
_ArrayDisplay($aResult, "Excel UDF: _Excel_RangeRead Example 3 - Formulas in the worksheet.")
Local $sE1=0
$aUsedRange = _Excel_RangeRead($oWorkBook)
If $sE1 = $aUsedRange[0][17] Then
   Local $aResult1 = _Excel_RangeRead($oWorkbook, 1, "E2", 2)  
   ; Read the Member Id and send it to the text boxex in another script

ElseIf
$sF1 = $aUsedRange[0][5] And $sG1 = $aUsedRange[0][6] Then
Local $aResult2 = _Excel_RangeRead($oWorkbook, 1, "F1,G1", 2)
;If cell contains "Yes" then select a value from dropdown
EndIf

 

Edited March 27, 2019 by ShrutiW

[FrancescoDiMuro]

20 minutes ago, ShrutiW said:

how do I send the parameters retrieved from the cells in the another script?

Another AutoIt script?

If so, you could use Command Line parameters to pass values to the other script.

[water]

Or you could use IPC (Inter Process Communication) to pass data between two scripts.
UDFs to implement IPC can be found here: https://www.autoitscript.com/wiki/User_Defined_Functions#Inter_Process_Communications