noob question: pull QT duration with "system events"

Scripting Forums AppleScript | Mac OS X
#1

I’m completely new to using Applescript…as many of you will be able to tell when I paste in what I am currently working with. I’m working on a new macbook pro running Snow Leopard 10.6.5

I have a long list of .mov files that I need to get the runtimes for, and started searching around and found some different scripts that I combined to get somewhat of a descent result. Right now I’m able to run this script on a .mov file and it will dump the duration into an excel spreadsheet for me. What I cannot get it to do without errors is

  1. Get it to place the name of the file in column 1
  2. Figure out how to run this script on the entire contents of a folder instead of one file at a time.

I’ve been searching for 3 days now and trying everything I come across but I just haven’t managed to figure these things out. If anyone can help me it would be greatly appreciated. Here is what I’m working with so far:

set movie_file to (choose file with multiple selections allowed) as string

tell application "System Events"
	set movie_contents to contents of movie file movie_file
	set tscale to time scale of movie_contents
	set dur to duration of movie_contents
end tell

set runningTime to dur / tscale
set hr to text 2 thru 3 of ((100 + runningTime div hours) as string)
set min to text 2 thru 3 of ((100 + runningTime mod hours div minutes) as string)
set sec to text 2 thru 3 of ((100 + runningTime mod minutes div 1) as string)
set frm to text 2 thru 3 of ((100 + runningTime mod 1 * 30) as string)
set displayedDuration to hr & ":" & min & ":" & sec & "." & frm


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"
		
	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 2 to displayedDuration
	end tell
end tell

Thank you in advance for any help!!
Justin

#2

Hi Justin,

welcome to MacScripter.

try this


set movieFiles to (choose file with multiple selections allowed)
tell application "Microsoft Excel"
	if name of active sheet is not "coding" then
		set newWorkBook to make new workbook
		tell sheet 1 of newWorkBook
			set name to "coding"
			set value of range "A1:B1" to {"Filename", "Movie Duration"}
		end tell
	end if
	set activeSheet to active sheet
end tell

repeat with oneFile in movieFiles
	try
		tell application "System Events"
			set movieFile to movie file (oneFile as text)
			set {time scale:tscale, duration:dur} to contents of movieFile
			set fileName to name of movieFile
		end tell
		
		set runningTime to dur / tscale
		set hr to text 2 thru 3 of ((100 + runningTime div hours) as string)
		set min to text 2 thru 3 of ((100 + runningTime mod hours div minutes) as string)
		set sec to text 2 thru 3 of ((100 + runningTime mod minutes div 1) as string)
		set frm to text 2 thru 3 of ((100 + runningTime mod 1 * 30) as string)
		set displayedDuration to hr & ":" & min & ":" & sec & "." & frm
		
		tell application "Microsoft Excel"
			tell activeSheet
				set firstUnusedCell to (count rows of used range) + 1
				set value of range ("A" & firstUnusedCell & ":B" & firstUnusedCell) to {fileName, displayedDuration}
			end tell
		end tell
	end try
end repeat
#3

That works great, thank you so much for the help!