Jump to content

Recommended Posts

Hi, 

I have a set of excel docs 1& 2

1) In Excel 1 i have a fixed column called dimensionId that contains list of dimensionid's with in between empty cells.

Here, For each dimesionId, i need to check corresponding columns of L,M,N ,O(senior, mid, junior, student) & check If they contain letter Y (Open excel 2) and if empty ignore,check next.

I need to loop through the entire column of dimensionId one by one, please help me proceed with the logic.

2) If letter Y exists, open excel 2 and check if same dimesionId exists in column A.

New to autoit excel automation, Help me with logic to execute this.

#include <Excel.au3>
Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, "D:\yta\Trial concept dimensions list.xlsx")

Local $read1 = _Excel_RangeRead ($oWorkbook,Default,"C3")

MsgBox(0,"",$read1)

Attached exce 1 doc.

 

 

exl1.xlsx exl2.xlsx

Share this post


Link to post
Share on other sites

Read both sheets into arrays and use _ArraySearch or _ArrayFindAll with the Regex search parameter.

Edited by dmob

Share this post


Link to post
Share on other sites
8 hours ago, dmob said:

Read both sheets into arrays and use _ArraySearch or _ArrayFindAll with the Regex search parameter.

Can you share some sample example please.

Share this post


Link to post
Share on other sites
1 hour ago, WindIt said:

Can you share some sample example please.

You have already done half of it...

#include <Excel.au3>

 Example()

 Func _Example()
    Local $oExcel = _Excel_Open()
    Local $oWorkbook1 = _Excel_BookOpen($oExcel, "D:\yta\exl1.xlsx")
    ; check for error
    
    Local $aRead1 = _Excel_RangeRead($oWorkbook1, Default, "C3") ; returns an array of Col C3
    ; check for error

    Local $oWorkbook2 = _Excel_BookOpen($oExcel, "D:\yta\exl2.xlsx")
    ; check for error
    
    Local $aRead2 = _Excel_RangeRead($oWorkbook2, Default) ; returns an array of entire sheet 
    ; check for error
    
    ; now you have both sheets in $aRead1 & $aRead2
    ; now do your comparison. See _ArraySearch & _ArrayFindAll
 EndFunc

 

Share this post


Link to post
Share on other sites

You would have to change $aRead1 to be an array first:

Local $aRead1 = _Excel_RangeRead($oWorkbook1, Default, $oWorkbook1.ActiveSheet.Usedrange.Columns("C:N"), 2)

However personally I would just use a formula in Excel for example, use this formula in Q column to determine if L, M, N match exl2.xlsx X, Y, Z columns:

=IFNA(IF(OR(VLOOKUP(C6,[exl2.xlsx]Sheet1!$A$1:$AH$331,24,FALSE)=L6,VLOOKUP(C6,[exl2.xlsx]Sheet1!$A$1:$AH$331,25,FALSE)=M6,VLOOKUP(C6,[exl2.xlsx]Sheet1!$A$1:$AH$331,26,FALSE)=N6),"Found - Same Values","Found - Different Values"),"Not Found")

 

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

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By WilliamasKumeliukas
      Hi,
      I started this project alone in May 2020 as project in my spare time at work, I'm working for a IT compagny that's started opening their services to residential customers few months ago and now my position in the company kinda drifted in the doom and gloom world of repetitive tasks like: Reinstallation of Windows 10.
      The procedure is very repetitive and I started feeling like being a robot which is the main reason I started this project.
       
      ==============================FAQ==================================
      1. Q: Do you want this project to be accomplished with the usage of AutoIT ONLY or 3rd party tools / Scripts (BAT / CMD / VBS) ? A: No, if I cannot find a way using AutoIT to accomplish a task I will move to my Plan B which consist of automating an 3rd party tool to accomplish the affected task until a solution is found. 2. Q: What do I get from helping/collaborating in this project? A: I will personally take the responsability to mention you in the credits of this project. 3. Q: If I have more questions, can I ask? A: Certainly! feel free to ask any questions related to this project! 4. Q: What is the main goal of this project? A: Automating Windows 10 configuration without user interaction needed (as much as possible) ______________________________________________________________________________________________________________________________
      Current progression of the project (more might be added in future)
      « Blue = Info || Yellow = Unfinished/Untested || Purple = Could be better || Green = Done ||Red = Not Yet Started »
      *** Very early Stage ***
      Connect Network Attached Storage(NAS) (Tested & Working - AutoIT only)
      Install Adobe Reader DC, 7zip, Google Chrome (Tested & Working - using 3rd party tool)
      Change OEM Information (Tested & Working - contains both AutoIT version and CMD script)
      Disable ScreenSaver (Require testing AutoIT only)
      Change Computer Name (Require testing AutoIT only) 
      Change Default Browser (Work - using 3rd party tool)
      Change .pdf / .pdfxml from Edge to Adobe Reader DC (AutoIT only)
      Change Edge to Google Chrome as Default Browser (Tested & Working - using 3rd party tool)
      Windows Updates (Currently working on it - AutoIT only)
      Install Office 
      Activate Office (Tested on 2010 & Working - CMD Script)
      Pin to taskbar Excel, Outlook, Word, Chrome (Tested & Working - using 3rd party tool)
      GUI currently featuring the following options 
      - TreeView to check case of desired tasks
      -  Redirect ConsoleWrite with timestamp messages in richedit (errors shown in red text)
                 *** To Do ***
       - Read informations needed for tasks like: OEM, ComputerName, NAS user/password etc... from a file
       - ListView for Windows Updates
       - Probably more
       
      ***  If this project interest you, Reply here This will greatly help me to see if you'd like this project to become real  ***
      ______________________________________________________________________________________________________________________________
      2020.09.21 Update: Since this topic reached over 200+ views without a single interest shown by community, pm me and i'll share with you W10 Configurator script  
       
      Best Regards,
      ~WilliamasKumeliukas
    • By Earthshine
      This is pretty useful to me at least, I hope to others. Whenever you need to poke a button or such on a GUI, you can just make calls to this UDF and instruct it how long to wait, 0 is forever, anyway, let the code to the explaining. Feel free to help expand this UDF. I plan to create new ones as I go along that support all the Win32 and .NET Controls. Ultimately, I would like to be able to fully control any type of control, this works well with most. Just an example of what you can do.
      NOTE: Logger Author(s) .....: Michael Mims (zorphnog)
      ;                    the logging script can be obtained here
      ;                   https://www.autoitscript.com/forum/topic/156196-log4a-a-logging-udf/
       
       
       
       
       
       
       
      WaitForControls.au3 log4a.au3
    • By Zaoka
      Hi,
      is it possible to break link in active/open $oWorkbook0 with excel.udf function ? If some could link some example...
      Data-Edit Links-Break Link

       
    • By NassauSky
      Hi All,
      I think I'm so close to solving an automation problem but need that last bit of help. Anyone familiar with automation might know this answer.  The beginning of this code is a nice example from @IanN1990 which I dug into a little and found it's counting my tabs correctly in Chrome.  How may I print out each tab name (probably some sort of property in the automation object)
      #include "CUIAutomation2.au3" ; Window handle Local $hWindow = WinGetHandle( "[CLASS:Chrome_WidgetWin_1]" ) If Not IsHWnd( $hWindow ) Then Return ConsoleWrite( "$hWindow ERR" & @CRLF ) ConsoleWrite( "$hWindow OK" & @CRLF ) ; Activate window WinActivate( $hWindow ) Sleep( 100 ) ; UI Automation object Local $oUIAutomation = ObjCreateInterface( $sCLSID_CUIAutomation, $sIID_IUIAutomation, $dtagIUIAutomation ) If Not IsObj( $oUIAutomation ) Then Return ConsoleWrite( "$oUIAutomation ERR" & @CRLF ) ConsoleWrite( "$oUIAutomation OK" & @CRLF ) ; Desktop element Local $pDesktop, $oDesktop $oUIAutomation.GetRootElement( $pDesktop ) $oDesktop = ObjCreateInterface( $pDesktop, $sIID_IUIAutomationElement, $dtagIUIAutomationElement ) If Not IsObj( $oDesktop ) Then Return ConsoleWrite( "$oDesktop ERR" & @CRLF ) ConsoleWrite( "$oDesktop OK" & @CRLF ) ; Chrome window Local $pCondition $oUIAutomation.CreatePropertyCondition( $UIA_ClassNamePropertyId, "Chrome_WidgetWin_1", $pCondition ) If Not $pCondition Then Return ConsoleWrite( "$pCondition ERR" & @CRLF ) ConsoleWrite( "$pCondition OK" & @CRLF ) Local $pChrome, $oChrome $oDesktop.FindFirst( $TreeScope_Descendants, $pCondition, $pChrome ) $oChrome = ObjCreateInterface( $pChrome, $sIID_IUIAutomationElement, $dtagIUIAutomationElement ) If Not IsObj( $oChrome ) Then Return ConsoleWrite( "$oChrome ERR" & @CRLF ) ConsoleWrite( "$oChrome OK" & @CRLF ) ; Tab item Local $pCondition1 $oUIAutomation.CreatePropertyCondition( $UIA_ControlTypePropertyId, $UIA_TabItemControlTypeId, $pCondition1 ) If Not $pCondition1 Then Return ConsoleWrite( "$pCondition1 ERR" & @CRLF ) ConsoleWrite( "$pCondition1 OK" & @CRLF ) ;~ Find All tab items Local $pTabs, $oUIElementArray, $iElements, $pFound, $oFound, $value $oChrome.FindAll( $TreeScope_Descendants, $pCondition1, $pTabs ) $oUIElementArray = ObjCreateInterface($pTabs, $sIID_IUIAutomationElementArray, $dtagIUIAutomationElementArray) $oUIElementArray.Length( $iElements ) ConsoleWrite( "$iElements:" & $iElements & @CRLF ) For $i = 0 To $iElements - 1 Local $pFound $oUIElementArray.GetElement($i, $pFound) ConsoleWrite($pFound & @CRLF) $pFound.GetCurrentPropertyValue($UIA_NamePropertyId,$value) ;<==PROBLEM HERE ConsoleWrite($value & @CRLF) Next  
      Thanks for any direction.
      🙂
×
×
  • Create New...