Jump to content
Sign in to follow this  
_leo_

RangeRead from active Excel cell

Recommended Posts

Hey guys :)
I'm new to autoit, so this could be a simple question. I'm trying to read the value from the currently selected cell in Excel. I read on the forum and tired to find videos, but I couldn't quite get to it. 

This is what I have got so far:

 

Local $oExcel_1 = _Excel_Open()

Local $var = "C:\Users\Acer\xy"

Local $oWorkbook = _Excel_BookOpen($oExcel_1,$var)

 

Local $_read1 = _Excel_RangeRead($oWorkbook, Default.Application.ActiveCell.Address)

 

Whatever I try, I either get an error or it only reads "0".

 

Thank you very much for any helpful thoughts!

Share this post


Link to post
Share on other sites

$var seems suspicious.  Should that be ended with .xls ? 

The _Excel_RangeRead is also bizarre.  What are trying to do exactly ?

Share this post


Link to post
Share on other sites

You need to use _Excel_BookAttach to read ActiveCell, if the workbook isn't opened you need to define the cell you want to read, for example:

#include <Excel.au3>
Local $bOpenWorkBook = False, $oExcel = _Excel_Open()
Local $sFilePath = @ScriptDir & "\Test.xlsx"

Local $oWorkbook
$oWorkbook = _Excel_BookAttach($sFilePath)
If @error Then
    $oWorkbook = _Excel_BookOpen($oExcel, $sFilePath)
    $bOpenWorkBook = True
 EndIf

If $bOpenWorkBook Then
    $sCell = _Excel_RangeRead($oWorkbook, Default, "A20") ;~ Workbook was opened so read cell A2
Else
    $sCell = _Excel_RangeRead($oWorkbook, Default, $oExcel.ActiveCell.Address) ;~ Workbook was already open, read the active cell.
EndIf
MsgBox(4096, "Excel Cell Value", "Value = " & $sCell)

 

Share this post


Link to post
Share on other sites
23 hours ago, Subz said:

You need to use _Excel_BookAttach to read ActiveCell, if the workbook isn't opened you need to define the cell you want to read, for example:

#include <Excel.au3>
Local $bOpenWorkBook = False, $oExcel = _Excel_Open()
Local $sFilePath = @ScriptDir & "\Test.xlsx"

Local $oWorkbook
$oWorkbook = _Excel_BookAttach($sFilePath)
If @error Then
    $oWorkbook = _Excel_BookOpen($oExcel, $sFilePath)
    $bOpenWorkBook = True
 EndIf

If $bOpenWorkBook Then
    $sCell = _Excel_RangeRead($oWorkbook, Default, "A20") ;~ Workbook was opened so read cell A2
Else
    $sCell = _Excel_RangeRead($oWorkbook, Default, $oExcel.ActiveCell.Address) ;~ Workbook was already open, read the active cell.
EndIf
MsgBox(4096, "Excel Cell Value", "Value = " & $sCell)

 

Thank you very much for the example! Yeah, the BookAttach was missing.

As I corrected my script, I came up with the question, if it was possible to read from a cell near the active cell. With an offset function for example:

Local $_read2 = _Excel_RangeRead($oWorkbook, Default, $oExcel.ActiveCell.Address.offset(2,2)) ?

Is this possible with Autoit?

 

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  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By AnonymousX
      Hello,
      I'm struggling and not sure what I'm missing with how to use the _Excel_RangeRead function.
      Based on: https://www.autoitscript.com/autoit3/docs/libfunctions/_Excel_RangeRead.htm
      The second parameter should allow me to select different sheet names, however I can't seem to get it to work.
      I was thinking something like this (see below) would be all I need to get an array for the sheet named as "test sheet". 
      $array = _Excel_RangeRead($oWorkbook, "test sheet")  
      Can someone please help tell me what I'm missing here?
       
      Thank you,
    • By goku200
      I'm having some issues with writing to column C when an element is found. It works on C2 but it does not continue to C3, C4, C5, etc..... I'm wanting to write "test" if the element //input[@id='username'] is found  $someUser = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//input[@id='username']"). I have attached my HTML and Excel file along with my AutoIt code below:
      #Include "wd_core.au3" #Include "wd_helper.au3" #Include "wd_core.au3" #Include "File.au3" #Include "Array.au3" #Include "Excel.au3" Local $sDesiredCapabilities, $sSession _WD_Startup() $Ssession = _WD_CreateSession($sDesiredCapabilities) _WD_Navigate($sSession, "https://127.0.0.1/test.html") _WD_LoadWait($sSession) Local $oExcel = _Excel_Open() Local $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\<Username>\Downloads\test.xlsx") Local $aArrayTest1 = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.ActiveSheet.Usedrange.Columns("A:A")) Local $aArrayTest2 = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.ActiveSheet.Usedrange.Columns("B:B")) For $i = 0 To UBound($aArrayTest1) - 1 _WD_Navigate($Ssession, $aArrayTest1[$i]) _WD_LoadWait($sSession) $someUser = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//input[@id='username']") _WD_SetElementValue($sSession, $someUser, $aArrayTest2[$i]) Local $sElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//input[@type='submit'][@value='Submit']") _WD_ElementAction($sSession, $sElement, 'click') _WD_LoadWait($sSession) Sleep(5000) If $someUser Then Local $aArray2D[2] = ["test"] _Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, $aArray2D, "C2") EndIf Next Func SetupChrome() _WD_Option('Driver', 'chromedriver.exe') _WD_Option('Port', 9515) _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"') $sDesiredCapabilities = '{"capabilities": {"alwaysMatch": {"goog:chromeOptions": {"w3c": true, "args":["start-maximized","disable-infobars"]}}}}' EndFunc ;==>SetupChrome  
      test.html test.xlsx
    • By kawumm3000
      Hi @water
      in my excel file it doesn't work because it counts also all the cells that a somehow formated. Can this also ignores formated cells and only counts cells with data?
      thanks
    • By dsm-sas
      Hi,
      I am looking for the possibility to extract a xls (not xlsx)-Excelfile to a csv . My problem: I do NOT have any Appliaction from MS-OFFICE. All searchings (since 3 days now) allways wants me to install ms-office or excel.
      I CAN'T DO THIS !!!
      Any help?!
      Ths'x alot
×
×
  • Create New...