Results 1 to 2 of 2

Thread: Microsoft jet engine is not able to locate the sheet Data$.

  1. #1

    Microsoft jet engine is not able to locate the sheet Data$.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi Friends,

    I am developing an Excel automation report which has the following functionality.

    I have a Excel workbook(Report_template.xls) which has 2 sheets "Data" and "Report". This template(Report_template.xls) will be uploaded onto a web system where users can do some selection and click export data in the web. This will populate some data in the "Data" sheet of the (Report_template.xls) workbook.

    Once the User clicks Export button in the web system, A pop -up comes giving the user options
    1. Open the Excel file
    2. Save the Excel file
    3. Cancel the Excel file
    ----------------------------------------------------

    Here comes the Coding Part. Now my requirement is to query the "data" sheet and then fetch the recordset and paste in the "Report" worksheet. This should be done when the user opens the (Report_template.xls) workbook.

    I have done the below coding in Excel vba and calling this procedure in workbook_open() and it works fine. (When the user saves the file and then opens the file)

    My problem comes here. When the user clicks "open the Excel file" directly without saving the file to the local system, I am getting the error
    "Microsoft jet engine could not find the object Data$".
    _________________________________________________________________________________

    Sub Query_Data()
    'On Error GoTo Err_handler:
    Dim oCn As ADODB.Connection
    Dim oRS As ADODB.Recordset
    Dim ConnString As String
    Dim SQL As String

    Dim qt As QueryTable

    ' connection string to connect to the excel data sheet
    If Val(Application.Version) >= 12 Then
    ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=2"""
    Else
    ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"""
    End If



    Set oCn = New ADODB.Connection
    oCn.ConnectionString = ConnString
    oCn.Open

    ' Query to pull data from the sheet "Data"
    SQL = "Select * from [Data$] "

    Set oRS = New ADODB.Recordset
    oRS.Source = SQL
    oRS.ActiveConnection = oCn
    oRS.Open ' this is where i get the error

    ' Data_Table is a sheet that holds the output of the recordset
    'Create a Query table to strore the output of the query
    Set qt = Worksheets("Data_Table").QueryTables.Add(Connection:=oRS, _
    Destination:=Worksheets("Data_Table").Range("A1"))

    qt.Refresh

    If oRS.State <> adStateClosed Then
    oRS.Close
    End If


    If Not oRS Is Nothing Then Set oRS = Nothing
    If Not oCn Is Nothing Then Set oCn = Nothing
    Exit Sub
    Err_handler:
    MsgBox "error occurred " & Err.Description & " " & Err.Number
    End Sub

    ____________________________________________________________________


    the same code works when the file is saved in local and then opened. Dont know why its showing error when we directly open the file from the web system.

    Please help me. I tried this many forums but no response. I have spent a whole day in doing R&D but no clue.

    thanks
    Sarathi

  2. #2
    Threads - GoSkills - Groupon

    Register for a FREE account, and/
    or Log in to avoid these ads!

    I know this may sound strange ,but i have seen file names cause this exact problem. Rename your file and make sure there are no _ or spaces. Also make sure it contains 8 characters or less. Will not hurt to try.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •