Jump to content
UEZ

Excel Range usage with Cells [solved]

Recommended Posts

UEZ

I'm stuck on how to use for range selection .Cells function.

Instead of

_Excel_RangeSort($oWorkbook, Default, Default, "AD:AD", Default, Default, $xlYes, False, Default, "AE:AE", Default, "L:L", Default)

and

$aResult = _Excel_RangeRead($oWorkbook, 1, "S2:AB" & $iRows)

which works properly I want to use Cells to select the range.

 

Why? Because the Excel sheet was modified and an additional row was inserted. I want to create the script more dynamically by selection it with Cells because I can search for the column headers.

Any idea?

 

Thanks.

Edited by UEZ

Please don't send me any personal message and ask for support! I will not reply!

Selection of finest graphical examples at Codepen.io

The own fart smells best!
Her 'sikim hıyar' diyene bir avuç tuz alıp koşma!
¯\_(ツ)_/¯  ٩(●̮̮̃•̃)۶ ٩(-̮̮̃-̃)۶ૐ

Share this post


Link to post
Share on other sites
water

$oWorkbook.ActiveSheet.Cells(x,y)
In the wiki you'll find a page where some special ranges are described.


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
UEZ

Thanks water!

$aResult = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.ActiveSheet.Range($oWorkbook.ActiveSheet.Cells(2, 18), $oWorkbook.ActiveSheet.Cells($iRows, 27)))

works for this particular line.

Does it works the same way also for _Excel_RangeSort?

 

Btw, is should be $oWorkbook.ActiveSheet.Cells(y,x)! ;)

 

Edited by UEZ

Please don't send me any personal message and ask for support! I will not reply!

Selection of finest graphical examples at Codepen.io

The own fart smells best!
Her 'sikim hıyar' diyene bir avuç tuz alıp koşma!
¯\_(ツ)_/¯  ٩(●̮̮̃•̃)۶ ٩(-̮̮̃-̃)۶ૐ

Share this post


Link to post
Share on other sites
water

All _Excel* functions accept a range object or an "A1" notation.
The following properties and methods return a range:


 

Edited by water

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
UEZ

Thanks water, I got it worked now.

;before
_Excel_RangeSort($oWorkbook, Default, Default, "AD:AD", Default, Default, $xlYes, False, Default, "AE:AE", Default, "L:L", Default)

;after
_Excel_RangeSort($oWorkbook, Default, Default, _
    $oWorkbook.ActiveSheet.Range($oWorkbook.ActiveSheet.Cells(2, $iPos_Shelf + 1), $oWorkbook.ActiveSheet.Cells($iRows, $iPos_Shelf + 1)), Default, Default, $xlYes, False, Default, _
    $oWorkbook.ActiveSheet.Range($oWorkbook.ActiveSheet.Cells(2, $iPos_Slot + 1), $oWorkbook.ActiveSheet.Cells($iRows, $iPos_Slot + 1)), Default, _
    $oWorkbook.ActiveSheet.Range($oWorkbook.ActiveSheet.Cells(2, $iPos_ModelType + 1), $oWorkbook.ActiveSheet.Cells($iRows, $iPos_ModelType + 1)), Default)



;before
$aResult = _Excel_RangeRead($oWorkbook, 1, "S2:AB" & $iRows)

;after
$aResult = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.ActiveSheet.Range( _
                            $oWorkbook.ActiveSheet.Cells(2, $iPos_Region + 1), _
                            $oWorkbook.ActiveSheet.Cells($iRows, $iPos_GN + 1)))

 

Outlook issue still ongoing... :think:

 

Edit:

_Excel_RangeSort($oWorkbook, Default, Default, _
    $oWorkbook.ActiveSheet.Range($oWorkbook.ActiveSheet.Cells(2, $iPos_Shelf + 1), $oWorkbook.ActiveSheet.Cells($iRows, $iPos_Shelf + 1)), Default, Default, $xlYes, False, Default, _
    $oWorkbook.ActiveSheet.Range($oWorkbook.ActiveSheet.Cells(2, $iPos_Slot + 1), $oWorkbook.ActiveSheet.Cells($iRows, $iPos_Slot + 1)), Default, _
    $oWorkbook.ActiveSheet.Range($oWorkbook.ActiveSheet.Cells(2, $iPos_ModelType + 1), $oWorkbook.ActiveSheet.Cells($iRows, $iPos_ModelType + 1)), Default)

doesn't work properly. The transformation e.g. of "AD:AD" seems to be different.

Workaround:

$oMatch = $oWorkbook.ActiveSheet.Range("A1:ZA1").Find("Shelf", Default, $xlValues, $xlPart, Default, Default, False)
$sPos_Shelf = StringRegExpReplace($oMatch.Address, "\$(.+)\$\d*", "$1")
$oMatch = Null
$oMatch = $oWorkbook.ActiveSheet.Range("A1:ZA1").Find("Slot", Default, $xlValues, $xlPart, Default, Default, False)
$sPos_Slot = StringRegExpReplace($oMatch.Address, "\$(.+)\$\d*", "$1")
$oMatch = Null
$oMatch = $oWorkbook.ActiveSheet.Range("A1:ZA1").Find("Model Type", Default, $xlValues, $xlPart, Default, Default, False)
$sPos_ModelType = StringRegExpReplace($oMatch.Address, "\$(.+)\$\d*", "$1")
$oMatch = Null

 

Edited by UEZ

Please don't send me any personal message and ask for support! I will not reply!

Selection of finest graphical examples at Codepen.io

The own fart smells best!
Her 'sikim hıyar' diyene bir avuç tuz alıp koşma!
¯\_(ツ)_/¯  ٩(●̮̮̃•̃)۶ ٩(-̮̮̃-̃)۶ૐ

Share this post


Link to post
Share on other sites
water

Glad you got it working :)

The Outlook issue is still on my ToDo-List but I'm quite busy at the moment. Can only spend a few minutes to answer some threads but do not have the time to check your problem at the moment.
Will keep you informed.


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

  • 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)
    • therks
      By therks
      So I'm needing a (better) way to get the selection in an edit control, while knowing which end of the selection is active vs anchor (ie, if you're holding shift while moving with the arrow keys, which end of the selection is moving and which is not). Since _GUICtrlEdit_GetSel() only returns the start and end positions, in the order of smallest to largest, if you were to pass that back to the _GUICtrlEdit_SetSel(), the user's selection could end up "backwards".
      My solution has been this function:
      Func _GUICtrlEdit_GetSelByAnchor($hWnd) ; Get selection range with anchor in first index If Not IsHWnd($hWnd) Then $hWnd = GUICtrlGetHandle($hWnd) Local $aActive, $aSelection = _GUICtrlEdit_GetSel($hWnd) ; Get base selection If $aSelection[0] <> $aSelection[1] Then ; Only proceed if actual selection range _GUICtrlEdit_SetSel($hWnd, -1, 0) ; Deselect, leaving only the active point $aActive = _GUICtrlEdit_GetSel($hWnd) ; Record the active point If $aActive[0] = $aSelection[0] Then ; If the active point is equal to the original first index ; Swap the original selection points (putting the anchor in [0] and active in [1]) $aSelection[0] = $aSelection[1] $aSelection[1] = $aActive[0] EndIf _GUICtrlEdit_SetSel($hWnd, $aSelection[0], $aSelection[1]) ; Reset selection to the original points EndIf Return $aSelection EndFunc The problem with this, as you may have already guessed from the two SetSel calls, is that calling it in a tight loop causes a lot of flickering. Is there a better, more reliable way to do what I'm looking for?
    • 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.
×

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.