Is this efficient code? Noob seeks guidance

Hi all,

I wrote an AS to take a table that was generated in Word and transfer it to Excel via a text file. The table in Word contains some entries with certain cells empty, which I delete prior to the creation of the text file that is passed to Excel. It also, user queried, creates a email message containing the file to be sent. THere is also some Growling of status messages along the way too. As a relative noob to the world of AS, I was just wondering if this was efficient code or if there was another way in which to do this. I look forward to hearing back.

Thanks!
Mark

tell application "System Events" to set GrowlRunning to exists application process "GrowlHelperApp"
my NotifyMe(wordMsg, "Microsoft Word", GrowlRunning)
tell application "Microsoft Word"
	open alias wdPath with read only
	set myTable to table 1 of active document
	set theRows to every row of myTable
	--Delete all rows with blank zip code
	repeat with i from (count theRows) to 2 by -1
		set theRow to item i of theRows
		if content of text object of cell 8 of theRow is equal to (return & (ASCII character 7)) then
			delete theRow
		end if
	end repeat
	-- Convert Table to Text & save as text file
	set aRange to convert to text myTable separator separate by tabs
	save as active document file name tempPath file format format text
	close document wdPath saving no
	close document tempPath saving yes
end tell
-- Open text file in Excel
my NotifyMe(excelMsg, "Microsoft Excel", GrowlRunning)
tell application "Microsoft Excel"
	open text file filename tempPath data type delimited field info {{1, text format}, {2, text format}, {3, text format}, {4, text format}, {5, text format}, {6, text format}, {7, text format}, {8, text format}} with tab
	autofit every column of range "A:Z"
	-- Sort the sheet by zip code
	tell active sheet
		sort range "A1" order1 sort ascending key1 column 8 header header yes without match case
	end tell
	-- Delete old version of Excel File if it exists
	tell application "Finder"
		if exists file excelPath then
			delete excelPath
		end if
	end tell
	save workbook as active workbook filename excelPath file format workbook normal file format
	tell application "Finder"
		delete tempPath
	end tell
end tell
quit application "Microsoft Excel"
quit application "Microsoft Word"
-- Place dialog box here to ask if list should be emailed
set emailDialog to display dialog "Do you want to eMail the file (Y/N)?" buttons {"OK"} with icon 1 default answer "N"
set emailResponse to text returned of emailDialog
if emailResponse = "Y" then
	--Set the attachment, body, recipient, sender, and subject variables
	my NotifyMe(mailMsg, "Mail.app", GrowlRunning)
	--Prompt the user to select which account to send this message from
	tell application "Mail"
		--Properties can be specified in a record when creating the message or
		--afterwards by setting individual property values
		set newMessage to make new outgoing message with properties {subject:theSubject, content:theBody & return & return}
		tell newMessage
			--Default is false. Determines whether the compose window will
			--show on the screen or whether it will happen in the background
			set visible to true
			set sender to theSender
			make new to recipient at end of to recipients with properties {name:theName, address:theAddress}
			tell content
				--Position must be specified for attachments
				make new attachment with properties {file name:theAttachment} at after last paragraph
			end tell
		end tell
		--Bring the new compose window to the foreground, in all its glory
		activate
	end tell
else
	my NotifyMe(completeMsg, "AppleScript Editor.app", GrowlRunning)
end if

to NotifyMe(msg, appIcon, Growler)
	if Growler then
		tell application "GrowlHelperApp"
			register as application "Word to Excel Script.scpt" all notifications {"Note"} default notifications {"Note"} icon of application appIcon
			notify with name "Note" title "Mailing List Generator" description msg application name "Word to Excel Script.scpt"
		end tell
	else
		display dialog msg with title "Mailing List Generator" with icon 0
	end if
end NotifyMe

Model: iMac
AppleScript: 2.1.1 (ASE 2.3)
Browser: Safari 531.21.10
Operating System: Mac OS X (10.6)

The table can be copied without going through a text file.
Blank rows can be easiely removed after they get to Excel, by using Special Cells

tell application "Microsoft Word"
	copy object text object of table 1 of active document
end tell

tell application "Microsoft Excel"
	make new workbook
	paste worksheet active sheet
	try
		delete range (entire row of (special cells (cells of column 3) type cell type blanks))
	end try
