Automate tasks in Microsoft Excel on a Mac

 Automate tasks with the Macro Recorder

To automate a repetitive task, you can record a macro with the Macro Recorder in Microsoft Excel. Imagine you have dates in random formats and you want to apply a single format to all of them. A macro can do that for you. You can record a macro applying the format you want, and then replay the macro whenever needed.

Record a macro

There are a few helpful things you should know about macros:

• When you record a macro for performing a set of tasks in a range in Excel, the macro will only run on the cells within the range. So if you added an extra row to the range, the macro will not run the process on the new row, but only the cells within the original range.
• If you have planned a long process of tasks to record, plan to have smaller relevant macros instead of having one long macro.
• It is not necessary that only tasks in Excel can be recorded in a macro. Your macro process can extend to other Office applications, and any other applications that support Visual Basic Application (VBA). For example, you can record a macro where you first update a table in Excel and then open Outlook to email the table to an email address.

Macros and VBA tools can be found on the Developer tab, which is hidden by default, so the first step is to enable it.

1. Go to Excel > Preferences… > Ribbon & Toolbar.
2. In the Customize the Ribbon category, in the Main Tabs list, select the Developer check box, and then click Save.

Follow these steps to record a macro.

1. On the Developer tab, click Record Macro.
2. In the Macro name box, enter a name for the macro. Make the name as descriptive as possible, so you can quickly find it if you create more than one macro.Note: The first character of the macro name must be a letter. Subsequent characters can be letters, numbers, or underscore characters. Spaces cannot be used in a macro name; an underscore character works well as a word separator. If you use a macro name that is also a cell reference, you may get an error message that the macro name is not valid.
3. In the Store macro in list, select where you want to store the http://macro.In general, you’ll save your macro in the This Workbook location, but if you want a macro to be available whenever you use Excel, select Personal Macro Workbook. When you select Personal Macro Workbook, Excel creates a hidden personal macro workbook (PERSONAL.XLSB) if it does not already exist, and saves the macro in this workbook. Workbooks in that folder are opened automatically whenever Excel starts, and any code you have stored in the personal macro workbook will be listed in the Macro dialog, which is explained in the next section.
4. To assign a keyboard shortcut to run the macro, in the Shortcut key box, type any letter (both uppercase or lowercase will work) that you want to use. It is best to use key combinations that don't correspond to existing shortcut keys, as a macro shortcut key will override any equivalent default Excel shortcut key while the workbook that contains the macro is open.
5. In the Description box, optionally type a brief description of what the macro does.Although the description field is optional, it is recommended you enter one. It's helpful to enter a meaningful description with any information that may be useful to you or other users who will be running the macro. If you create a lot of macros, the description can help you quickly identify which macro does what, otherwise you might have to guess.
6. Click OK to start recording.
7. Perform the actions that you want to record.
8. On the Developer tab, click Stop Recording.

Working with recorded macros in Excel

In the Developer tab, click Macros to view macros associated to a workbook. This opens the Macro dialog box.

Note: Macros cannot be undone. Before you run a recorded macro for the first time, make sure that you’ve either saved the workbook where you want to run the macro, or better yet work on a copy of the workbook to prevent unwanted changes. If you run a macro and it doesn’t do what you want, you can close the workbook without saving it.

Use the Developer tab to create or delete a macro in Excel for Mac

To automate a repetitive task so that you can do the task again with a single click, you can use the Developer tab in Excel for Mac to record and run a macro. You can also create a macro by using the Visual Basic Editor in Microsoft Visual Basic for Applications (VBA) to write your own macro programming code. If you no longer use a macro, you can delete it.

Mac Automation: automating Microsoft Excel 2008

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

Continue reading to learn how to automate this process.

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

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.