Drew Wutka
DWUTKA at Marlow.com
Fri Mar 14 11:27:49 CDT 2008
LOL, tell me about it!
Drew
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Friday, March 14, 2008 11:19 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] OT: Just noticed an excel feature....
It's always nice when things work the way they should.
John W. Colby
Colby Consulting
www.ColbyConsulting.com
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka
Sent: Friday, March 14, 2008 11:55 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] OT: Just noticed an excel feature....
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.
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
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.