Get data from database via web

[Greg Lovern]

We have a spreadsheet used by our vendors; we put certain data in it,
then the vendors put other data in it. The data we put in it changes
frequently and we'd like to be able have the workbooks update
dynamically whenever any vendor uses one, rather than send new
versions to our vendors.

The data lives in our database, and I'm familiar with using ADO to get
data from databases. But vendors don't have access to that internal
database.

What would be the simplest way to make the data available on the web
and use it in Excel?


Thanks,

Greg

 

[SysAccountant]

Greg

From what I have read I can only think of one viable solution.

There is an Excel Add In - a template tracking wizard - which creates a MS
Access database base and allows the user to enter data via Excel,but loads
tha data into the Access database.

My propsed soltuion would involve 2 stages:

1. Create the spredashhet with the tracking database attached
2.replicate the attached database (Replication is an Access technology - you
will have to read up on this ,if you don't already know about it).

After the above framework is set up ,send the spreadsheet and the tracking
database to the user(s);collect it and merge the changes in to the main
database ,via replication.

Hope this helps.

Regards

SysAccountant

PS. in relation to web technology : prior to Access 2007 ,you had Data
Access Pages in MS Access (this is a Web page in Access).

Other alternatives are Sharpoint sites or ASP Pages.

 

[gimme_this_gimme_that]

There are so many different approaches that you can take that it's
unlikely you're going to find much in terms of useful tips with this
question. It's too generic.

We have to know what you can do in terms of making the data available.

Can you expose an XML web service with data from the database on the
web. If so what are you companies policies? Are they already doing
something with IIS or Apache?

If not, how can vendors update dynamically when by definition they
can't get dynamic data?

Can you set requirements, in terms of the latest version of Excel and
MSXML, that users must have on their machines so that you can come up
with a solution that works for everyone?

Can you assume everyone one is on board with using MS Exchange?

How do you plan to handle race conditions?

Why don't you build a web application for reading and inserting data?
Is that an option? Or are we to assume you don't have skills to do
that?

Have you investigated using google spreadsheets? I'm not being a
troll, this is something that google spreadsheets are suited for.

Solution: (? one that doesn't use a web application).

Have the spreadsheet fetch it's data from a web service. Have users
update their spreadsheets and, for updating, email an XML document to
your exchange server. Set it up so that when an email message is
attached the email message is parsed and the data is inserted into the
database.

This solution has the advantages:

1. Implementers only have to know VBA
2. No web service has to be created to recieve data

 

[Greg Lovern]

There are so many different approaches that you can take that it's
unlikely you're going to find much in terms of useful tips with this
question. It's too generic.

Thanks, I'll try to answer the questions.

I've only been at this company for a few days, so I don't know
everything.

We have to know what you can do in terms of making the data available.

Can you expose an XML web service with data from the database on the
web. If so what are you companies policies? Are they already doing
something with IIS or Apache?

We are already getting other data into the spreadsheets with a web
service. But when I suggested doing that to get this data, the other
programmers on the team seemed to feel that would be too big of a
project. Thus my question here to find out what would be the simplest
way.

I don't know details about the web services, company policies, etc.

If not, how can vendors update dynamically when by definition they
can't get dynamic data?

There is a separate mechanism for vendors to upload their data to us.
I haven't seen it.

Can you set requirements, in terms of the latest version of Excel and
MSXML, that users must have on their machines so that you can come up
with a solution that works for everyone?

Probably not, though Excel 97 (at least) is already required for the
VBA already in place.

Can you assume everyone one is on board with using MS Exchange?

I'm pretty sure we can't assume all vendors are on Exchange.

How do you plan to handle race conditions?

I'm not sure what you mean. I understand in general what a race
condition is, but how would it apply to this situation?

Why don't you build a web application for reading and inserting data?
Is that an option? Or are we to assume you don't have skills to do
that?

Recreating all the functionality in all of these workbooks in a new
web application would take more money and time than they want to
commit at this point.

Have you investigated using google spreadsheets? I'm not being a
troll, this is something that google spreadsheets are suited for.

There is a lot of VBA automation in these spreadsheets; last I heard,
google spreadsheets didn't have a macro language. Even if it did,
rewriting all that would probably take more money and time than they
want to commit at this point.

Solution: (? one that doesn't use a web application).

Have the spreadsheet fetch it's data from a web service. Have users
update their spreadsheets and, for updating, email an XML document to
your exchange server. Set it up so that when an email message is
attached the email message is parsed and the data is inserted into the
database.

We're not looking for a way for users to upload anything to us. That's
already working fine.

This solution has the advantages:

1. Implementers only have to know VBA
2. No web service has to be created to recieve data

Thanks,

Greg

 

[gimme_this_gimme_that]

Hi Greg.

We are already getting other data into the spreadsheets with a web
service. But when I suggested doing that to get this data, the other
programmers on the team seemed to feel that would be too big of a
project. Thus my question here to find out what would be the simplest
way.

Hum... The simplest way... What if instead of a web service you
published an XML file with all the data every day or every few hours.
That way you wouldn't have to build a web service and your Excel
Worksheet would be able to get the latest published set of data.

Optionally, if your vendors are OK with it, you can create a bat
script that ftp's to your site and downloads the XML file and stores
it in a spot that vendors can administrate themselves. The
configuration issues can be ugly, but that might make things easier on
your end for your administrators to handle, if say, they are on board
with setting up ftp but don't want you to store a file the corporate
web site.

Also, you can use geocites or dot-mac or whatever, you could save the
XML file anywhere - it doesn't have to be on your corporate site.

There is a separate mechanism for vendors to upload their data to us.
I haven't seen it.

Yeah, for advice you have to tell us what that mechanism is.

For a simple thing ... I wondered was whether *you* had Outlook to use
as the mechanism. Because if you do then you can write Outlook VBA
that will execute VBA code (parse XML attached in the file and insert
the data into a database and perhaps publish the updated version of
the xml file).

So long as you have access to that mailbox and access to shared drives
you can do lots of stuff.

Probably not, though Excel 97 (at least) is already required for the
VBA already in place.

An XML solution might require users install MSXML and possibly MSXSL
on XP SP1. These are straightforward downloads. You might hit the
lucky jackpot and get by on 97.

I'm pretty sure we can't assume all vendors are on Exchange.

Sorry, I wasn't thinking of the vendors, I was thinking of you and the
upload mechanism.

I'm not sure what you mean. I understand in general what a race
condition is, but how would it apply to this situation?

A race condition occurs when you publish, both Vender A and B get a
Workbook. then vendor A publishes, then vendor B publishes but
overwrites some of what vendor A saved.

Recreating all the functionality in all of these workbooks in a new
web application would take more money and time than they want to
commit at this point.

Saving data in a Worksheet to an XML file is easy to do. I mean, if
that's too time consuming then they really aren't interested in doing
the project.

There is a lot of VBA automation in these spreadsheets; last I heard,
google spreadsheets didn't have a macro language. Even if it did,
rewriting all that would probably take more money and time than they
want to commit at this point.
Understood.


We're not looking for a way for users to upload anything to us. That's
already working fine.

Oh.. Then storing the data in a file on your server may be all that
you need to know.

 

[Greg Lovern]

Okay, it turns out the simplest way is this:

Publish a web page with the data in an html table or tables, then use
a web query in Excel to get the data (QueryTable, WebSelectionType =
xlSpecifiedTables, etc).

That will allow us to update the data anytime, and vendors get the
updated data the next time they open the workbook.


Greg