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.
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?
I’m at work and can’t work up the script, but two quick thoughts:
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).
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.
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