Drew Wutka
DWUTKA at Marlow.com
Fri Mar 14 10:54:58 CDT 2008
I just love it when I discover little 'features' like these.
Here's the story:
My company is a manufacturing company. We have about 15 to 20 'test
machines', where some sort of testing device is attached to a computer,
and the computer runs the test, and records the results in a database.
About 6 years ago, we got a 'CMM' test machine. It's a cool little
machine, a camera mounted on a moving platform, that can be programmed
to measure parts (height width and length...pretty slick...). Anyhow,
the guy that was going to 'program' that machine, wanted to store the
data in a database. I whipped up a database and a .dll he could use in
that softwares scripting language to handle the three types of 'runs' he
was going to perform. Worked great. About 4 years ago, we got another
machine of the same type to be used at a different step in our
manufacturing process. This machine was going to have a lot more then 3
types of runs though. So I decided, back then, to build a 'data
acquisition' system. With the exception of that first CMM, all of our
test machines dumped their data into databases developed by a
engineering software guy, who's concepts of how to create a database
were as far away from relational that you could get.
So, what I built, was a system where the 'developer' could create a
'program'. A program was a set of fields. Each field had a data type,
min max limits, etc. Then the developer could use the DataAcquisition
.dll to record the data into a relational database. A 'univeral'
database, designed so that all of that data could be put in the same
database, and easily searched.
This system had another design consideration. Our test machines were
currently recording their data into a local .mdb. And we had setup some
processes to copy those databases to a network drive on a daily basis.
The problem with that, is that as those databases got larger, the copy
process took longer, and we still had 15 to 20 separate databases being
stored on our network. The system I designed was intended to run on the
local machine, and then push only the new data (not the whole database)
to a server database. On top of that, each test machine had a 'website'
setup, so that you could query the local database on that test machine,
without having to open the .mdb across the network.
This is where I stumbled across a neat trick with excel yesterday/today.
That Data Acquisition project was completed to about 80% 4 years ago.
That new CMM test machine, records it's data into it, but the 'custom'
reporting engine I planned for it, and the server side database push was
never completed. And, that is the only machine using the system,
instead of all machines, which was the end goal. Well, now it's getting
pushing back into the limelight, so it's on my list of must do projects.
When initially designing this system, I always had in the back of my
mind, that I wanted to create an Excel Add-on to work with the system.
Our engineers LOVE excel, and constantly want their data in it. So I
envisioned created an Add-on that let them select the test machine of
their choice, then the 'program' and other criteria that would then
populate their spreadsheet. The quirk is, I already have a webpage that
grabs the data. I didn't want excel to hit that .mdb over the network,
I wanted it to use the webserver on the test machines (or a network
'intranet' to hit the server side database). So when I was mulling this
issue over yesterday, I thought to myself:
Self: The web pages being produced are just HTML tables.
Self: Excel is just a big table.
Self: You can copy and paste an HTML table into excel.
Self: I wonder if Excel could just 'open' the HTML page.
And it does.
Workbooks.Open Filename:= _
"http://fecmm/batchreport.asp?BatchID=3569", ReadOnly:=True
That line opens the HTML table right into excel. It even sets the
column widths the same. Pretty cool, I think. All I'll have to do with
that Addin, is open a workbook with the right URL, and Excel does all
the work populating it's workbook! Woohoo!
Drew
The information contained in this transmission is intended only for the person or entity to which it is addressed and may contain II-VI Proprietary and/or II-VI Business Sensitive material. If you are not the intended recipient, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited.