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$".
'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"""
ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"""
Set oCn = New ADODB.Connection
oCn.ConnectionString = ConnString
' 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, _
If oRS.State <> adStateClosed Then
If Not oRS Is Nothing Then Set oRS = Nothing
If Not oCn Is Nothing Then Set oCn = Nothing
MsgBox "error occurred " & Err.Description & " " & Err.Number
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.