(*
My program SNP Compare was written 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
