Excel – Graphical Scheduled Usage Viewer for Production Equipment

1012010

January 1, 2010

This article is based on a sample included in a presentation that I conducted a couple of months ago. Essentially, this example pulls utilization schedules from multiple equipment resources and generates both an equipment resource specific calendar and a graphical overview of the calendars for all equipment resources. This demonstration uses ADO with a direct connection to an Oracle database for both scheduling the work orders that need to be processed at each of the equipment resources (using a very weak scheduling algorithm that does not go back to fill in unused time), and to create the graphical results in Excel.

The options area for the original Excel spreadsheet used in my presentation looked like this, but we will not create anything that complex in this article:

–

Before starting, we need to create source data for our example (note that some of the tables created are only needed for a short period of time, and may be dropped once all of the tables are created):

We now switch to Excel to build the interface area with two ActiveX command buttons:

First, name the first worksheet as ShopCalendar – this name is very important since it is referenced in the code for this example. Next, add two ActiveX command buttons in the first two rows. Give the first one the name cmdCreateSchedule, and the second one the name cmdDrawCalendar – the names are not overly critical, but the names are referenced in the code. Finally, double-click one of the buttons to access the Code Editor.

Delete any code that is shown in the Code Editor and add the following code:

The above code is the basic logic needed to permit connecting to the Oracle database. Next, add a reference to the ActiveX Data Objects. Now we need to add the code for the scheduling routine – this code uses bind variables to minimize the number of hard parses:

Switch back to the Excel window and turn off Design Mode. Clicking the Create Schedule button should populate the T_RESOURCE_DAILY_SCH table. Once that code finishes executing, switch back to the Code Editor and add the following below the rest of the code:

With the above code added, the Draw Calendar button should now work. Click the Draw Calendar button, the output should look something like what you see below:

A second tab is added to the worksheet that shows a graphical overview, with cyan colored bars representing operations that will complete before the work order due date and red colored bars representing operations that will complete after the work order due date:

I may post a follow-up at some point that shows how to generate an enhanced Windows metafile (EMF) which shows the graphical view of the resource utilization.

Flavio,
If you have a copy of Microsoft Excel, even the 2016 version, you should be able to very quickly create the sample Excel files by following the directions in this article. All of the necessary code is included in the article. I originally created this example for a presentation that I gave to a Visual Manufacturing user group in June 2009. If you are a member of the VMIUG-TEC Yahoo group, you can access the full set of sample files and my presentation from that group’s file area.

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: