Introduction
Welcome to this live presentation titled OPC to Excel. My name is Randy and I am the presenter today. We’re going to talk about the problem we’re trying to solve. I’ll explain the solution itself and then I will show you an Excel connection where I’m reading and writing real-time data. We will also create reports from historical data. I will also show you how to run all of this as a Windows service. Of course, no login required. At the very end, I will tell you how to get a copy of this webinar recording, where to get quick setup videos so you can get this going on your own, and how to get an invitation to the next webinar.
The Problem: Getting OPC Data into Excel
Excel really is the single most popular analysis tool in automation. It’s very widely used and very well understood by folks in automation. Why? Because it’s easy — very easy to create calculations and reports based on your data. And we see people doing this all the time. The problem is: how exactly do we get data from a controller, a PLC, DCS, or RTU right into Excel?
We’ve got data from controllers, PLCs, whatever, and we want to get that data into Excel. Of course, we have OPC servers to do this, but Excel can’t reach OPC servers directly. So, we’re going to show how OPC Expert enables us to get the data from OPC servers.
In this case, we’ll have three steps. Step one: capture the real-time data. Step two: create the historian. Step three: produce reports based on real-time and historical data. You may only need one of these. Maybe you’re not interested in real time and only want historical data, or the reverse. All you need to do is one of these. I’m going to do both in this demonstration so you’ll have an idea of what’s going on.
Excel and Platform Support
As far as Excel support goes, you get 32-bit and 64-bit support with Excel 2003 all the way to Excel 2021 and Office 365 — pretty much all versions of Excel are supported. You can get this going from Windows XP all the way to Windows 11. If you’re using Windows Server, it goes from Windows 2003 all the way to Windows 2025. So from something very old to something very new.
You get communication with OPC UA — that’s both DA for real-time data and UA — as well as OPC Classic DA and HDA. You can connect to local and remote OPC servers using DCOM, HTTP, or even MQTT. OPC Expert and these OPC servers can be on the same PC, on remote PCs, or if you’re using OPC on a device, the OPC server can even be directly on the PLC. OPC Expert and the OPC server do not have to be on the same computer.
Also pay attention here: there’s OPC UA HDA and OPC Classic HDA. What that means is OPC Expert can enable you to get data from a remote or local historian. So if you’re using PI or any historian with a UA or HDA server, you can get that data into Excel. It doesn’t just have to be OPC Expert’s own historian — you can get it from remote historians as well.
Asynchronous and Synchronous Data
All reading and writing is done asynchronously, which means you get report-by-exception or on-data-change subscriptions. In other words, you don’t have to keep polling the OPC server asking for the value over and over. There’s no need to do that whatsoever. OPC Expert does that for you and brings the value right into Excel. It’s event-based. If you’re using VBA, you don’t actually need to wait in the source code — you have the data immediately as soon as it changes.
However, you can also do synchronous reads and writes. This is optional. Sometimes if you’re writing code, it’s a little bit easier for programmers to use synchronous reading and writing to get their code working. So if you’re using VBA or user-defined functions (UDFs), it’s sometimes easier to code that way — meaning if you’re writing a value, you can set your code to write the value and if there’s success, then you move on. You can do synchronous or asynchronous. The synchronous option is entirely optional.
Inside OPC Expert
Let me explain what’s inside OPC Expert. Everything I’m showing you here is inside OPC Expert — it’s really a white-box view. We begin with OPC DA and UA clients, and these enable you to ingest data into OPC Expert’s intelligent engine. The data comes right in via DA or UA, and then goes out using Excel’s real-time data (RTD) server. This is what Microsoft uses to bring real-time data into Excel — it’s event-based, report by exception. This is what enables asynchronous data. It’s very efficient. There’s no more polling.
Since the data is inside OPC Expert, you can also send it out to an OPC DA server or UA server, which means a separate UA or DA client can connect to OPC Expert and get this data. This also includes an MQTT broker, which means you can send data out to an MQTT broker, but you can also bring data in via an MQTT broker and out to Excel. So Excel can be used to read data from MQTT brokers as well.
Other ways to bring data in include databases like Microsoft SQL, MySQL, Azure, Amazon, or CSVs. You can also connect with HDA alarms and events, and even send data out using a REST API. That REST API connection enables you to get data into non-Windows platforms, remote connections, and so on. All the components you see here are included inside OPC Expert. In this webinar, we’re specifically concerned with the OPC DA client, UA client, and the RTD server.
Demonstration: Setting Up OPC Expert
We’ll have a couple of controllers with data coming in from OPC servers, and I’ll show you how to set up OPC Expert for that. We’ll do three things: capture the real-time data, archive it in a historian so we have historical data, and then create reports in Excel. Steps one and two are really just setup to enable step three — showing the OPC data.
Installing and Starting OPC Expert
When you download OPC Expert, you get one executable. When you run it, OPC Expert extracts all the files it needs and gets going. There’s no installation. There are no changes to the Windows registry. When you’re done, you can remove all of the files quite easily.
Since we want to use Excel, we go to Tools, Options, and enable Excel features right in there, then click OK. Now, when we expand the computer view, we immediately see two OPC DA servers and two OPC UA servers. I’ll connect to the Modbus OPC server, go to Numeric, and subscribe to a few items. There’s no limit on the number of items you subscribe to. I’ll also connect to the Kepware OPC UA server. Kepware provides OPC UA servers. And here we go — we have a bunch of items with different data types, and we can see data coming in and changing every second.
Creating the Archive
We’re getting real-time data, so now I’ll create an archive. I right-click and choose to archive. You can archive in many different places, but I’m going to choose CSV. There are a whole bunch of options, but we’ll click OK and use the defaults. OPC Expert sets up the archive — we’ve got 12 items and they’re all good, showing green across the board.
Running as a Windows Service
Now let me run this as a service. We’re going to take the OPC Expert UA server and run it as a service. You can see it’s setting up and running under a system account. Once that’s done, we no longer need the GUI. We’ll also install the archive itself as a service so that if we ever reboot the computer, OPC Expert can get up and run and set up the archive automatically — without anybody being logged on. When you get into Excel, you can retrieve all the historical data that was collected even when no one was logged on.
Reading Real-Time Data in Excel
All the data is being archived. Let’s start Excel and open a blank workbook. To get real-time data, I take the items in OPC Expert and simply drag and drop them onto Excel. You can see OPC Expert’s add-in immediately attempts to subscribe, and now we’re getting data from both OPC DA and UA servers. Data from the DA server — memory, ramp, and so on — comes in on one side, and data from the Kepware UA server comes in on the other. Once inside OPC Expert’s intelligent engine, it makes no difference whether it’s UA or DA.
Update Rates
Right now, all the data is coming in once a second — that’s 1,000 milliseconds. But I can make it go faster. Here we go — now it’s every 100 milliseconds. You can see some items updating faster and others still at one second. You can very easily tell some data to go faster and some to go slower. And as you can see, the CPU load in Excel is very low even though data is coming in quite fast.
Writing Data
To write data, I enter a value — say, 111 — and I want to send that to the OPC server. All I do is go to the formula, add a comma at the end, and reference the cell containing the value I want to write. Press Enter, and there we go — the value appears in OPC Expert. If I change it to 222, then 333, you can see the values updating. Very, very easy. I can both read data and write data, and I can change the update rates on the fly.
Working with Historical Data in Excel
Now let’s see how we can get historical data. We go to the archive in OPC Expert and over in Excel, there’s an OPC menu option. Before retrieving data, I can click Status to confirm the connection. Real-time data is functioning properly. Historical data is functioning properly. UDFs are functioning properly. OPC Expert is available, we’re getting a heartbeat, and I can see this is a 64-bit process. Everything is working correctly.
Now let’s get some history. I’ll take three items from the archive, drag them, and drop them into Excel. Here are the items, where they come from, and what the table will look like — item names across the top, timestamps down the side, and all the values. By default, we’re going to take one minute of data from one minute ago until the most recent point, which is now. I’ll click OK.
OPC Expert immediately grabs the data. Here’s the data, with my local timestamps — data from one minute ago all the way up to right now. I can take this data, insert a chart, and produce a trend. There’s our trend, and we’ve got historical data.
Important Note: Excel Is Not Storing This Data
Keep in mind: Excel was not storing this data. Even if I save the workbook, Excel stores the query itself — not the data. For example, instead of a hardcoded timestamp, I can use =NOW() as my end time and =NOW() - TIME(0,1,0) as my start time. Now this is dynamic. If I press F9 to refresh, you can see the timestamps update to the current time and the values update as well. The trend moves to reflect the latest data.
I’ll save the file to the desktop. Now watch this: I’ll close the file, then close OPC Expert entirely, and even exit the system tray. The only thing still running are the services. When I reopen Excel, and the workbook loads — it uses the time right now, pulls the query, and there is the fresh data. The query runs against the archive server automatically. Excel itself was not archiving anything. This all comes from OPC Expert running as a Windows service in the background.
Querying Local and Remote Historians
From Excel, you can query for any time range you want — the last shift from midnight to 8:00 a.m., the last week, averages, minimums, whatever you need. You can go to OPC Expert’s own archive server, or you can go to a third-party historian. For example, if you’ve got a remote PI system, InfoPlus.21, Honeywell PHD, an ABB historian from their DCS, Siemens, Yokogawa — whatever database you have — OPC Expert can get the data out of it using OPC HDA or OPC UA, whichever one your database supports.
So we’ve done real-time data reading and writing, and we’ve got historical data here with reports. If I press F9 to refresh, everything is updating in real time.
Demonstration Summary
That is the end of the demonstration. To recap: we captured real-time data and sent it to Excel. We created a historian and sent historical data to Excel. We created reports, and we were able to both read data and write data.
Products Mentioned
To get automated troubleshooting, you can download OPC Rescue for free, or you can get OPC Expert. With OPC Expert, you can do everything demonstrated here — trend, bridge, tunnel, and more. In this particular webinar, we spoke about Excel connectivity as well as OPC UA and OPC Classic connections for both real-time and historical data.
Training Courses
If you want to learn even more, come to one of our hands-on training courses where you can earn your Certified OPC Professional designation. In these workshops, you’ll learn how to configure DCOM and OPC UA, how to troubleshoot OPC systems, how to properly secure these systems, and learn OPC best practices.
During the class, you get two computers. You configure them yourself and troubleshoot them yourself. You’ll learn how to fix real-time communication problems related to user accounts, access control, services not starting, and more. These classes are quite interactive — live instructor, everything hands-on, and conducted remotely so there’s no travel required. Most of our public classes are remote. You join along with people from other companies.
If you want a class specifically for your company, we do private training as well — either remote or on-site, just for you. You can check dates at opcti.com.
Next Steps and Resources
We’re going to send you an email with several links: a link to this webinar recording, a link to download OPC Expert, and a link with examples and short setup videos showing how to get this done. You’ll also get an invitation to our next webinar on OPC and MQTT, coming up in about six weeks. If you have any questions, we do answer the phone, or you can visit our website at opcexpert.com.
This ends the formal part of our presentation on OPC to Excel. I hope to see you at our next webinar. If you’d like to stay a little longer, I will answer the questions I couldn’t get to during the webinar itself.
