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

Problem connecting to Excel spreadsheet

I am trying  to connect to an Excel spreadsheet called test.asp uploaded to the private folder of my website (hosted on a shared host). I am using the following code for this:

strconn = "DRIVER=Microsoft Excel Driver (*.xls);UID=admin;UserCommitSync=Yes;Threads=3;SafeTransactions=0;ReadOnly=1;PageTimeout=5;MaxScanRows=8;MaxBufferSize=2048;FIL=excel 8.0;DriverId=790;DefaultDir=" + Server.MapPath("..\..\private") + ";DBQ=" + Server.MapPath("..\..\private\test.xls") + ";"

but receive the following error message:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Excel Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x16e4 Thread 0x1070 DBC 0x51a4338c Excel'.

Can anyone help?
0
e_mam
Asked:
e_mam
  • 5
  • 4
1 Solution
 
sah18Commented:
Is the filename  test.asp or test.xls??
0
 
e_mamAuthor Commented:
The name of the excel spreadsheet I am trying to connect to is test.xls
0
 
sah18Commented:
I think I need a little more context.  What are you trying to connect to it from -- possibly an active server page?  If so, could you give a little more code for how you are connecting, and what you are trying to do with the data itself.  Are you just trying to display the entire spreadsheet from a web page, or are you trying to retrieve the data from the spreadsheet and do something with it?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
e_mamAuthor Commented:
OK. The idea is to retrieve quite extensive financial data from a spreadsheet uploaded by various subsidiary companies (that they fill in using a standard template and their own accounting system). The spreadsheet is then uploaded to a website, where data needs to be validated before it is entered into a database. The idea was to open a connection to the excel spreadsheet, create a recordset, post the data using a form to the database whilst doing some server-side validation. I have managed to get the data to publish on my page (for testing purposes) on my local server here using the following code:

----------------------------
Const sadOpenStatic = 3
Const sadLockPessimistic = 2
Dim cnnExcel
Dim rstExcel
Dim iCols, strConn

Set cnnExcel = Server.CreateObject("ADODB.Connection")
With cnnExcel
      .Provider = "MSDASQL"
      .ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
"DBQ=" & Server.MapPath("test.xls") & "; ReadOnly=False;"
      .Open
end with
Set rstExcel = Server.CreateObject("ADODB.Recordset")
rstExcel.Open "SELECT * FROM Data;", cnnExcel, _
      sadOpenStatic, sadLockPessimistic

iCols = rstExcel.Fields.Count
%>
<table border="1">
      <thead>
            <%
            For I = 0 To iCols - 1
                  Response.Write "<th>"
                  Response.Write rstExcel.Fields.Item(I).Name
                  Response.Write "</th>" & vbCrLf
            Next 'I
            %>
      </thead>
      <%
      rstExcel.MoveFirst
      Do While Not rstExcel.EOF
            Response.Write "<tr>" & vbCrLf
            For I = 0 To iCols - 1
                  Response.Write "<td>"
                  Response.Write rstExcel.Fields.Item(I).Value
                  Response.Write "</td>" & vbCrLf
            Next 'I
            Response.Write "</tr>" & vbCrLf

            rstExcel.MoveNext
      Loop
      %>
</table>
<%
rstExcel.Close
Set rstExcel = Nothing
cnnExcel.Close
Set cnnExcel = Nothing
%>
----------------------------------------------------

and that works fine locally but doesn't work on the host that we will be using for this website (host who doesn't offer any help with coding and has been chosen by our client).

Thanks for any help you can offer.
0
 
sah18Commented:
It may be an issue of the web server app not have sufficient privileges to connect to a file on a mapped server drive.  Would it be possible for you to first upload the xls file to a folder on your web server, and then do the processing directly on that local copy?  This may solve your problem, since you would no longer be having to deal with access privileges from within your script.
0
 
sah18Commented:
ps:  I'd suggest trying what I suggested above first manually (just copy the file over somewhere and see if your script works, with redirecting it to the new file location).  If it works manually, then you could schedule the file to be copied over every so many hours (or at a specific time each day), etc.
0
 
e_mamAuthor Commented:
I'm sorry I must be really dim but I don't understand what you mean
0
 
e_mamAuthor Commented:
the issue was resolved by my hosting company. They had not setup the permissions properly on that specific folder and that's why I was experiencing this problem, thanks for your help.
0
 
sah18Commented:
I thought it was probably a permissions problem.  I'm glad your problem is resolved.
0
 
Computer101Commented:
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now