Since opening a new office in Phoenix to support our customers on the West Coast, some evenings in Minitab technical support feel busier than others. (By evenings, I mean after 5:30 p.m. Eastern time, when the members of our tech support team in Pennsylvania go home for the day, and I become an office of one.)
The variability in terms of days that felt extremely busy versus days that didn’t seemed unpredictable, so I decided to keep track of that information in an Excel spreadsheet, which I’ve been updating each evening:
After gathering this information for several months, I used the data to make a few graphs in Minitab to see if any particular days were busiest. The graphs were fun, but not exactly what I needed. I wanted an easy way to make Minitab produce the graphs automatically each morning, so that they reflect the most up-to-date information.
In this post, I’ll show you the steps I took to link my Excel file to my Minitab worksheet and how I automated the generation of the graphs. You can do the same thing with any data you record regularly in Excel spreadsheets.
Creating a DDE Link from Excel to Minitab
The first step was to create a DDE link from Excel to Minitab. To do that, I highlighted and copied a range of cells from Excel, beginning with the first row of data, and extending well beyond my last row of data (I went down to row 500):
After copying the data from Excel, I navigated to my Minitab worksheet, clicked in the column where I want to link the data, and then used Edit > Paste Link:
After creating the link, the data is automatically imported from Excel into Minitab:
Since I have three columns to link from Excel to Minitab, I repeated the copy/paste process again for the two other columns, until all three columns were linked. I also added titles to the columns in my Minitab worksheet:
Now with the links in place, any time I update my Excel file, the data is automatically updated in Minitab. Since the data is being transferred from Excel to Minitab, one important thing to remember is that for these links to continue working, Excel must be opened before opening Minitab each day.
Adding a Macro to the DDE Links
As a next step, I created a Minitab macro with the commands needed to manipulate the data that is imported and generate the graphs.
After saving the commands for the graphs I wanted to create in a GMACRO titled busydays.mac, I used the Edit menu shown below to add my macro to my DDE link:
The Manage Links menu shows the links for each column in the order in which the columns were linked. First I linked C1, then C2, and then C3, so the last link listed corresponds to C3, which is the last column of data that is imported. Therefore, I’ll add my macro to that link so that my graphs will be generated after all the data is imported by highlighting that option and clicking the Change button:
After opening the link, I just added the macro to the Commands field—the % symbol tells Minitab to look for the Busydays macro in my default macro location. Finally I clicked the Change button to save the change to the link:
As a final step, I saved the Minitab project file with all the links that I added.
Now each morning when I come to the office, I open the Excel file first, then open my Minitab project file and I just watch the magic happen:
