Excel to Access

darkhalf · May 16th, 2008, 02:58 PM

Hi,

I have a form that will browse to an excel file... but what I can't seem to figure out is how to get the data into access...

I've tried the following with no luck...

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test.xls;Extended Properties=Excel 8.0;HDR=YES;"

AND

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "DRIVER=Microsoft Excel Driver (*.xls);DBQ=Server.MapPath('text.xls')"

Could someone shed some light?

Mych · June 16th, 2008, 10:02 AM

Have a look at...

http://p2p.wrox.com/topic.asp?TOPIC_ID=25073

I came across it while google'ing. I liked the look of the site that I registered and am now just browsing some of the posts.

hope it helps

Mych

I have not failed... I've just found 10,000 way that don't work!

Old Pedant · June 16th, 2008, 02:32 PM

Look here:
http://www.carlprothman.net/Default....orMicrosoftJet
(scroll down to the section that shows how to open Excel).

Thus:

Code:

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
        & "Data Source=" & Server.MapPath("test.xls") & ";" _
        & "Extended Properties=""Excel 8.0;HDR=YES;"""

' do NOT forget the $ in the name of the sheet.  And the [...] *are* required
SQL = "SELECT * FROM [name of sheet$]" ' order by is optional, of course

Set RS = conn.Execute( SQL ) 
...

darkhalf · June 18th, 2008, 10:17 AM

Hi,

This is good, but in both examples the file needs to be on the server itself... I've copied an excel file, and I am able to read it there using this:

Set cnnExcel = Server.CreateObject("ADODB.Connection")
cnnExcel.Open "DBQ=" & Server.MapPath("xl_data.xls") & ";" & _
  "DRIVER={Microsoft Excel Driver (*.xls)};"
Set rstExcel = Server.CreateObject("ADODB.Recordset")
rstExcel.Open "SELECT * FROM [Sheet1$B4:C12];", cnnExcel, adOpenStatic, adLockPessimistic

But what I need, is that on the client side, through a form, they would specify an excel file, and I woudl ike to be able to read through THAT file...

Trying to create the connection hasn't been working for me...

cnnExcel.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
         "Data Source=c:\xl_data.xls;" & _
         "Extended Properties=Excel 8.0;"

darkhalf · June 18th, 2008, 10:28 AM

FYI

This is the error message that I get:

Microsoft JET Database Engine error '80040e37'

The Microsoft Jet database engine could not find the object 'Sheet1$B4:C12'. Make sure the object exists and that you spell its name and the path name correctly.

/asp101_1.asp, line 40

This is line 40:
rstExcel.Open "SELECT * FROM [Sheet1$B4:C12];", cnnExcel, adOpenStatic, adLockPessimistic

But it works fine if the excel file is on the server...

Old Pedant · June 18th, 2008, 01:36 PM

Ain't never going to happen.

Period.

NOTHING on the SERVER can EVER read any file (or other content) that is on the client. Period.

[Even if server and client are on same LAN, the server has to access the client via the LAN, *NOT* via HTTP.]

You would need to UPLOAD the Excel file to the server to make this work.

Why do you think browsers have all these incredible security features in place, if not to protect against just exactly things like this??? If the server *COULD* get to the Excel file on the client, it could also maliciously erase the file. Or erase lots of files. Or corrupt various operating system files.

Yes, servers and clients can "talk" more closely than what I have described, but ONLY if the clients INSTALL software that allows the closer connection. That is, either browser plugins or actual full applications. In general, what you are wanting to do should NOT be solved via a browser.

darkhalf · June 18th, 2008, 01:43 PM

Thank you!

So uploading it is!