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

Sarathi_57

Banned
Joined
May 2, 2012
Messages
3
Reaction score
0
Points
0
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​
 
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.
 
Back
Top