Need help on getting Excel on an Intranet...

stevesian · Apr 16th, 2002, 11:33 PM

I have created a spreadsheet that automatically reads in the contents of five text files.

The largest text file contains transaction information: employee, date, amount, etc.

I lookup information from the other four text files and add it to the transaction table, as adjacent columns.

From advice in this forum, I have made this table a 'dynamic named range', as it changes constantly.

The combined table is the source data for various pivot tables to summarize and present data.

The two biggest questions answered by this spreadsheet are:

- what are employees spending money on?
- what vendors are we buying from?

Currently, the spreadsheet is used by the 'big boss' who sees data for all employees.

If there are items of note during her analysis, she wants to e-mail the appropriate supervisor, who will then sign on to the company intranet and request/view/analyse transaction data for their employees only.

This aspect of it: presenting the data on the intranet is what I wish help with.

Firstly, a developer told me to consider querying the data from the original mainframe data tables, either through UI or API, and just present that data using HTML, in other words, ignore using the current Excel spreadsheet at all.

However, the 'big boss' wonders if we can put some version of the spreadsheet onto the intranet, with only a subset of the data.

If any one has any ideas, I appreciate any and all replies.

Thanks.

[ This Message was edited by: stevesian on 2002-04-16 22:34 ]

[ This Message was edited by: stevesian on 2002-04-16 22:34 ]

[ This Message was edited by: stevesian on 2002-04-16 22:38 ]

anno · Apr 17th, 2002, 12:44 AM

stevesian
have a look at what ivan maola's doing here -it may help with the html thing:
http://www.mrexcel.com/board/viewtop...711&forum=10&8

pirateman30 · Jul 12th, 2002, 05:39 AM

This can be done. I have set up an SQL database on a database server. Then, on the web server I will be building my pages on & my local PC, I install Microsoft Office Web Components. I then make a system DSN ODBC connection to the SQL Database on both my web server and my local PC. I then use either Frontpage or Visual Interdev to create an .asp page. Insert - Component - Office pivot table into the .asp page. Then right click in the pivot table and select property toolbox and make a data source connection to the ODBC you set up connecting the SQL database. Then click on the icon in the pivot table area on your .asp page that brings up your field list and drag and drop the fields you want in your pivot table to their correct positions. The only downside I have found to this method is that for the user to view the pivot tables dynamicly the user must either; have Microsoft Web Components installed on their PC or set their browser (this assumes you use Internet Explorer) to allow the access of data across domains ( this is an internet security setting in your tools area).

[ This Message was edited by: pirateman30 on 2002-07-12 04:40 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 16th, 2002, 11:33 PM	#1
stevesian New Member Join Date: Apr 2002 Posts: 9	I have created a spreadsheet that automatically reads in the contents of five text files. The largest text file contains transaction information: employee, date, amount, etc. I lookup information from the other four text files and add it to the transaction table, as adjacent columns. From advice in this forum, I have made this table a 'dynamic named range', as it changes constantly. The combined table is the source data for various pivot tables to summarize and present data. The two biggest questions answered by this spreadsheet are: - what are employees spending money on? - what vendors are we buying from? Currently, the spreadsheet is used by the 'big boss' who sees data for all employees. If there are items of note during her analysis, she wants to e-mail the appropriate supervisor, who will then sign on to the company intranet and request/view/analyse transaction data for their employees only. This aspect of it: presenting the data on the intranet is what I wish help with. Firstly, a developer told me to consider querying the data from the original mainframe data tables, either through UI or API, and just present that data using HTML, in other words, ignore using the current Excel spreadsheet at all. However, the 'big boss' wonders if we can put some version of the spreadsheet onto the intranet, with only a subset of the data. If any one has any ideas, I appreciate any and all replies. Thanks. [ This Message was edited by: stevesian on 2002-04-16 22:34 ] [ This Message was edited by: stevesian on 2002-04-16 22:34 ] [ This Message was edited by: stevesian on 2002-04-16 22:38 ]

Apr 17th, 2002, 12:44 AM	#2
anno Board Regular Join Date: Feb 2002 Posts: 202	stevesian have a look at what ivan maola's doing here -it may help with the html thing: http://www.mrexcel.com/board/viewtop...711&forum=10&8

Jul 12th, 2002, 05:39 AM	#3
pirateman30 New Member Join Date: Jul 2002 Location: David Probst Posts: 2	This can be done. I have set up an SQL database on a database server. Then, on the web server I will be building my pages on & my local PC, I install Microsoft Office Web Components. I then make a system DSN ODBC connection to the SQL Database on both my web server and my local PC. I then use either Frontpage or Visual Interdev to create an .asp page. Insert - Component - Office pivot table into the .asp page. Then right click in the pivot table and select property toolbox and make a data source connection to the ODBC you set up connecting the SQL database. Then click on the icon in the pivot table area on your .asp page that brings up your field list and drag and drop the fields you want in your pivot table to their correct positions. The only downside I have found to this method is that for the user to view the pivot tables dynamicly the user must either; have Microsoft Web Components installed on their PC or set their browser (this assumes you use Internet Explorer) to allow the access of data across domains ( this is an internet security setting in your tools area). [ This Message was edited by: pirateman30 on 2002-07-12 04:40 ]