Excel - Web interface?

[Andy D.]

I have a project I need to get done quickly, and any advice would be most appreciated.

To cut to the short of it: I need to write an app that will take Purchase Orders, tidy them up and dump them into Excel. The front-end needs to be accessible via an intranet to non-tech types.

I'm thinking I can use VB for this and create some form of web front end (I presume VB.Net) that communicates with Excel and inserts the data.

I'd normally just store all the data in a DB and spit out the Excel/CSV doc, but the XLS file has a bunch of work done it already to manipulate the data, so I need to integrate with this.

Any thoughts on the best approach? I've only ever used Excel as a basic spreadsheet and never done any coding in it so I'm new to this.

Thanks

 

[Tsotne]

Are you gonna use desktop DB (MS Access) or SQL?

 

[Andy Nguyen]

Is this app only accessible internally? If it is to take order from the public, I would use some of several enterprise forms solution like Wufoo that provides a backend reporting that can be configured and download as excel sheets.

 

[Andy D.]

Tsotne - We use MySQL pretty much exclusively at my place. I'll use a DB if necessary in order to pass the data into Excel if that is a recommended route?

Andy - It will be accessed internally only, by around 12 people so it is a pretty small app really.

Thanks guys.

 

[Tsotne]

Yes why not?! If you have a data to be modified before imported into excel, store your data modification logic into SQL procedure and import the modified data into excel using the VBA commands.

 

[alain]

Which languages do you know? This sounds like a simple CRUD application for the web. Ruby on Rails is famous for easy of use. If you don't know Ruby (I don't), I would use Python and the Django framework. You will be up and running in no time also. Dumping to XLS could be as simple as dumping a CSV unless you give more details.

 

[Andy D.]

Which languages do you know? This sounds like a simple CRUD application for the web. Ruby on Rails is famous for easy of use. If you don't know Ruby (I don't), I would use Python and the Django framework. You will be up and running in no time also. Dumping to XLS could be as simple as dumping a CSV unless you give more details.

Alain - I primarily work in PHP at the moment, mostly LAMP environments. I'll have a look into Python today them, that sounds interesting.
The content coming via the web front-end will basically needs be added incrementally to an XLS. The XLS is then setup to do financial functions for the accounting department.

Thanks

 

[alain]

The content coming via the web front-end will basically needs be added incrementally to an XLS. The XLS is then setup to do financial functions for the accounting department.

This sounds like a bad idea all together. You are using Excel as your storage... bad bad bad.

 

[Andy D.]

This sounds like a bad idea all together. You are using Excel as your storage... bad bad bad.

I totally agree that using Excel as storage is bad, my preferred method would be to setup an Apache environment, pump the data into MySQL via a PHP front-end and then dump the data and have it imported to Excel.

My concern with the above method is getting the data from the DB into Excel without screwing anything up, and in a way so that the user doesn't have to perform any steps and preferably the data is added incrementally (or at least appears that way to the user).

That's why I'm a bit stumped on this, it's the portion of getting the data from the DB into Excel in such a manner as to achieve the above and which technology is best to achieve this.

Thanks

 

[Andy Nguyen]

Input gets stored in database
Create an Excel spreadsheet and use MS Queries to connect to your database.
Create macros and put some buttons linked to those macro so your users can click on it and get data populated on the sheets.

 

[iHateVariance]

not big expert on this. but i use ADO to get data then manipulate it at VBA level and dump on spreadsheet. if u google for ADO u get tons of ref to get u started

 

[Andy D.]

Everyone,

Thanks for the feedback here. I've decided to go down the route of a C# asp.net web interface, that will store the data in a MSSQL DB. I'm then going to hook up the Excel spreadsheet to the DB so it pulls the data from it using some macros.