Problem connecting to Excel spreadsheet

Is the filename  test.asp or test.xls??

The name of the excel spreadsheet I am trying to connect to is test.xls

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?

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.

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.

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.

I'm sorry I must be really dim but I don't understand what you mean

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.

I thought it was probably a permissions problem.  I'm glad your problem is resolved.