9 Replies Latest reply: Jul 16, 2012 9:34 AM by taffners
taffners Level 1 (0 points)

Hello, I'm new to applescript.  I'm trying to write a script to compare column B of one excel file to column B of the another excel file.

 

I will have to had a repeat loop to proceed down the column but I'm stuck on the find command. 

 

Below is some of the code I have so far.  The applescript error message is "Microsoft Excel got an error: The object you are trying to access does not exist. It highlights the find column." 

 

set patientFile to choose file with prompt "Please select the patient Excel file:"

set patientName to name of (info for patientFile)

tell application "Microsoft Excel"

open patientFile

set thepatientFile to workbook patientName

end tell

 

display dialog "Is your patient SNP data in column B of the " & " " & patientName & " " & "excel file?" buttons {"No", "Yes"} default button 2

if the button returned of the result is "No" then

display dialog "Please put your data in column B" buttons {"Cancel"} default button 1

if the button returned of the result is "Cancel" then

end if

 

else

display dialog "Is column E empty? The results of this program will be stored in column E of the" & " " & patientName & " " & "excel file." buttons {"No", "Yes"} default button 2

 

if the button returned of the result is "No" then

  display dialog "Please put your data in empty column E" buttons {"Cancel"} default button 1

  if the button returned of the result is "Cancel" then

  end if

else

  set relativeFile to choose file with prompt "Please select the relative Excel file:"

  set relativeName to name of (info for relativeFile)

  tell application "Microsoft Excel"

  open relativeFile

  set therelativeFile to workbook relativeName

  end tell

end if

end if

 

display dialog "Is your relative SNP data in column B of the" & " " & relativeName & " " & "excel file?" buttons {"No", "Yes"} default button 2

if the button returned of the result is "No" then

display dialog "Please put your data in column B" buttons {"Cancel"} default button 1

if the button returned of the result is "Cancel" then

end if

else

display dialog "Does column C contain the result data you would like copied from" & " " & relativeName & " " & "excel file?" buttons {"No", "Yes"} default button 2

if the button returned of the result is "No" then

  display dialog "Please put the data you would like copied in column C." buttons {"Cancel"} default button 1

  if the button returned of the result is "Cancel" then

  end if

else

  tell application "Microsoft Excel"

  activate object workbook patientName

  copy value of cell "A1" to cellPatientFile

  find (range "B:B" of workbook relativeName) what cellPatientFile

  if cellPatientFile is true then

  set bold of font object of cellPatientFile to true

  end if

  end tell

end if

end if

 

 

Does any one have any hints towards solving this problem?  Thank you for any help.

  • rccharles Level 5 (7,574 points)

    I'm not so familiar with AppleScript & excel.

     

    I'm trying to write a script to compare column B of one excel file to column B of the another excel file.


    Not sure why you need the find.

     

    For a quick & not so elegant solution, I'd read in a cell  into an appliscript variable.  We the cell from the other excel sheet into another variable.  Do the comparison in Applescript.

     

    You seem OK in applescript, so maximize you applescript coding.

     

    Here is some code to print out variables.

     

    (*

     

    It is easier to diagnose problems with debug information. I suggest adding log statements to your script to see what is going on.  Here is an example.

     

     

        Author: rccharles

     

        For testing, run in the Script Editor.

          1) Click on the Event Log tab to see the output from the log statement

          2) Click on Run

     

    *)

     

     

    on run

        -- Write a message into the event log.

        log "  --- Starting on " & ((current date) as string) & " --- "

        --  debug lines

        set desktopPath to (path to desktop) as string

        log "desktopPath = " & desktopPath

    end run

     

     

     

    --------------------------------------------------------------

     

    In the future:

    -- try to reduce the code to what statements are causing the problem before posting

    -- The number of display dialog statements creates a hassle when trying to run the script.  I didn't.

    -- create a dropbox account.  Place some example excel spreadsheets we may use.

     

    Robert

  • taffners Level 1 (0 points)

    Thank you rccharles.  Very helpful comments.  This is the first program I've wrote and the first time I've ever posted on a forum.  Thank you for the tips.  I will try the log statements.  Thank you for the times on posting in the future. 

  • rccharles Level 5 (7,574 points)

    Here are the books that I use when writing an applescript.  Do not know of a good source for AppleScript to Excel.
    Do not know why your post didn't draw more interest.

    AppleScript

    Learn AppleScript: The Comprehensive Guide to Scripting and Automation on Mac OS X, Third Edition the book

     

    AppleScript Language Guide pdf download the pdf file

     

    Intro to applescript with sending an email
    http://mac.appstorm.net/how-to/applescript/the-ultimate-beginners-guide-to-apple script/

  • Jacques Rioux Level 4 (3,065 points)

    Hi,

     

    1-  The find command return a range (not a boolean), or it return an error if nothing was found.

    You must use a try block.

     

     

    2-  The find command doesn't work as in the application, you must specify the sheet, otherwise it's the active sheet.

    To search in all sheet, you must use a loop.

     

     

    3-  cellPatientFile is a variable wich contains a text, you can't set bold this variable, but it's possible on a variable wich contains a range object.

    example :

    set cellPatient to range "A1"

    set bold of font object of cellPatient to true

    .

     

     

    --------------------------------

    set patientFile to choose file with prompt "Please select the patient Excel file:"

    tell application "Microsoft Excel"

          set thepatientWBook to open workbook workbook file name (patientFile as string)

          set patientName to name of thepatientWBook

    end tell

     

    display dialog "Is your patient SNP data in column B of the " & " " & patientName & " " & "excel file?" buttons {"No", "Yes"} default button 2

    if the button returned of the result is "No" then

          display dialog "Please put your data in column B" buttons {"Cancel"} default button 1

          if the button returned of the result is "Cancel" then

          end if

     

    else

          display dialog "Is column E empty? The results of this program will be stored in column E of the" & " " & patientName & " " & "excel file." buttons {"No", "Yes"} default button 2

         

          if the button returned of the result is "No" then

                display dialog "Please put your data in empty column E" buttons {"Cancel"} default button 1

                if the button returned of the result is "Cancel" then

                end if

          else

                set relativeFile to choose file with prompt "Please select the relative Excel file:"

                tell application "Microsoft Excel"

                      set therelativeWBook to open workbook workbook file name (relativeFile as string)

                      set relativeName to name of therelativeWBook

                end tell

          end if

    end if

     

    display dialog "Is your relative SNP data in column B of the" & " " & relativeName & " " & "excel file?" buttons {"No", "Yes"} default button 2

    if the button returned of the result is "No" then

          display dialog "Please put your data in column B" buttons {"Cancel"} default button 1

          if the button returned of the result is "Cancel" then

          end if

    else

          display dialog "Does column C contain the result data you would like copied from" & " " & relativeName & " " & "excel file?" buttons {"No", "Yes"} default button 2

          if the button returned of the result is "No" then

                display dialog "Please put the data you would like copied in column C." buttons {"Cancel"} default button 1

                if the button returned of the result is "Cancel" then

                end if

          else

                tell application "Microsoft Excel"

                      activate

                      activate object thepatientWBook

                      copy value of cell "A1" to cellPatientFile

                      try

                            find range "B:B" of sheet 1 of therelativeWBook what cellPatientFile -- find in first sheet of therelativeWBook

                            set bold of font object of cell "A1" to true -- cell in thepatientWBook

                      end try

                end tell

          end if

    end if

    --------------------------------

  • taffners Level 1 (0 points)

    Thank you everyone for your help.  I attached my code.  It works now but kinda slow.  Does anyone have any hints on making it faster.  Sorry this is my first program ever so I probably went about something completely backwards. 

     

     

    (*

    My program SNP Compare was writen for bioinformatics.  It compares a patient file to a relatives file. This program takes a column in excel file1 and sees if it is in a column in excel file2.  If the answer is found it will copy a field in excel file2 in the same row the answer was found in.

     

    Example data:

    ------------------------

    File one:

    Column of data          column to     paste answer

    10

    29

    7

    2

    105

    102

    110

    111

    ------------------------

    File two:

    Column of data          The answer to be copied to file one

    29                                                                      found row 2

    10                                found row 3

    7                                  found row 4

    2                                  found row 5

    205                              found row 6

    202                               found row 7

    210                               found row 8

    ------------------------

    File 1 after my program

    Column of data         Paste answer here

    10                                                                      found row 3

    29                                                                      found row 2

    7                                  found row 4

    2                                                                        found row 5

    105                              no

    102                              no

    110                              no

    111                              no

    ------------------------

    *)

    --opens File1.  Names it patientName

    set patientFile to choose file with prompt "Please select the patient Excel file:"

    set patientName to name of (info for patientFile)

    tell application "Microsoft Excel"

    open patientFile

    set thepatientFile to workbook patientName

    end tell

     

     

    --This Dialog assigns where the data is stored for file1

    display dialog "What Column is your patient SNP data of the " & " " & patientName & " " & "excel file?" default answer "" buttons {"submit"} default button 1

    set PatientSNPCol to (text returned of the result)

     

    --This Dialog assigns what row your data starts at for file1

    display dialog "What row does your patient SNP data start in the " & " " & patientName & " " & "excel file?" default answer "" buttons {"submit"} default button 1

    set patientSNPRow to (text returned of the result)

    set patientStartCell to PatientSNPCol & patientSNPRow

     

    --This Dialog assigns the last row of data for file1

    display dialog "What row is the last row of you patient SNP data start of the " & " " & patientName & " " & "excel file?" default answer "" buttons {"submit"} default button 1

    set patientLastRow to (text returned of the result)

    set patientLastCell to PatientSNPCol & patientLastRow

     

    --This Dialog assigns the location of the data copied from file2

    display dialog "What column would you like the results of this program stored in of the" & " " & patientName & " " & "excel file." default answer "" buttons {"submit"} default button 1

    set resultsLocation to (text returned of the result)

     

    --opens File2.  Names it relativeName

    set relativeFile to choose file with prompt "Please select the relative Excel file:"

    set relativeName to name of (info for relativeFile)

    tell application "Microsoft Excel"

    open relativeFile

    set therelativeFile to workbook relativeName

    end tell

     

    --This dialog assigns the column of data in file2

    display dialog "What Column is your relative SNP data of the " & " " & relativeName & " " & "excel file?" default answer "" buttons {"submit"} default button 1

    set relativeSNPCol to (text returned of the result)

     

    --This dialog assigns the column of data begining copied to excel file1.

    display dialog "What Column contains the data you would like copied to the " & " " & patientName & " " & "excel file in column" & " " & resultsLocation & " " & "?" default answer "" buttons {"submit"} default button 1

    set copyAnswer to (text returned of the result)

     

    --This repeat loop makes the way down the column

    repeat with thisRow from patientSNPRow to patientLastRow

     

    tell application "Microsoft Excel"

      activate object workbook patientName

     

     

      set theRange to PatientSNPCol & thisRow & ":" & PatientSNPCol & thisRow

      set theValue to (get value of range theRange as list)

      set theAnswer to resultsLocation & thisRow & ":" & resultsLocation & thisRow

     

      set relativeSearchRange to (relativeSNPCol & ":" & relativeSNPCol)

      copy value of cell theRange to numberOfCellToSearch

     

     

      set searchTerm to numberOfCellToSearch

      activate object workbook relativeName

      set searchRange to get range relativeSearchRange of active sheet

     

      set foundSNPCellAddress to ""

      set foundCells to {}

      try

      set foundSNPInRelative to (find searchRange what searchTerm)

      set foundSNPCellAddress to get address foundSNPInRelative

     

      end try

      --If data is not found a no will be placed on file1

      if foundSNPCellAddress = "" then

      tell application "Microsoft Excel"

      activate object workbook patientName

      set value of cell theAnswer to "no"

      end tell

      else

      --If data is found the value of data in an assigned column will be copied to a column in file1

      tell application "Microsoft Excel"

      activate object workbook patientName

      set theStr to foundSNPCellAddress

      set tid to AppleScript's text item delimiters

      set AppleScript's text item delimiters to "$"

      set theSubStr to (text item 3 of theStr) as text

      set AppleScript's text item delimiters to tid

     

      set copyResult to copyAnswer & theSubStr & ":" & copyAnswer & theSubStr

      activate object workbook relativeName

     

      copy value of cell copyResult to cellCopy

      activate object workbook patientName

      set value of cell theAnswer to cellCopy

      end tell

     

      end if

    end tell

    end repeat

  • rccharles Level 5 (7,574 points)

    this is my first program ever so I probably went about something completely backwards.

    Your doing really well.  You are natural programmer.

    Thank you everyone for your help.  I attached my code.  It works now but kinda slow.  Does anyone have any hints on making it faster.

     

    I'd suggest creating a new post with the script & the question.  There's some way of turning off screen refresh.  I'll have to look around.

     

    --------------------------------------------------------------

     

    Presto. I found this article.

    http://lists.apple.com/archives/applescript-users/2011/Apr/msg00129.html

     

    Look for this line.

    set screen updating to false

    Robert

  • taffners Level 1 (0 points)

    Your doing really well.  You are natural programmer.

    Thank you.  Well I guess I wrote 1 program before but all it did was open a url on chrome. 

     

     

     

     

    set screen updating to false

     

    I will try to look into this set screen updating to false.  This looks like a good recommendation.  it would be a lot better program if the two excel files do not continue to flash between each other. 

  • Jacques Rioux Level 4 (3,065 points)

    Hi,

    taffners wrote:

     

    Thank you everyone for your help.  I attached my code.  It works now but kinda slow.  Does anyone have any hints on making it faster.

     

    Important : when you use the find command, it's preferable to use the properties ( look in and look at).

    Example : find searchRange what searchTerm look in values look at whole

     

    Otherwise, it will search according to the criteria of your last find.

    Example, if you uncheck 'Find entire cells only', and after that you run the script,

    find column 2 what 10.0 -->  match 110.0 or 1110.0 or 310.0, .... , because it look at part.

     

    ---

    set screen updating to false : Yes it improves the speed.

     

    Here are my tests (no duplicate values in the Excel column ) on old computer (Powermac G5) .

    set screen updating to false without any activate object command

    2000 rows --> 3.7 minutes,  your script : 11.5 minutes

    4000 rows --> 7.5 minutes, your script : 23 minutes

    8000 rows --> 15 minutes,  your script : 46 minutes

     

     

    But, it's faster to search through a list without using the Excel find command, no need to set the screen updating, You can continue working on the workbook or another workbook, this will not disturb the script.

    Tests on my script :

    2000 rows --> 4 seconds

    4000 rows --> 21 seconds

    8000 rows --> 1.3 minutes

    17000 rows --> 6 minutes

     

    Here is the script :

    ------------------------------------

    main()

    display dialog "Done"

    on main()

          script o

                property patientSNP_list : {}

                property relativeSNP_list : {}

                property resultsList : {}

                property copyAnswerList : {}

     

                on findLookAtWhole(value) -- case sensitive

                      set {low, high} to {1, count my relativeSNP_list}

                      considering case

                            repeat while lowhigh

                                  set mid_ to (low + high) div 2

                                  if value is in items low thru mid_ of my relativeSNP_list then

                                        set high to mid_

                                  else

                                        set low to mid_ + 1

                                  end if

                            end repeat

                      end considering

                      if (item low of my relativeSNP_list is value) then

                            set end of my resultsList to item low of my copyAnswerList

                      else

                            set end of my resultsList to {"no"}

                      end if

                end findLookAtWhole

          end script

     

          --opens File1.  Names it patientName

          set patientFile to choose file with prompt "Please select the patient Excel file:"

          set patientName to name of (info for patientFile)

          tell application "Microsoft Excel"

                open patientFile

                set thepatientFile to workbook patientName

                set patientSheet to sheet (name of active sheet) of thepatientFile

          end tell

     

     

          --This Dialog assigns where the data is stored for file1

          display dialog "What Column is your patient SNP data of the " & " " & patientName & " " & "excel file?" default answer "" buttons {"submit"} default button 1

          set PatientSNPCol to (text returned of the result)

     

          --This Dialog assigns what row your data starts at for file1

          display dialog "What row does your patient SNP data start in the " & " " & patientName & " " & "excel file?" default answer "" buttons {"submit"} default button 1

          set patientSNPRow to (text returned of the result)

          set patientStartCell to PatientSNPCol & patientSNPRow

     

          --This Dialog assigns the last row of data for file1

          display dialog "What row is the last row of you patient SNP data start of the " & " " & patientName & " " & "excel file?" default answer "" buttons {"submit"} default button 1

          set patientLastRow to (text returned of the result)

          set patientLastCell to PatientSNPCol & patientLastRow

     

          --This Dialog assigns the location of the data copied from file2

          display dialog "What column would you like the results of this program stored in of the" & " " & patientName & " " & "excel file." default answer "" buttons {"submit"} default button 1

          set resultsLocation to (text returned of the result)

     

          --opens File2.  Names it relativeName

          set relativeFile to choose file with prompt "Please select the relative Excel file:"

          set relativeName to name of (info for relativeFile)

          tell application "Microsoft Excel"

                open relativeFile

                set therelativeFile to workbook relativeName

                set relativeSheet to sheet (name of active sheet) of therelativeFile

          end tell

     

          --This dialog assigns the column of data in file2

          display dialog "What Column is your relative SNP data of the " & " " & relativeName & " " & "excel file?" default answer "" buttons {"submit"} default button 1

          set relativeSNPCol to (text returned of the result)

     

          --This dialog assigns the column of data begining copied to excel file1.

          display dialog "What Column contains the data you would like copied to the " & " " & patientName & " " & "excel file in column" & " " & resultsLocation & " " & "?" default answer "" buttons {"submit"} default button 1

          set copyAnswer to (text returned of the result)

     

          tell application "Microsoft Excel" -- get values

                set o's patientSNP_list to value of range (PatientSNPCol & patientSNPRow & ":" & PatientSNPCol & patientLastRow) of patientSheet

                set lastrow to first row index of (last cell of used range of relativeSheet)

                set o's relativeSNP_list to value of range (relativeSNPCol & "1:" & relativeSNPCol & lastrow) of relativeSheet

                set o's copyAnswerList to value of range (copyAnswer & "1:" & copyAnswer & lastrow) of relativeSheet

          end tell

     

          set tc to (count o's relativeSNP_list)

          repeat with i from 1 to tc --convert list of lists to one list

                set item i of o's relativeSNP_list to item 1 of item i of o's relativeSNP_list

          end repeat

     

          set tc to (count o's patientSNP_list)

          repeat with i from 1 to tc -- AppleScript find method, not the Excel Find

                o's findLookAtWhole(item 1 of (item i of o's patientSNP_list))

          end repeat

     

          tell application "Microsoft Excel" -- set values

                set value of range (resultsLocation & patientSNPRow & ":" & resultsLocation & patientLastRow) of patientSheet to o's resultsList

          end tell

    end main

    ------------------------------------

     

    If you have lots of rows and you want it to go faster.

    I have another script that uses the power of (/usr/bin/awk and his associative arrays) in a do shell script

     

    17000 rows --> awk : 2 seconds, applescript : 6 seconds, total : 8 seconds

    34000 rows --> awk : 3 seconds, applescript : 11 seconds, total : 14 seconds

  • taffners Level 1 (0 points)

    Thats awesome.  It is so fast and the annoying updating of pages is gone. Thank you so much.  I learned a lot from your approach.