Eric Barro
ebarro at verizon.net
Fri Mar 14 13:11:20 CDT 2008
Or you could whip up a web service that you can call using VBA in Excel to
populate the spreadsheet template. The web service encapsulates your calls
to the database via the web server.
I work for a manufacturing company too and the users love Excel to death
because they can manipulate data using the app. I created a web service that
can be referenced in Excel and using VBA, loop through the dataset pumped
out as an XML stream by the web service. User specifies a part number,
clicks on a button that triggers a call to one of the web service's exposed
methods and they get their data. If an extra column is needed or removed,
all that's needed is to add/remove the column to/from the stored procedure.
There is no need to recompile the web service DLL.
As an aside...
One of the interesting features that Excel has that works really well with
Sharepoint is the ability to "publish a list" (basically a range of data) to
Sharepoint which allows the users to update either the XLS file or the
Sharepoint published list and have two-way updates. :)
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka
Sent: Friday, March 14, 2008 8: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