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
what exactly isn’t working, the quicktime part or the excel part? which line of code?
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.
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
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
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? Good luck. And “Hi” McUsr!
Good luck. And “Hi” McUsr!