Applescript and Excel

Scripting Forums AppleScript | Mac OS X
#1

Alright so here is my current problem. I have a database in excel that I am trying to use Applescript to manipulate. I have a series of dates in Row 1 and a list of Materials in Column A and what I am trying to do is have it update the Quantity of the Material in the column under the current date in that specific Material’s Row. I really hope that made sense.
The issue I am currently having is that when the script selects cell “A1” which is blank I tell it to move one cell to the right which has a date in it and check to see if it is the current date and if its not it needs to repeat and again move one cell to the right and check that date to see if it matches. If it does then I am having it do something else. But there is my issue, here is what I have for the script to date.


tell application "System Events"
	set leftArrow to ASCII character 28
	set rightArrow to ASCII character 29
	set downArrow to ASCII character 31
	set upArrow to ASCII character 30
end tell

set d to day of (current date)
set m to month of (current date)
set y to year of (current date)
set cd to (current date)

tell application "Microsoft Excel"
	tell worksheet "Sheet1" of active workbook
		select cell "A1"
repeat
		tell application "System Events"
			keystroke rightArrow
		end tell
             if current cell <> cd then   -- this line is the issue
        delay 0.1
else
         say "Congrats"
         exit repeat
end repeat
end tell
end tell
end

say "Yay! you Finally did it."

Thank you in advance for your help.
-John

#2

Hello

I have never written a single line of AppleScript for MsExcel, but tons of visual basic.

The first thing that get’s to my mind is why don’t you search for the column with the current date?

  • I believe you can search within a range.

Best Regards

McUsr

#3 
tell application "Microsoft Excel"
	set seekMe to current date
	set seekMe to seekMe - (time of seekMe)
	if date 1904 of active workbook then
		set dateCorrection to date "Friday, January 1, 1904 12:00:00 AM"
	else
		set dateCorrection to date "Monday, January 1, 1900 12:00:00 AM"
	end if
	
	set lastCol to (first column index of (get end range "IV1" direction toward the left))
	
	repeat with colNum from 2 to lastCol + 1
		if value of cell 1 of column colNum = seekMe then exit repeat
	end repeat
	
	if colNum < lastCol then
		-- cell 1 of column colNum has the current date	
		-- do something
	else
		display dialog "Today's date is not in row 1"
	end if
end tell