Excel Is Our Friend – Series Introduction

Blog Post created by rkoonce on Nov 6, 2013
Excel Is Our Friend

With the initial “Welcome to Treasure Island” blog post, I answered the What, Why and Who. With subsequent blog entries, it is all about the How. I’d like to start off with something not directly PI related… but often used in tandem, Microsoft Excel. I will be the first to admit, I love Excel, hence the naming of my first blog series – “Excel Is Our Friend”.

It is only natural that OSIsoft would have add-ins for the “duct tape” that holds the business world together. I marvel at the fact that our higher education systems hasn’t fully grasped its role in the big scheme of things. To me it would seem perfectly logical for someone to make the statement, “ I have a bachelor’s in business, but I continue to focus on achieving my doctorate in Microsoft Excel.”

I plan to discuss Excel here, initially talking through some overlooked “treasures” and features. But as I proceed, the add-ins will inch into the discussion, including those delivered by OSIsoft. And since OSIsoft products are VBA compliant, I plan to jump in with both feet there, as well. Often, during Bomgar and WebEx sessions, work is halted when someone says “wait, how did you do that?” (I admit it, sometimes it is me that is speaking). Thinking about it now, I realize that the question is usually asked with some urgency, for fear of missing the opportunity. There are so many little things that can be done the hard way, and it is those which compel us to ask the question.. “How did you do that?” I hope you find some answers here, without having to ask.

Let me tell you about the island that I found myself upon. It was a maze of PI interfaces, with a variety of data sources, talking to several PI Servers. Some tags were input and some output. I had working samples that I could not easily find or quickly trace. I needed a map to find my way around. So, I pointed my mouse at Excel and started making a list… in the blog entries that follow.

Excel Is Our Friend – Series Overview

  • Series Introduction
  • Part I – Exploring the Island
    • Navigating Excel
    • Moving Around and Selecting
    • Utilizing shortcuts with PI DataLink
  • Part II – Foundation - Building your shelter
    • Environment
      • Developer Ribbon
      • ActiveX Controls
      • Adding a Reference to a Project

  • Part III – Toolkit - Building a raft
    • Spreadsheet functions
    • Nested Functions
    • User Defined Functions – “roll your own”
    • System Overload
  • Part IV – Toolkit – adding a sail
    • Microsoft Scripting Runtime, aka FSO
    • VBA and FSO – using File System Object
  • Part V – Recycling
    • Personal.xlsm, aka MyFunctions Add-in
    • Recording Macros - Let Excel teach you VBA
    • Challenge