Script only running from within Editor

Hi all,

the following script was written to copy a TOC from MS Word (2008) into MS Excel (2008) as space-delimited (tabbed) text - into seperate columns.

It works fine when I run it in editor, but stops after opening the xlsx-file and activate the cell “A20”. Also, when I then (force) close Excel and retry, it (sometimes) tells me the xls-file is allready open…

What am I doing wrong - apart from probably not using very ‘compact’ scripting… (I’m a newbie)


--    VERSIE
set sVers to "1.1.5"
try
	tell me to «event ascrgdut»
end try
tell application "System Events" to set the clipboard to "" -- klembord leegmaken


-- Deel 1 
tell application "Microsoft Word"
	
	activate
	-- einde van document definiëren
	set documentEnd to end key selection move unit a story extend by moving
	-- nieuwe TOC toevoegen aan einde van document
	make new table of contents at active document with properties {include page numbers:false, text object:documentEnd, use fields:false, use heading styles:true, lower heading level:3, upper heading level:1}
	-- | vervangen door tabs
	set tocRange to create range active document start (start of content of text object of table of contents 2 of active document) end (end of content of text object of table of contents 2 of active document) --TOC als tekst-range instellen
	
	execute find find object of tocRange find text "| " replace with "^t" replace replace all
	execute find find object of tocRange find text "^w|" replace with "^t^t^t^t^t^t^t" replace replace all -- zorgt ervoor dat er genoeg lege cellen zijn tussen omschrijving en meetcode
	execute find find object of tocRange find text "|" replace with "^t" replace replace all
	tell application "System Events" to set the clipboard to "" -- klembord leegmaken
	copy object tocRange
	delete table of contents 2 of active document
end tell

-- Deel 2 
tell application "Microsoft Excel"
	try
		launch
	end try
	delay 3
	activate
	set nieuweMS to open "HD Macbook:Users:ruben:Documents:DET_MES-template.xlsx" --template openen
	set nieuweMS to active workbook
	activate object sheet "ARTIKELS" of active workbook
	select range "A20"
	-- copy object copiedText
	paste special on worksheet sheet "ARTIKELS" of active workbook format "Unicode Text" --TOC uit word als delimited text plakken
	
	
	set totaalCopy to range "K7" of sheet "ARTIKELS"
	set meetlijnenCopy to range "A8:K16" of sheet "ARTIKELS"
	set stopRowNumber to value of range "B1" as integer --aantal rijen wordt in het excelblad zelf geteld	
	
	set stijlMeetpost to range "A7:K7"
	
	
	set rowCount to 19
	
	repeat until rowCount is stopRowNumber
		set rowCount to (rowCount + 1)
		set eenheidCheck to (value of range ("J" & rowCount))
		
		if not eenheidCheck = "" then 
			set insertionPoint to range ("A" & (rowCount + 1) & ":K" & (rowCount + 1))
			activate insertionPoint
			select insertionPoint -- invoegpunt definiëren en selecteren
			set insertSize to get resize insertionPoint row size 9 
			insert into range insertSize shift shift down
			copy range totaalCopy destination range ("K" & rowCount) of sheet "ARTIKELS"
			copy range meetlijnenCopy destination range ("A" & (rowCount + 1)) of sheet "ARTIKELS"
			copy range stijlMeetpost
			paste special range ("A" & rowCount & ":K" & rowCount) what paste formats
			
			set rowCount to (rowCount + 9)
			set stopRowNumber to (stopRowNumber + 9)
		end if
	end repeat
	
	-- Deel 3 
	set artikelsCopy to range ("A20:K" & stopRowNumber) of sheet "ARTIKELS"
	activate object sheet "DET"
	set insertionPoint to range "A4:K4" of sheet "DET"
	set insertSize to get resize insertionPoint row size (stopRowNumber - 19)
	insert into range insertSize shift shift down
	copy range artikelsCopy destination range "A4" of sheet "DET"
	try
		set display alerts to false
		delete sheet "ARTIKELS"
	end try
	
	set meetstaatNaam to get save as filename
	save workbook as nieuweMS filename meetstaatNaam
	
	-- Deel 4 
	display dialog "Uw Det werd aangemaakt!" buttons {"Sluit af"} with icon 1 default button 1 giving up after 180
	-- geluid afspelen
	tell application "System Events"
		set muteStatus to output muted of (get volume settings)
		set originalVolume to output volume of (get volume settings)
		if muteStatus then set volume without output muted
		set newVolume to 20
		if originalVolume < newVolume then set volume output volume newVolume
	end tell
	try
		do shell script "afplay '/System/Library/Sounds/Ping.aiff'"
	end try
	set volume output volume originalVolume
	if muteStatus then set volume with output muted
	
end tell

Note: I’m Belgian, so some words/comments/variables are in Dutch - but I don’t think that matters…
Any help would be very much appreciated

Model: MacBook Pro 13"
AppleScript: 2.1.2 | 2.3 (118)
Browser: Safari 534.57.2
Operating System: Mac OS X (10.6)

Update: I’ve run the trial version of SCript Debugger, and there seemed to be no problems.
I’m guessing it has something to do with writing the data to the clipboard?

This is really frustrating for I’v been working on this past 2 days and thought I was so close…
Sorry for ‘bumping’ - but I really hope you experts can help me out…

Hi,

Is the part to reorganize the scripting additions («event ascrgdut») really used ?
Actually it’s only needed to load scripting additions dynamically in Cocoa bundles

color=blue the clipboard[/color] is part of Standard Additions, you can omit the System Events tell block

Try to define a handler to clear the clipboard at the end of the script

on clearClipboard()
	set the clipboard to ""
end clearClipboard

then call the handler with

clearClipboard() -- klembord leegmaken

and from the Word tell block with

my clearClipboard() -- klembord leegmaken

It’s also recommended to put “Deel 4” out of the Excel tell block to avoid nested application tell blocks

I really am not sure but I do see one anomaly that might cause you problems. For example sometimes when doing something with a range you use something like this…

set totaalCopy to range "K7" of sheet "ARTIKELS"

And then other times you use this…

select range "A20"

So you are not consistently referring to ranges throughout your code (and probably other objects too). This may be causing you problems. Figure out the correct way to refer to an object and then be consistent. Example - something like this for ranges…

select range "A20" of active sheet of active workbook
set totaalCopy to range "K7" of active sheet of active workbook

Thx for the replies - I will look into it

@ Hank: I know I’m not always that ‘consistent’ - but it works either way - so why is ‘mixing’ that bad?

Stefan,

I’ve tried to implement your suggestion (or at least I think I did…), but it didn’t solve the issue.
The strange thing is that Excel ‘hangs’ for a moment, when having opened the xls-file, does no pasting at all (but has the A20 range selected) and after half a minute staring at the apple-colorwheel asks me to save the (unchanged) document…

EDIT: it also deletes the sheet “ARTIKELS” after the hangup…

This is the adjusted script:



on clearClipboard()
	set the clipboard to ""
end clearClipboard

clearClipboard() -- klembord leegmaken

-- Deel 1 
tell application "Microsoft Word"
	
	activate
	-- einde van document definiëren
	set documentEnd to end key selection move unit a story extend by moving
	-- nieuwe TOC toevoegen aan einde van document
	make new table of contents at active document with properties {include page numbers:false, text object:documentEnd, use fields:false, use heading styles:true, lower heading level:3, upper heading level:1}
	-- | vervangen door tabs
	set tocRange to create range active document start (start of content of text object of table of contents 2 of active document) end (end of content of text object of table of contents 2 of active document) --TOC als tekst-range instellen
	
	execute find find object of tocRange find text "| " replace with "^t" replace replace all
	execute find find object of tocRange find text "^w|" replace with "^t^t^t^t^t^t^t" replace replace all 
	execute find find object of tocRange find text "|" replace with "^t" replace replace all
	my clearClipboard() -- klembord leegmaken
	copy object tocRange
	delete table of contents 2 of active document
end tell

