Playhead Position of Quicktime

Hey, I’m unable to get the below script to pick out the details from the currently open quicktime player 7 movie and write it to an excel file. Anyone able to help!? Thanks!


tell application "QuickTime Player 7"
	tell document 1
		set {current_time, movie_end} to {current time, duration} -- get movie properties
		set inp to display dialog "Enter Coding Now" & return & return & "NB: You may find it quicker to assign each measure a numbee (e.g. look at the mouth = 1), and later use IF statements in excel to convert the numbers into meaningful info" default answer "1"
	end tell
	
	set current_time to (current_time as string)
	set movie_end to (movie_end as string)
	
	tell application "Microsoft Excel"
		activate
		
		if name of active sheet = "sheet1" then -- if statement to rename sheet1 if there is one
			set name of sheet 1 of active workbook to "coding"
			set value of cell 1 of column 1 to "Movie Duration" -- enter title into A1
			set value of cell 1 of column 2 to "Playhead Position"
			set value of cell 1 of column 3 to "Code"
			set value of cell 1 of column 4 to "Comments"
		end if
		
		if not (exists sheet "coding") then -- if the coding sheet doesn't exist, produce an error
			display dialog "Sheet could not be formed and does not already exist.  This likely means you already have an excel workbook open.  To prevent this problem reoccuring, either close all open excel documents or else create a new excel document and run the script again.  Ensure the first sheet is called 'sheet1'" buttons {"OK"} cancel button 1
		end if
		
		select sheet "coding"
		-- set first blank cell in column 1 (i.e. column A) to the data held in the variable time_scale
		set first row index of (get end (last cell of column 1) direction toward the top) to end_movie
		set first row index of (get end (last cell of column 2) direction toward the top) to current_time
		set first row index of (get end (last cell of column 3) direction toward the top) to inp
		
	end tell
	
end tell

2 things

  1. what exactly isn’t working, the quicktime part or the excel part? which line of code?
  2. why do you have the excel code inside of the quicktime tell block of code? Why are you telling quicktime to tell excel? It’s unnecessary and might be the cause of your problem. You should separate them.

Thanks for the reply! I am very new to applescripting, which is why the tell excel is inside the tell quicktime, my mistake. I’ve been tinkering with it for a few hours since I’ve posted. At the moment, I’ve taken the “convert number to string” lines out, and now my problem is that it won’t write the current time to the excel file - it says the variable is undefined.

Thanks!

There’s a couple things I saw. First, you need to know the “time scale” of a movie to calculate the current time and duration of a movie. Dividing by the time scale gives you the time in seconds. Next, you can’t set “first row index” to value… you set a cell’s value. Third, you were actually getting the last row that has a value… and of course you want to set the value of the next cell after that so we have to add 1 to the row index. Last, to get the text you type into a dialog box you get the “text returned” from the dialog box. Try this…

tell application "QuickTime Player 7"
	tell document 1
		set ts to time scale
		set {current_time, movie_end} to {current time / ts, duration / ts} -- get movie properties
		set inp to display dialog "Enter Coding Now" & return & return & "NB: You may find it quicker to assign each measure a numbee (e.g. look at the mouth = 1), and later use IF statements in excel to convert the numbers into meaningful info" default answer "1"
	end tell
	
	set current_time to (current_time as string)
	set movie_end to (movie_end as string)
	set the_code to text returned of inp
	
end tell

tell application "Microsoft Excel"
	activate
	
	if name of active sheet = "sheet1" then -- if statement to rename sheet1 if there is one
		set name of sheet 1 of active workbook to "coding"
		set value of cell 1 of column 1 to "Movie Duration" -- enter title into A1
		set value of cell 1 of column 2 to "Playhead Position"
		set value of cell 1 of column 3 to "Code"
		set value of cell 1 of column 4 to "Comments"
	end if
	
	if not (exists sheet "coding") then -- if the coding sheet doesn't exist, produce an error
		display dialog "Sheet could not be formed and does not already exist.  This likely means you already have an excel workbook open.  To prevent this problem reoccuring, either close all open excel documents or else create a new excel document and run the script again.  Ensure the first sheet is called 'sheet1'" buttons {"OK"} cancel button 1
	end if
	
	select sheet "coding"
	
	tell front sheet
		set lastUsedCell to get end (last cell of column 1) direction toward the top
		set emptyRowIndex to (first row index of lastUsedCell) + 1
		
		set value of cell emptyRowIndex of column 1 to movie_end
		set value of cell emptyRowIndex of column 2 to current_time
		set value of cell emptyRowIndex of column 3 to the_code
	end tell
end tell

Hi Hank,

That’s absolutely amazing! I actually threw my arms in the air with a “yes!” (I have no idea why people are looking at me oddly). Whilst a fairly simple task, this script has reduced my work time by approximately 3 months! Thank you so much, I really appreciate it! Works like a charm

Chris

P.S: I added a column to add the filename to the excel document too


tell application "QuickTime Player 7"
	tell document 1
		set sel to name of item 1
		set sel to (sel as string)
		set ts to time scale
		set {current_time, movie_end} to {current time / ts, duration / ts} -- get movie properties
		set inp to display dialog "Enter Coding Now" & return & return & "NB: You may find it quicker to assign each measure a number (e.g. look at the mouth = 1), and later use IF statements in excel to convert the numbers into meaningful info" default answer "1"
	end tell
	
	set current_time to (current_time as string)
	set movie_end to (movie_end as string)
	set the_code to text returned of inp
	
end tell

tell application "Microsoft Excel"
	activate
	
	if name of active sheet = "sheet1" then -- if statement to rename sheet1 if there is one
		set name of sheet 1 of active workbook to "coding"
		set value of cell 1 of column 1 to "Filename" -- enter title into A1
		set value of cell 1 of column 2 to "Movie Duration"
		set value of cell 1 of column 3 to "Playhead Position"
		set value of cell 1 of column 4 to "Code"
		set value of cell 1 of column 5 to "Comments"
	end if
	
	if not (exists sheet "coding") then -- if the coding sheet doesn't exist, produce an error
		display dialog "Sheet could not be formed and does not already exist.  This likely means you already have an excel workbook open.  To prevent this problem reoccuring, either close all open excel documents or else create a new excel document and run the script again.  Ensure the first sheet is called 'sheet1'" buttons {"OK"} cancel button 1
	end if
	
	select sheet "coding"
	
	tell front sheet
		set lastUsedCell to get end (last cell of column 1) direction toward the top
		set emptyRowIndex to (first row index of lastUsedCell) + 1
		set value of cell emptyRowIndex of column 1 to sel
		set value of cell emptyRowIndex of column 2 to movie_end
		set value of cell emptyRowIndex of column 3 to current_time
		set value of cell emptyRowIndex of column 4 to the_code
	end tell
end tell

Hello Hank

Best Regards

McUsr

No problem Chris. You did the work and I just helped you clean it up. I’m always happy to help someone who is willing to work it them self first. Applescript is amazing isn’t it? :wink: Good luck. And “Hi” McUsr!

Hello.

If the script did save 3 months of work it is at least pretty close to a “timesaving” record here.

:smiley:
Best Regards and a nice day to both of you

McUsr

Quite so, in fact I should be finished at the end of last week