Jump to content
Happy82

Excel _ExcelReadCell

Recommended Posts

Happy82

I am trying to read a specific cell value in Excel. In my Excel.au3 I don't have the function _ExcelReadCell. Is there any other way to read a specific cell or is there any download available for Excel.au3

Edited by Happy82

Share this post


Link to post
Share on other sites
BrewManNH

Use _Excel_RangeRead instead. And please pick a smaller font.

  • Like 1

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
JLogan3o13

Please tone down your font size, making my eyes bleed.

What version of AutoIt are you running? The current version would use _Excel_RangeWrite


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
Happy82

sure... i have reduced the font size..... I will use  _Excel_RangeRead. I using Autoit V3.3.12

Share this post


Link to post
Share on other sites
water

The Excel UDF has been rewritten. Please check the AutoIt history for Script Breaking Changes to see what has changed. 


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
Happy82

 

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

; Create application object and open a workbook
Local $oAppl = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_SheetList Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oAppl, @ScriptDir & "\Unassigned Credit Card Transactions (MAIN REPORT).xlsx")
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_SheetList Example", "Error opening workbook '" & @ScriptDir & "\Unassigned Credit Card Transactions (MAIN REPORT).xlsx'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oAppl)
    Exit
EndIf

; *****************************************************************************
; Display a list of all worksheets for a specific Workbook
; *****************************************************************************
Local $aWorkSheets = _Excel_SheetList($oWorkbook)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_SheetList Example 1", "Error listing Worksheets." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
;_ArrayDisplay($aWorkSheets, "Excel UDF: _Excel_SheetList Example 1")
For Local $nNames In $aWorkSheets
            Local $worksheetname = $nNames
            MsgBox(64,"Res", "The Name of worksheet :" & $worksheetname )
Next

 

This code does not go into for-loop.

All i am trying to do "Format the Range of cells in mutliple worksheet in a  workbook. My workbook has multiple worksheets.

I took this script form the Examples. Instead of displaying using _ArrayDisplay i am trying to use a For Next loop. Once I loop i will know the worksheet I am on to modify the cell ranges.

I am not sure I am doing the right way. Since the variable is 2-d array it does not work in the for loop?

Edited by Happy82

Share this post


Link to post
Share on other sites
water

Use

For $nNames In $aWorkSheets

 


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
Happy82

I tried using the mentioned way, still it did not work. It did not do anything.

Edited by Happy82

Share this post


Link to post
Share on other sites
JLogan3o13

The function returns a 2D array. Try this. It works for me on a 25-sheet workbook.

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

Local $oAppl = _Excel_Open()
    If @error Then Exit ConsoleWrite("Error creating application object" & @CRLF)

Local $oWorkbook = _Excel_BookOpen($oAppl, @ScriptDir & "\Unassigned Credit Card Transactions (MAIN REPORT).xlsx")
    If @error Then Exit ConsoleWrite("Error creating workbook object" & @CRLF)


Local $aWorkSheets = _Excel_SheetList($oWorkbook)
    If @error Then Exit ConsoleWrite("Error enumerating worksheets" & @CRLF)


For $i = 0 To UBound($aWorkSheets) - 1
    MsgBox(64,"Res", "The Name of worksheet :" & $aWorkSheets[$i][0])
Next

 

  • Like 1

√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
Happy82

Thanks..... It works. :)

Edited by Happy82

Share this post


Link to post
Share on other sites
JLogan3o13

Glad to hear it. Again, very important to read the notes on each function to determine what it returns. In this case, big difference between 1D and 2D array, and how you handle them :)


√-1 2^3 ∑ π, and it was delicious!

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

    • MrCheese
      By MrCheese
      HI there
      this is driving me nuts - i get the row count, but not the column count - what am I missing? Thanks for your help!
      #include <Excel.au3> #include <Array.au3> #include <MsgBoxConstants.au3> ; XlDirection enumeration: https://msdn.microsoft.com/en-us/library/office/ff820880.aspxGlobal $oExcel = _Excel_Open() Global $xlup = -4162 Global $xlByRows, $xlPrevious, $xlByColumns Global $oExcel = _Excel_Open() $bookname = "temp.xlsx" $sWorkbook = @ScriptDir & "\" & $bookname Global $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, Default, Default, True) With $oWorkbook.ActiveSheet ; process active sheet $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) ; get a Range that contains the last used cells $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count ; get the the row count for the range starting in row/column 1 and ending at the last used row/column $iColCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Column.Count MsgBox(0, "", "row:" & $iRowCount & "Col:" & $iColCount) EndWith  
      for context - i want to :
      * count columns used in excel
      * create ini file from the rows in each column - finishing at the last column used - i.e. one column for one ini file; containing 15 rows or so.
      is it better to read the entire sheet to an array via the sheettoarray function? then read it from that?
       
       
      temp.xlsx
    • AnonymousX
      By AnonymousX
      Sorry if this is a repost, but having some trouble searching for the answer and pretty tired right now.
      I'm just wondering how to make a border around a range of cells in Excel. I want the regular lines created by selecting "All Borders" option around F2:G3
      I saw this code:
      With $oExcel.ActiveSheet.range("F2:G3") .Select .Borders($xlEdgeBottom).LineStyle = $xlContinuous .Borders($xlEdgeBottom).Weight = $xlThick .Borders($xlEdgeBottom).ColorIndex = $xlAutomatic EndWith but I get errors of these variables not existing. It seems these no longer exist in the "#include <Excel.au3>"
      Is there another include file I need?
      I got this to work for highlighting cells, wondering if there is a option similar to this for all borders?
      $oExcel.ActiveSheet.Range("F2:G3").Interior.ColorIndex = 6 Thanks
    • YouriKamperman
      By YouriKamperman
      I am working on a script that will turn all file names in a directory into an array, and then writes this Array to an Excel file.
      This in itself is working, but the RangeWrite function always puts the Array count in the first cell.
      How can i make sure this does not happen? I can of course just filter it out in Excel, but i am trying to keep all logic of filtering text in my script.
      This is what my script looks like:
      Local $Yesterday = _DateAdd('d', -1, _NowCalcDate())
      Local $cDate = StringReplace($Yesterday, "/", "-")
      Local $aFileList = _FileListToArray(@WorkingDir & "/" & $cDate, "*")
      Local $oExcel = _Excel_Open()
      Local $oWorkbook = _Excel_BookNew($oExcel)
      _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aFileList)
    • Eminence
      By Eminence
      Hello,
      Is there a way wherein I can access the data from an array coming from an Excel file then have it assigned on to a variable?
      Below is a snippet of my current code. For now, it just reads and outputs the data from the excel file and have it displayed via an array.
      #include <Array.au3> #include <Excel.au3> #include <MsgBoxConstants.au3> Local $oExcel = _Excel_Open(False) If @error Then Exit MsgBox(0, "Error", "Error creating application object." & @CRLF & "Error: " & @error & " Extends: " & @extended) ; Open Excel Woorkbook and return object Local $sWorkbook = @ScriptDir & "\Excel Files\Test Data.xlsx" Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, False, True) If @error Then MsgBox(0, "Error", "Error opening workbook'" & $sWorkbook & ".'" & @CRLF & "Error: " & @error & "Extends: " & @extended) _Excel_Close($oExcel) Exit EndIf Local $aResult = _Excel_RangeRead($oWorkbook) ; Error Trapping If @error Then MsgBox(0, "Error", "Error reading data from '" & $sWorkbook & ".'" & @CRLF & "Error: " & @error & " Extends: " & @extended) _Excel_Close($oExcel) Exit EndIf _ArrayDisplay($aResult) My Excel file has values from Column A to H with values from 1 to 30, what I desired to do is have the value in "A7" assigned on to a variable. 
       
      Any help is appreciated. Thanks in advance.
    • JNutt
      By JNutt
      I am trying to close an excel file that was not opened with _Excel_Open.  How do I found the excel application object?  I'm new and I am used to files and folders names, so an 'object' is new to me.  I have the info too and simply spy, but I don't know which info is the object name/string.  In the example from help doc's I see the code below and I tried justin pasting it into Scite.
      <
      Local $oExcel1 = ObjCreate("Excel.Application")
      ; Close the Excel instance which was not opened by _Excel_Open
      ; (will still be running because it was not opened by _Excel_Open)
      _Excel_Close($oExcel1)
      If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_Close Example 1", "Error closing the Excel application." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
      Sleep(2000)
      Local $aProcesses = ProcessList("Excel.exe")
      MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_Close Example 1", "Function ended successfully." & @CRLF & @CRLF & $aProcesses[0][0] & " Excel instance(s) still running.")
      >
×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.