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

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

 

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")

 

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 goku200
      Not sure what's going on with my script, but when I range read the contents in column A and then write them to column E it inserts the contents but it also inserts #N/A after all the way down to row  1048576. What am I missing from my script? Below is my script
       
    • By goku200
      I'm having some issues with my script that's not grabbing the id from the cell and appending it to the url test that I have in my script.
      Here is my script:
      What I'm having issues is looping through the id column in the excel spreadsheet and adding 1 to the url address when its tabbed by using _WD_NewTab. When it goes to the browser I'm wanting it to display: https://127.0.0.1/test.html?id=1 press Enter and save the file as 1.html and so forth for the others. Not sure what I'm doing wrong here. 
       
    • By abberration
      Software Installer
      Version: 2.0
      It's been been a long year, but I finally got some time to rework this project. I re-wrote everything from scratch because the old version was getting too complicated with so many options and sub-menus. This new version is much easier to use and I have been testing for a few days and it seems very stable.
      For those who are new to this software, it helps you install software silently/unattended. This new version tries to determine the silent switch automatically. You can also re-organize the order in which the software installs by dragging & dropping them in the listview. It now supports creating profiles and checks for missing software (and automatically unchecks them, so it does not attempt to install non-existent software). One feature I included was because I have seen several people on Youtube talk about disliking bright screens at night. So, now you can choose from a few color theme (half of them are dark). I dabbled a bit more into GDI+ to draw a few things and show my logo with a transparent background (hint: I'm not good at GDI+).
      Under the Help menu, you will find a User Guide, which goes through most of it's features. I included a new icon if you want to use when you compile the script (in the Assets > Misc folder).
      If you have questions, comments or suggestions, all are welcome. Hope you enjoy!
      Here it is in action:

       
      Software_Installer_2.0.zip
    • By rcmaehl
      A UDF to connect and control Five9 on the Local Network via it's provided HTTP API
       
      THIS UDF IS STILL IN TESTING
       
      Current Features:
      Agent Ready
      * Contains most functions needed to control Agent Status  
      PLANNED Features:
      Heavyweight
      * Control most Five9 API functions
      * Full Control over own Status as well as Supervisor functionality
        Helpful Premade Examples Files
      * Examples for almost every function
      * Premade CUI and GUI clients
        Ready for all users, beginner and up
      * Detailed Error Reporting to help pin-point problems
      * Thorough parameter checking to prevent accidental script crashes  
      Support:
      Support for this UDF can be obtained in my Discord Server
    • By rcmaehl
      A UDF to connect and control Cisco Finesse on the Local Network via it's provided HTTP API
       
      Current Features:
      Heavyweight
      * Large feature set, containing most Finesse Desktop API functions  
      PLANNED Features:
      Heavyweight
      * Control over own Status as well as Supervisor functionality
        Helpful Premade Examples Files
      * Examples for almost every function
      * Premade CUI and GUI clients 
        Ready for all users, beginner and up
      * Detailed Error Reporting to help pin-point problems
      * Thorough parameter checking to prevent accidental script crashes  
      Support:
      Support for this UDF can be obtained in my Discord Server
×
×
  • Create New...