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…
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
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.
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
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"
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…
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.
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…)