Good Day,
I am relatively new to the VBA coding arena so please bare with my ignorance.
I am trying to apply some code I found on the internet to take some data from an excel table and create the equivalence of a cross tab query in another excel worksheet within the same workbook. Essentially I am trying to calculate the average of a data value with in the table and to aggregate this information by end user and month. Here is the code I am using:
Sub ApplyCrossTab()
Dim Myconnection As ADODB.Connection
Dim Myrecordset As ADODB.Recordset
Dim Myworkbook As String
Dim strSQL As String
Dim i As Integer
Set Myconnection = New Connection
Set Myrecordset = New Recordset
'Identify the workbook you are referencing
Myworkbook = ActiveWorkbook.Sheets("Completions").Select
'Open connection to the workbook
Myconnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & Myworkbook & ";" & _
"Extended Properties=""Excel 12.0;HDR=YES;"";"
'& _
'"Persist Security Info=False"
'Build SQL Statement
strSQL = "TRANSFORM Avg(CycleTime) " & _
"SELECT cust_nm_top, Product " & _
"FROM Completions " & _
"GROUP BY cust_nm_top, Product " & _
"PIVOT Month"
'Load the Query into a Recordset
Debug.Print strSQL
Myrecordset.Open strSQL, Myconnection, adOpenStatic, adLockOptimistic, adCmdText
'Place the Recordset onto Sheet2
With ActiveWorkbook.Sheets("Sheet2").Range("A1")
.CopyFromRecordset Myrecordset
End With
'Place the Recordset onto Sheet2
With Sheets("Sheet2")
.Range("A2").CopyFromRecordset Myrecordset
'Add column heading names
For i = 1 To Myrecordset.Fields.Count
.Cells(1, i).Value = Myrecordset.Fields(i - 1).Name
Next i
End With
End Sub
When I attempt to run the code I receive the following error box
Run-rime error '-2147217865(80040e37)':
The Microsoft Access database engine could not find the object 'Completions'.
If I use the Provider=Microsoft.Jet.OLEDB.4.0, I also receive the same error.
Any help would be greatly appreciated.
I am relatively new to the VBA coding arena so please bare with my ignorance.
I am trying to apply some code I found on the internet to take some data from an excel table and create the equivalence of a cross tab query in another excel worksheet within the same workbook. Essentially I am trying to calculate the average of a data value with in the table and to aggregate this information by end user and month. Here is the code I am using:
Sub ApplyCrossTab()
Dim Myconnection As ADODB.Connection
Dim Myrecordset As ADODB.Recordset
Dim Myworkbook As String
Dim strSQL As String
Dim i As Integer
Set Myconnection = New Connection
Set Myrecordset = New Recordset
'Identify the workbook you are referencing
Myworkbook = ActiveWorkbook.Sheets("Completions").Select
'Open connection to the workbook
Myconnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & Myworkbook & ";" & _
"Extended Properties=""Excel 12.0;HDR=YES;"";"
'& _
'"Persist Security Info=False"
'Build SQL Statement
strSQL = "TRANSFORM Avg(CycleTime) " & _
"SELECT cust_nm_top, Product " & _
"FROM Completions " & _
"GROUP BY cust_nm_top, Product " & _
"PIVOT Month"
'Load the Query into a Recordset
Debug.Print strSQL
Myrecordset.Open strSQL, Myconnection, adOpenStatic, adLockOptimistic, adCmdText
'Place the Recordset onto Sheet2
With ActiveWorkbook.Sheets("Sheet2").Range("A1")
.CopyFromRecordset Myrecordset
End With
'Place the Recordset onto Sheet2
With Sheets("Sheet2")
.Range("A2").CopyFromRecordset Myrecordset
'Add column heading names
For i = 1 To Myrecordset.Fields.Count
.Cells(1, i).Value = Myrecordset.Fields(i - 1).Name
Next i
End With
End Sub
When I attempt to run the code I receive the following error box
Run-rime error '-2147217865(80040e37)':
The Microsoft Access database engine could not find the object 'Completions'.
If I use the Provider=Microsoft.Jet.OLEDB.4.0, I also receive the same error.
Any help would be greatly appreciated.
Last edited:
