Solved

ASP, Excel and MsAccess

Posted on 1998-09-07
10
247 Views
Last Modified: 2013-12-25
I am trying to display an Excel worksheet onto my html page. I want to use MsAccess 95 to import the excel data, but before importing I also need to do some calculations in my excel colums(adding, substracting, etc.) I know that I can do this with macros (but should I do it in MsAccess or Excel?)

I am Using ASP, the list of items(what my excel datasheet contains) in excel is modified every month and sent to me as a .xls file. I need to know if there is an automatic way(maybe through ASP) to import this .xls file into my MSAccess, then do the calculations and afterwards display the hole table of items on my html page...Thanks

0
Comment
Question by:Patricia080698
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 28

Expert Comment

by:sybe
ID: 1828554
With ASP it is possible to retrieve data directly from Excel and display it browsers, so you don't need the way through Access.

By the way, did you solve to "Order" problem (Q.10077588), there are some suggestions waiting for you

0
 
LVL 1

Author Comment

by:Patricia080698
ID: 1828555
How can I display my Excel datasheet directly through asp? Is there a place where I can get some info about this?
0
 
LVL 10

Expert Comment

by:MasseyM
ID: 1828556
you will need to first set up a DSN in your ODBC with a type of Excel rather than Access..

0
 
LVL 28

Expert Comment

by:sybe
ID: 1828557
I have seen it once in a magazine. Anyway, there is a ODBC-driver for Excel.
0
 
LVL 10

Expert Comment

by:MasseyM
ID: 1828558
The driver is installed when you install Excel (97) on the computer.

There was an article in Avatar magazine once about creating a link to Excel, but It talked more along the lines of using the charting feature.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 1

Author Comment

by:Patricia080698
ID: 1828559
I know that I can export an excel datasheet to Access but I need to do some calculations to the price list before exporting it.
0
 
LVL 28

Expert Comment

by:sybe
ID: 1828560
So do you need to change the spreadsheet before publishing it ?

Is it for an intranet ? and do you know what browsers the visitors will use.

You could also make a <a href="  "> to the spreadsheet. IE will open the spreadsheet and display it within the browser.
If you make the spreadsheet read-only, then no one will be able to change it.

I'll be chcking today how to make a ODBC connection to a spreadsheet.
0
 
LVL 10

Expert Comment

by:MasseyM
ID: 1828561
You can make the calculation using ASP... Why do you need to do it in the spreadsheet?  I would think you could get the data from Access and make the calculation then place them on the page.  
0
 
LVL 1

Author Comment

by:Patricia080698
ID: 1828562
I was wondering if there was a way to directly print out my price list (which is in excel) on my html page. Through ASP.
0
 
LVL 11

Accepted Solution

by:
mouatts earned 50 total points
ID: 1828563
As has already been stated you can access the spreadsheet via ODBC. Within the ASP page you open a connection to your spreadsheet. EG
<%Set Connection = Server.CreateObject("ADODB.Connection")
Connection.Open "DBQ=" & LocalPath & "Database\prices.xls;Driver={Microsoft Excel Driver (*.xls)};" %>

You then issue the SQL command which will be along the lines of
SELECT price FROM PRICE_LIST
using the execute method
This returns a record set which you can cycle through to read each item using the Move method.
Finaly you close the connection.

Steve
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The following is a collection of cases for strange behaviour when using advanced techniques in DOS batch files. You should have some basic experience in batch "programming", as I'm assuming some knowledge and not further explain the basics. For some…
In this tutorial I will aim to show you how simple is making a small application in WhizBase, how to add, remove and update data in the DB. I will make a small address book application where you can add, browse, update and remove addresses. I wi…
Learn the basics of lists in Python. Lists, as their name suggests, are a means for ordering and storing values. : Lists are declared using brackets; for example: t = [1, 2, 3]: Lists may contain a mix of data types; for example: t = ['string', 1, T…
Learn the basics of while and for loops in Python.  while loops are used for testing while, or until, a condition is met: The structure of a while loop is as follows:     while <condition>:         do something         repeate: The break statement m…

939 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

2 Experts available now in Live!

Get 1:1 Help Now