• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • Last Modified:

ASP, Excel and MsAccess

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
Patricia080698
Asked:
Patricia080698
  • 3
  • 3
  • 3
  • +1
1 Solution
 
sybeCommented:
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
 
Patricia080698Author Commented:
How can I display my Excel datasheet directly through asp? Is there a place where I can get some info about this?
0
 
MasseyMCommented:
you will need to first set up a DSN in your ODBC with a type of Excel rather than Access..

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
sybeCommented:
I have seen it once in a magazine. Anyway, there is a ODBC-driver for Excel.
0
 
MasseyMCommented:
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
 
Patricia080698Author Commented:
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
 
sybeCommented:
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
 
MasseyMCommented:
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
 
Patricia080698Author Commented:
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
 
mouattsCommented:
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

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 3
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now