Darryl Collins
Darryl.Collins at coles.com.au
Sat Mar 15 19:11:43 CDT 2008
You could consider doing a webquery using MS Query directly into excel as a querytable. That way when the data changed on the website you could requery and update immediately into Excel. Maybe worth a look.
cheers
Darryl.
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Jim Lawrence
Sent: Saturday, 15 March 2008 5:20 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] OT: Just noticed an excel feature....
Hi Drew:
Those lines of code are very interesting.
Jim
-----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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
This email and any attachments may contain privileged and confidential information and are intended for the named addressee only. If you have received this e-mail in error, please notify the sender and delete this e-mail immediately. Any confidentiality, privilege or copyright is not waived or lost because this e-mail has been sent to you in error. It is your responsibility to check this e-mail and any attachments for viruses. No warranty is made that this material is free from computer virus or any other defect or error. Any loss/damage incurred by using this material is not the sender's responsibility. The sender's entire liability will be limited to resupplying the material.