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.
Before you get started: Your data should be organized in a tabular format, and not have any blank rows or columns. Ideally, you can use an Excel table like in our example above. Tables are a great PivotTable data source, because rows added to a table are automatically included in the PivotTable when you refresh the data, and any new columns will be included in the PivotTable Fields List. Tab in Excel makes you move to the next cell to your right. You can use indent which will have the same function as tab. You may also use spacebar to indent the first line, Alt+Enter to go to next line below. Create a PivotTable Select the cells you want to create a PivotTable from. Note: Your data shouldn't have any empty rows or columns. It must have only a single-row heading. Jun 21, 2018 It lets you do other things in Excel if your connection is slow (like mine is at the moment). Notice the status bar says it is running the background query. To refresh a query manually, go to the Data tab of the Ribbon and click the Refresh button. Another way to refresh is to right-click in the query table result set and choose Refresh.
Converting a list to a table makes it very easy to format your data and to keep the formatting consistent as you make changes to the data. You can convert a list to a table in three short steps.
This feature works the same in all modern versions of Microsoft Excel: 2010, 2013, and 2016.
- Select a cell within the list you wish to convert to a table.
- On the Insert tab, in the Tables group, click the Table command.
- In the Create Table dialog box, verify that Excel has correctly guessed the correct data range, check My table has headers if your table does have headers, and click OK.
How To Create A Cross Tab Table In Excel For Mac 2016 Autorecover Location
Want to master Microsoft Excel and take your work-from-home job prospects to the next level? Jump-start your career with our Premium A-to-Z Microsoft Excel Training Bundle from the new Gadget Hacks Shop and get lifetime access to more than 40 hours of Basic to Advanced instruction on functions, formula, tools, and more.