-- Deel 2 
tell application "Microsoft Excel"
	if not (exists document "DET_MES-template.xlsx") then
		open "HD Macbook:Users:ruben:Documents:DET_MES-template.xlsx" --template openen
	end if
	activate workbook "DET_MES-template.xlsx"
	select sheet "ARTIKELS" of active workbook
	select range "A20" of active sheet
	-- copy object copiedText
	paste special on worksheet sheet "ARTIKELS" format "Unicode Text" --TOC uit word als delimited text plakken
	
	
	set totaalCopy to range "K7" of sheet "ARTIKELS"
	set meetlijnenCopy to range "A8:K16" of sheet "ARTIKELS"
	set stopRowNumber to value of range "B1" as integer 
	
	set stijlMeetpost to range "A7:K7"
	
	set rowCount to 19
	
	repeat until rowCount is stopRowNumber
		set rowCount to (rowCount + 1)
		set eenheidCheck to (value of range ("J" & rowCount))
		
		if not eenheidCheck = "" then 
			set insertionPoint to range ("A" & (rowCount + 1) & ":K" & (rowCount + 1))
			activate insertionPoint
			select insertionPoint -- invoegpunt definiëren en selecteren
			set insertSize to get resize insertionPoint row size 9 
			insert into range insertSize shift shift down
			copy range totaalCopy destination range ("K" & rowCount) of sheet "ARTIKELS"
			copy range meetlijnenCopy destination range ("A" & (rowCount + 1)) of sheet "ARTIKELS"
			copy range stijlMeetpost
			paste special range ("A" & rowCount & ":K" & rowCount) what paste formats
			
			set rowCount to (rowCount + 9)
			set stopRowNumber to (stopRowNumber + 9)
		end if
	end repeat
	
	-- Deel 3 
	set artikelsCopy to range ("A20:K" & stopRowNumber) of sheet "ARTIKELS"
	activate object sheet "DET"
	set insertionPoint to range "A4:K4" of sheet "DET"
	set insertSize to get resize insertionPoint row size (stopRowNumber - 19)
	insert into range insertSize shift shift down
	copy range artikelsCopy destination range "A4" of sheet "DET"
	try
		set display alerts to false
		delete sheet "ARTIKELS"
	end try
	
	set meetstaatNaam to get save as filename
	save workbook as active workbook filename meetstaatNaam
end tell

-- Deel 4 
display dialog "Uw Des werd aangemaakt!" buttons {"Sluit af"} with icon 1 default button 1 giving up after 180
-- geluid afspelen
tell application "System Events"
	set muteStatus to output muted of (get volume settings)
	set originalVolume to output volume of (get volume settings)
	if muteStatus then set volume without output muted
	set newVolume to 20
	if originalVolume < newVolume then set volume output volume newVolume
end tell
try
	do shell script "afplay '/System/Library/Sounds/Ping.aiff'"
end try
set volume output volume originalVolume
if muteStatus then set volume with output muted

When you have problems you can’t track down the first thing to do is remove possible sources of errors. I think those are “possible sources of errors”. It may be nothing, but it may be something. You won’t know until you try.

I totally agree - so I adjusted this, where I think it goes wrong… But it doesn’t help

The real question is: how is it possible that it runs (smoothly) within editor and hangs when run from the MS Word script-menu?

Narrowing down to pinpoint the problem I’ve created a simple copy-paste script (Tab-seperated text in Word to columns in Excel):


-- copy selection in Word
tell application "Microsoft Word"
	copy object selection
end tell
-- paste selection in Excel as delimited text
tell application "Microsoft Excel"
	try
		launch
	end try
	if not (exists document "Empty.xlsx") then
		open (path to desktop as Unicode text) & "Empty.xlsx" --open empty workbook
	end if
	activate workbook "Empty.xlsx"
	select sheet 1 of active workbook
	select range "A20" of active sheet
	-- copy object copiedText
	paste special on worksheet sheet 1 format "Unicode Text" --paste as delimited text	
end tell

This essentially does the same as this piece of script in the ‘total’ script:


-- incomplete script! Partial script from initial post

copy object tocRange
	delete table of contents 2 of active document
end tell

-- Deel 2 
tell application "Microsoft Excel"
	if not (exists document "DET_MES-template.xlsx") then
		open "HD Macbook:Users:ruben:Documents:DET_MES-template.xlsx" --template openen
	end if
	activate workbook "DET_MES-template.xlsx"
	select sheet "ARTIKELS" of active workbook
	select range "A20" of active sheet
	
	paste special on worksheet sheet "ARTIKELS" format "Unicode Text"

The result?
Exactly the same: works properly within Editor - hangs when run from scriptmenu in Word…

