Huge Presentation – Working with Oracle Database in C#, VBScript, and Excel; Enhancing Visual Manufacturing 8.0.0

November 2, 2016

In late September 2016 I gave a somewhat long presentation that lasted roughly four and a half hours.  Surprisingly, the only glitch that I encountered during the live demonstration portion of the presentation was due to an apparent bug, at least in Excel 2010, that causes long object names (specifically checkbox names on the YearEnd tab) to be discarded when the .XLSX file format is used rather than the .XLS file format.  This Huge Presentation required roughly two months to put together, and half of that time was spent updating the contents of a presentation that I originally gave in 2009 that originally required three months to assemble.  I initially used Windows 10 and Excel 2016 for most of the updates, and then found significant compatibility problems when trying to test the live demonstration portion of the presentation with Windows 7 and Excel 2010… so, I spent literally days fixing the presentation contents so that Windows 7 and Excel 2010 were supported again.

As far as I am aware, the live presentation was not recorded.  However, I managed to sneak out a copy of the slide deck of the presentation (210+ slides, most with an extensive Notes section); the full source code of the examples – some of which were written in C#, Visual Basic 6.0, VBScript, Excel macro language, or the macro language used by Visual Manufacturing; and more.  The presentation and support files that I created as part of the presentation may be downloaded from Google Drive at this link.  The Crystal Reports free runtime support files, which are required to create PDF versions of Crystal Report files from a VBScript, and within the C# IIS example project, may be downloaded from Google Drive at this link. On the Google Drive site, click the Download button to save the Visual8MacrosAndExcel.zip file to your computer.  Once the download completes, extract the contents of that file to a new C:\Visual8MacrosAndExcel folder in the root of the C:\ drive (some of the examples specifically refer to this folder name – adjust the examples as necessary if you extract the files to a different location).  Some of the examples may also require a C:\Visual folder to be created.

So, what is in the presentation and support files?  A huge collection of programming examples that are usable essentially out of the box for people using Infor’s Visual Manufacturing 8.0.0 with an Oracle Database backend.

• For people using older versions of Visual Manufacturing with an Oracle Database backend, take a look at the 2009 version of the presentation which may be downloaded from the VMIUG-TEC Yahoo group’s files area.
• For people using Visual Manufacturing 8.0.0 with a SQL Server backend, all I can say is good luck with converting the code examples – a few people have successfully converted a handful of the examples.
• For people who have no experience with Visual Manufacturing, but are interested in programming against an Oracle Database backend, the examples in the presentation could be very helpful.
• For people who are bored, just want to listen to roughly 4.5 hours of computer generated speech (likely sounds best on Windows 10), or suffering from severe sleep insomnia, download the presentation and click the Read to Me button on every slide in the presentation.

So, what is in the presentation?  A partial list:

• 135+ VBScript or Visual Manufacturing macros.
• At least 13 custom helper EXE or DLL files, including a few to help specifically with minor cases of sleep insomnia, that may be used within VBScript, Excel macros, and other programs.  There is even a custom helper that allows inserting a web page into just about any program window.
• C# ASP.Net (IIS) project that allows selecting data from the database based on date ranges or other criteria, with the results returned to the client in web pages using HTML tables (Repeater control or the “hard way” with HTML table code created manually), or potentially sent to Microsoft Excel or PDF files (with Crystal Report files as the report source).
• Potential solutions for various problems encountered when trying to use the C# project with IIS.
• Visual Manufacturing 8.0.0 “macro encyclopedia” – listing/detailing essentially every macro variable that is available in Visual Manufacturing 8.0.0.
• Executing Microsoft Excel macros, Excel functions, and using Excel user forms from within a VBScript or Visual Manufacturing macro.
• Printing a label to a Zebra printer that supports the ZPL language using a VBScipt file with the help of an Excel macro.
• Generating multiple Microsoft Outlook emails from a VBScript file that accesses an Oracle Database.
• Determining if the person logged into the computer is a member of an Active Directory group within a VBScript or Visual macro so that an action may be allowed or denied.
• Basics of writing SQL statements.
• Enabling the Developer/macro functionality in Microsoft Excel 2016.
• Methods of retrieving and using Oracle Database data in Microsoft Excel, and potentially pushing data back into the database.
• Executing Windows Management Instrumentation (WMI) calls in Microsoft Excel against the local computer and/or other computers in the Active Directory domain.  For domain administrators, for example, this functionality would permit retrieving a list of every running process on every computer in the domain, and then selectively killing specific processes running on a specific remote computer (or starting a process on that remote computer).
• Extracting most or all of the icons that are built into Microsoft Excel.

If you find the presentation or the support files helpful, feel free to leave a comment.  If you need assistance with some of the examples, I will do what I can to help as time permits.  If you need help converting the examples to work with a SQL Server backend… you are on the wrong blog – I cannot help with that conversion.

(Updated with a few pictures from the presentation slides November 3, 2016):

C# ASP.Net Project:

Part Search page running in the Edge browser in Windows 10

Part Search page running in the Visual Manufacturing banner – top half of output

 

Part Search page running in the Visual Manufacturing banner – bottom half of output

 

VMBrowse page in the Visual Manufacturing banner – Part Browsing

 

VMBrowse page in the Visual Manufacturing banner – Work Order Browsing

 

Labor Ticket search page embedded into the Manufacturing Window

 

Excel Sample Project:

Windows Management Instrumentation control panel in Excel

 

Extract Excel’s built-in icons

 

Resource Check – a very light-weight version of Nagios

 

Resource Check – graphical view, red shows down status

 

Exploded Structure Viewer

 

Showing database table relationships using defined foreign keys – Querying Excel

 

Material Planning in Excel

 

Multi-level Material Planning review in Excel

 

Multi-level cost analysis in Excel