Can't utlize Excel Driver on Windows Server 2003
I want to access an Excel workbook on a network share and display it's data in an ASP.NET datagrid.
I set up a DSN using the Microsoft Excel Driver(*.xls) and got this working on my dev machine. It was my understanding that this driver would allow an app to read the spreadsheet without having Excel actually installed on the server. However I get the following error on the server. I've compared the dev DSN to the server and don't see any diffs. I can drill down to the location of the Excel file on the server so the network share is properly mapped on it.
ERROR [HY024] [Microsoft][ODBC Excel Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.
ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
ERROR [HY024] [Microsoft][ODBC Excel Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides..
Please help.
I set up a DSN using the Microsoft Excel Driver(*.xls) and got this working on my dev machine. It was my understanding that this driver would allow an app to read the spreadsheet without having Excel actually installed on the server. However I get the following error on the server. I've compared the dev DSN to the server and don't see any diffs. I can drill down to the location of the Excel file on the server so the network share is properly mapped on it.
ERROR [HY024] [Microsoft][ODBC Excel Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.
ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
ERROR [HY024] [Microsoft][ODBC Excel Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides..
Please help.
Are you setting this a a User DSN or System DSN?
ASKER
It is a system DSN.
You may also want to set itup as a user DSN since ASP uses the ASP.NET account for credentials.
ASKER
hopefully we're getting closer here:
Ok here's what I know:
1) I can sign on to the server as an admin and create DSNs either system or user
Here's what I do NOT know:
1) The user account that ASP.NET uses
2) How to specify any kind of user information in the setup for a DSN using an Excel Driver. This setup only cares about
the location of the file.
How do setup a user DSN to make this work.
Ok here's what I know:
1) I can sign on to the server as an admin and create DSNs either system or user
Here's what I do NOT know:
1) The user account that ASP.NET uses
2) How to specify any kind of user information in the setup for a DSN using an Excel Driver. This setup only cares about
the location of the file.
How do setup a user DSN to make this work.
ASKER
I created a user DSN and deleted the System one. Now the app says it can't find that DSN name. The spelling is correct.
I see that the ASPNET account is a member of Administrators and Users.
I see that the ASPNET account is a member of Administrators and Users.
ASKER
Here's what I ended up using. The only stipulation is that the Excel file had to be on the server. I could not get this to work through the network share. Obviously an issue with permission which I'll look into. But the good news is this works without a DSN and without having Office installed on the server:
Imports System.Data
Dim ds As New DataSet()
Dim cn As New Data.OleDb.OleDbConnection
"Data Source=" & myfile & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""")
cn.Open()
Dim sql As String = "SELECT * from [Sheet1$];"
Dim da As New Data.OleDb.OleDbDataAdapte
da = New Data.OleDb.OleDbDataAdapte
da.Fill(ds)
Me.dgrid.DataSource = ds
Me.dgrid.DataBind()
Me.dgrid.Visible = True
cn.Close()
Imports System.Data
Dim ds As New DataSet()
Dim cn As New Data.OleDb.OleDbConnection
"Data Source=" & myfile & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""")
cn.Open()
Dim sql As String = "SELECT * from [Sheet1$];"
Dim da As New Data.OleDb.OleDbDataAdapte
da = New Data.OleDb.OleDbDataAdapte
da.Fill(ds)
Me.dgrid.DataSource = ds
Me.dgrid.DataBind()
Me.dgrid.Visible = True
cn.Close()
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ERROR [HY000] [Microsoft][ODBC Excel Driver] The Microsoft Jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view its data.
ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
ERROR [HY000] [Microsoft][ODBC Excel Driver] The Microsoft Jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view its data..
The driver is setup for readonly. Do file permissions need to be set somewhere?