0

I am trying to open an Excel sheet using ADODB connection, but I am getting the following error when I try to execute the query although the name of the worksheet is correct:

Error number: 3704

Error Description:Operation is not allowed when the object is closed

I have gone through most of the answers provided online and I still can find my answer as I already have what was missing in the answers I read.

Here is my code:

Dim objConn

Set objConn = Server.CreateObject("ADODB.Connection")

Dim ext : ext = GetFileExt(filename)

If ext = "xlsx" Then
  With objConn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = _
      "Data Source=" & Server.MapPath("../import/" & filename) & ";" & _
      "Extended Properties=Excel 12.0;"
    .CursorLocation = adUseClient
    .Open
  End With
End If

Dim sql
sql = "SELECT * FROM [" & worksheet & "$]"
Dim objRs
Set objRs = objConn.Execute(sql)
response.write "err.number: " & Err.Number            '3704
response.write "err.Description: " & Err.Description  'Operation is not allowed when the object is closed
Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
Alladin
  • 1,010
  • 3
  • 27
  • 45
  • At which line are you getting the error? Are you sure your connection is actually open for it to give you a recordset? – Pankaj Jaju Aug 07 '18 at 10:13
  • @PankajJaju I have edited my question and showed at the end how i exactly get the error.. how can i check that my connection is actually open in this case, I am actually a bit of a newbie in vbscript. – Alladin Aug 07 '18 at 10:19
  • Have you set the value in `worksheet` anywhere? Check out [this](https://stackoverflow.com/questions/17611545/check-if-adodb-connection-is-open) to check if your connection is ok or not – Pankaj Jaju Aug 07 '18 at 10:28
  • @PankajJaju I have just checked the state of the connection and it returned the value 0. What could be the reason behind my connection not opened? and the worksheet variable's value is assigned previously to Sheet1. – Alladin Aug 07 '18 at 10:45
  • Maybe the filepath is incorrect, issues with access rights - can be anything. Also, I get an error if I try to set the cursorlocation - maybe comment that out and see? – Pankaj Jaju Aug 07 '18 at 10:49
  • @PankajJaju I have double checked the path and it's correct. also I have commented the cursorlocation line; yet, i still have the same issue. I gave full control when it comes to access rights. I have no clue on what could be the cause behind this. Maybe you might have any other suggestions that you think might be the cause behind the issue – Alladin Aug 07 '18 at 10:55

0 Answers0