JailDoctor Posted February 10, 2012 Posted February 10, 2012 I am working on a routine to check assigned classrooms versus a roster. The user chooses from an excel spreadsheet the ID numbers and classrooms. The routine checks if the rooms match those in the roster. If the room for one element is different, I need to change it in the spreadsheet. The question: How do I track what cells the user selected when choosing classrooms to change the value when necessary? expandcollapse popup#include <clipboard.au3> #include <Array.au3> #include <ExcelCOM_UDF.au3> ;Set a hotkey combo for exit HotKeySet("{ESC}", "MyExit") HotKeySet("{F9}", "TogglePause") Global $oExcel, $XLArrayID, $XLArrayRoom Opt("WinTitleMatchMode",2) $message = "Choose the file containig today's list" $var = FileOpenDialog($message, @MyDocumentsDir & "\", "All (*.xls*)", 1 + 4 ) If @error Then MsgBox(4096,"","No File(s) chosen") Exit EndIf ;Open selected file $oExcel = _ExcelBookOpen($var) ;clear clipboard _ClipBoard_Open (0) _ClipBoard_Empty () _ClipBoard_Close () MsgBox(262176, "Choose", "Select ID numbers") ;User selects a group of cells $oExcel.Application.Selection.Copy $XLArrayID = (_ClipBoard_GetData ()) ;~ Convert to Array $XLArrayID = StringSplit($XLArrayID,@LF) _ArrayTrim($XLArrayID, 1, 1, 0) ;clear clipboard _ClipBoard_Open (0) _ClipBoard_Empty () _ClipBoard_Close () MsgBox(262176, "Locations", "Select Class Rooms") ;User selects a group of cells $oExcel.Application.Selection.Copy $XLArrayRoom = (_ClipBoard_GetData ()) ;~ Convert to Array $XLArrayRoom = StringSplit($XLArrayRoom,@LF) _ArrayTrim($XLArrayRoom, 1, 1, 0) ;Remove empty elements For $n = UBound($XLArrayID) - 1 To 0 Step - 1 If StringStripWS($XLArrayID[$n], 8) = "" Then _ArrayDelete($XLArrayID, $n) _ArrayDelete($XLArrayRoom, $n) EndIf Next ; This is where I check the classrooms assigned versus a roster. ;How do I get the Excel spreadsheet location of each element? Your help is trully appreciated.
Reg2Post Posted February 10, 2012 Posted February 10, 2012 If you are trying to return the cell location of a value you can assign a cell with the formula =Match("string",range,0). Or, you can assign the vba equivalent [cells.find("string").address] to a variable and return the variable to your function. You must create a reference to a COM object, in your case, "excel.application", to use the latter.
JailDoctor Posted February 10, 2012 Author Posted February 10, 2012 Thanks Reg2Post, but this approach wont work because the "strings" may repeat several times in the same sheet. I did look at this: $WhatCell = $oExcel.ActiveCell.Address And this gives me the address of the first cell the user selected. Something like this: $L$139 So now I know the first cell corresponding to the first element. I thought: _ExcelWriteCell($oExcel,$NewClassroom, $WhatCell + $n) $n= element number But it didn't work. How do I add a row to active address?
Reg2Post Posted February 10, 2012 Posted February 10, 2012 L139 would be (139, 11) in R1C1 format. You can add variables or integers to either the row or column number to suit your needs. If using _ExcelWriteCell, then just use the last optional parameter to add a column location and change the $sRangeOrRow parameter to an integer for the row location. With vba: Activesheet.Cells( R1, C1).Value =
water Posted February 10, 2012 Posted February 10, 2012 If you need to get all cells with a certain string you can use function _ExcelFindInRange of the My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now