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 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