Try to disable instructions starting from the last one (putting # at front of it) then run the edited script.
Doing that, you will identify which one is the culprit.

My trial version expired late saturday so I can’t make tests here.

Yvan KOENIG (VALLAURIS, France) jeudi 11 octobre 2012 14:41:07

Yvan,

as suspected it’s the last line:


paste special on worksheet sheet 1 format "Unicode Text" 

I don’t see any ‘syntax-errors’, but maybe there is indeed another way to paste the tab-delimited text into excel?
Also: attempting to copy into the specific cell “A20” I’ve tried something like:


paste special on worksheet sheet 1 paste range "A20" format "Unicode Text" 

But this does give a syntax-error…

UPDATE:

changing the ‘formatting-part’ to:

paste special on worksheet sheet 1 format text as text

or

paste special on worksheet sheet 1 format text 

or completely ommitting the ‘format’ part

paste special on worksheet sheet 1 

pastes an ‘image’, with a Red Cross and this message: “The image cannot be displayed. Your computer may not have enough memory to open the image, or the image may have been corrupted. Restart your computer, and then open the file again. If the red x still appears, you may have to delete the image and then insert it again.

So my guess is it has something to do with the formatting. Unfortunately I haven’t been able to find any resources on this specific subject giving me the available options…

Hello

Are you sure that you must enclose Unicode Text between quotes ?

I would try with :

paste special on worksheet sheet 1 format Unicode Text

or

paste special on worksheet sheet 1

Yvan KOENIG (VALLAURIS, France) jeudi 11 octobre 2012 18:11:09

PS

Re-reading your late message, I’m wondering about the content of the clipboard.
Is it text one or is it a picture one.

If you want to be sure that you paste Unicode Text, you may use this instruction:

set the clipboard to the clipboard as Unicode Text

This time, I assume that pasting without specified format will necessarily paste Unicode Text.

Hello

May you run the script with the paste instruction disabled.

When the script is ended, run this simple script from the script editor :


the clipboard as record

The log report will display what was stored in the clipboard.
With a bit of luck it will put you on the right track for the way to describe the format.

I did the trick with Pages.

I got :

{«class rtfd»:«data rtfd72746664000000000300000002000000070000005458542.»,
«class RTF »:«data RTF 7B5C727466315C616E73695C616E7369637067313235325C636F636F.»,
«class utf8»:"aze sdf
",
«class ut16»:"aze sdf
",
uniform styles:«data ustl0200000090000000000000001400000020000000010.»,
string:"aze sdf
",
scrap styles:«data styl0100000000000E001100150000130C00000000000000»,
Unicode text:"aze sdf
"}

Yvan KOENIG (VALLAURIS, France) jeudi 11 octobre 2012 18:51:00

Hi all,

it has been a while since my last post, but thanks to the tip Yvan gave, I was able to pin-point the problem. I got the script into pasting ‘something’, but it was never the table that was copied to the clipboard. So the problem lay within the transferring.

The solution was telling the clipboard to treat the data as unicode text and just paste it, instead of using the “paste special on worksheet” command. This is the piece of script that was fundamentally different from the previous versions:

	execute find find object of tocRange find text "|" replace with "^t" replace replace all
	select tocRange
	copy object selection
	set the clipboard to (the clipboard as Unicode text)
	
end tell

-- Deel 2 
tell application "Finder"
	set bootDisk to (get name of startup disk) as string
end tell
tell application "Microsoft Excel"
	try
		launch
	end try
	activate
	set screen updating to false
	if not (exists document "DET_MES-template.xltx") then
		open (bootDisk & ":Applications:Microsoft Office 2008:Office:Media:Templates:_ sometemplate:DET_MES-template.xltx")
	end if
	set meetstaatXls to active workbook
	activate workbook meetstaatXls
	select sheet 1 of active workbook
	select range "A20" of active sheet
	paste special on worksheet sheet 1
end tell

tell application "Microsoft Excel"
	activate
	activate workbook meetstaatXls
	select sheet "ARTIKELS" of active workbook
	
	set totaalCopy to range "K7" of sheet "ARTIKELS"
	set meetlijnenCopy to range "A8:K16" of sheet "ARTIKELS"

I hope this solves similar problems for other users in the future.
Thanks for all the help - it not only pointed me toward the solution but also thought me some other interesting things…

(BTW - maybe somewhat off-topic: but there seems to be a bug in the Word-equivalent of the “screen updating” command…)