This is the second part of Mac Automation: automating Microsoft Office 2008. In this how-to, I will show you how to create an importer for Microsoft Excel 2008. This importer will allow you to type text in TextEdit.app (or other text editor) and import it into an Excel file.For this automation, you will need the following Automator actions (in the same order):
- Create New Excel Workbook
- Set Excel Workbook Properties
- Get Specified Finder Items
- Import Text Files to Excel Workbook
- Save Excel Workbooks
Running the workflow
To run the workflow, just fill in the required information into the proper Automator actions. Specify information in the "Set Excel Workbook Properties" and "Save Excel Workbooks." In the "Import Text Files to Excel Workbook," select "Tab" under the "Delimiters" section -- this allows you to specify between columns/rows.
Next, let's look at how you need to format your text documents. When you are typing the data that you want to go in the spreadsheet, use a tab to separate the columns and a return to separate the rows. Your document should look similar to the one that I have created below. Note that I have a tab each time I want a new column and a return where I want a new row.
Be sure to save your text in the proper format! This Automator action requires that you save it as either: .txt, .csv, .htm, or .xml. Any other format will not work. I would recommend using a .txt format. Once you have your text saved, click the "Add" button in the "Get Specified Finder Items" action and navigate to your saved text file.
You can now click the "Run" button in the top-right corner of Automator. Your workflow will start working and create the new Excel workbook and import the text in the proper cells.
Saving the workflow
You can now save the workflow to your liking.
Application of this workflow
You can use this workflow to bridge the gap between someone that uses Excel and someone that doesn't. You can also use it to create Excel documents on-the-go on, say, an iPhone. Just e-mail yourself a document. You can use periods to denote new columns and line breaks to denote new rows. Reminder: if you use a different text formating for rows/columns, be sure to specify that in the "Import Text Files to Excel Workbook" action.
To run the workflow, just fill in the required information into the proper Automator actions. Specify information in the "Set Excel Workbook Properties" and "Save Excel Workbooks." In the "Import Text Files to Excel Workbook," select "Tab" under the "Delimiters" section -- this allows you to specify between columns/rows.
Next, let's look at how you need to format your text documents. When you are typing the data that you want to go in the spreadsheet, use a tab to separate the columns and a return to separate the rows. Your document should look similar to the one that I have created below. Note that I have a tab each time I want a new column and a return where I want a new row.
You can now click the "Run" button in the top-right corner of Automator. Your workflow will start working and create the new Excel workbook and import the text in the proper cells.
Saving the workflow
You can now save the workflow to your liking.
Application of this workflow
You can use this workflow to bridge the gap between someone that uses Excel and someone that doesn't. You can also use it to create Excel documents on-the-go on, say, an iPhone. Just e-mail yourself a document. You can use periods to denote new columns and line breaks to denote new rows. Reminder: if you use a different text formating for rows/columns, be sure to specify that in the "Import Text Files to Excel Workbook" action.
Reader Comments (Page 1 of 1)
3-17-2008 @ 10:01AM
Jon said...
Thank you for writing this; it is very helpful.
For more advanced usage, has anyone figured out how to make a custom function in Excel 2008? From the (very limited) docs, it sounds like it should be possible to make one written in AppleScript, but I can't figure out how to trigger the AppleScript from anything but the Scripts menu. (A custom function should be run from a cell, as in =RunMyFunction(A1, B1, C1)).
A call to Microsoft tech support didn't help, as they say that they explicitly do not support the AppleScript functionality in Excel 2008.
Reply
4-30-2008 @ 8:32PM
Tyler said...
How do I get an action to Save Excel Workbook, that will work with Excel 2004?
3-17-2008 @ 1:26PM
Danny (from Scotland) said...
I'm interested to see an example of how to do things in Automator. So thanks for that. But given the "Application of this workflow" suggested, I'd recommend just doing copy and paste. If you copy tab separated text from pretty much anywhere, Excel will place it in columns for you and isn't that easier than importing (whether it's 'automated' or not).
Danny
(I'm assuming that in Excel 2008 they've not removed this functionality... though given what I've heard about Office 08 to date this may be an unsafe assumption....)
Reply