end tell

Thanks! I thought there was an easier way, I just couldn’t figure out getting the table out of table format in Word AND into Excel without the text file pass. Noob mistake, but I learned so it won’t happen again. Thanks also for the Excel delete, that definitely will make it go faster than deleting out line by line in Word as I was. Must read more on the Special Cells.

Cheers,
Mark

The thing to remember about Special Cells is that Excel will fall over if no cells meet the specification.
In this case, if all of column (oops, I forgot to switch from my testing 3 to your posted 8)
If none of the cells in that column are blank, the script will crash, hence the Try block

I ran into one issue. With doing the copy/paste, the column with zip codes in it doesn’t paste the zips with leading zeros properly (i.e. 03456 pastes in Excel as 3456). Any way to get this to paste properly?

Thanks!

I’m at work and can’t work up the script, but two quick thoughts:

  1. after import, the destination cells could be formatted to show the leading “0” with Excel’s zip code format(the underlying value in the cell would still be a number).

  2. before import, the zip column could be formatted as Text (I’d try to avoid this option, cells formatted as text often lead to downstream difficulties.)

This won’t work as the cells need to be really with the leading 0 included and not masked to look like it. The file is imported into another program for reading.

This maybe the fix…doesn’t matter if it is formatted as text or not. Hopefully thee paste special will leave the cells alone and not reformat to number after pasting.

thanks,
Mark

Got it to work…Thanks for all the help! I attached the code in case it is useful to someone else as well.

tell application "System Events" to set GrowlRunning to exists application process "GrowlHelperApp"
my NotifyMe(wordMsg, "Microsoft Word", GrowlRunning)
tell application "Microsoft Word"
	open alias wdPath with read only
	copy object text object of table 1 of active document
	close document wdPath saving no
end tell
-- Open text file in Excel
my NotifyMe(excelMsg, "Microsoft Excel", GrowlRunning)
tell application "Microsoft Excel"
	make new workbook
	tell cells of column 8
		set number format to "@"
	end tell
	paste special on worksheet active sheet format "Unicode Text"
	try
		delete range (entire row of (special cells (cells of column 8) type cell type blanks))
	end try
	autofit every column of range "A:Z"
	-- Sort the sheet by zip code
	tell active sheet
		sort range "A1" order1 sort ascending key1 column 8 header header yes without match case
	end tell
	-- Delete old version of Excel File if it exists
	tell application "Finder"
		if exists file excelPath then
			delete excelPath
		end if
	end tell
	save workbook as active workbook filename excelPath file format workbook normal file format
end tell
quit application "Microsoft Excel"
quit application "Microsoft Word"
-- Place dialog box here to ask if list should be emailed
set emailDialog to display dialog "Do you want to eMail the file (Y/N)?" buttons {"OK"} with icon 1 default answer "N"
set emailResponse to text returned of emailDialog
if emailResponse = "Y" then
	--Set the attachment, body, recipient, sender, and subject variables
	my NotifyMe(mailMsg, "Mail.app", GrowlRunning)
	--Prompt the user to select which account to send this message from
	tell application "Mail"
		--Properties can be specified in a record when creating the message or
		--afterwards by setting individual property values
		set newMessage to make new outgoing message with properties {subject:theSubject, content:theBody & return & return}
		tell newMessage
			--Default is false. Determines whether the compose window will
			--show on the screen or whether it will happen in the background
			set visible to true
			set sender to theSender
			make new to recipient at end of to recipients with properties {name:theName, address:theAddress}
			tell content
				--Position must be specified for attachments
				make new attachment with properties {file name:theAttachment} at after last paragraph
			end tell
		end tell
		--Bring the new compose window to the foreground, in all its glory
		activate
	end tell
else
	my NotifyMe(completeMsg, "AppleScript Editor.app", GrowlRunning)
end if

to NotifyMe(msg, appIcon, Growler)
	if Growler then
		tell application "GrowlHelperApp"
			register as application "Word to Excel Script.scpt" all notifications {"Note"} default notifications {"Note"} icon of application appIcon
			notify with name "Note" title "Mailing List Generator" description msg application name "Word to Excel Script.scpt"
		end tell
	else
		display dialog msg with title "Mailing List Generator" with icon 0
	end if
end NotifyMe