I have a piece of code that dumps a bunch of data into a spreadsheet. Also rearranges it into a pivot table and then graphs the pivot table as well so my boss can get a clear view of the data.
This question is two part. One, I seem to be getting the title error at the starting line stating:
Expand|Select|Wrap|Line Numbers
- oSheet.Range(rng).Select
- With Selection
- shp.Left = .Left
- shp.Top = .Top
- shp.Width = .Width
- shp.Height = .Height
- End With
The other issue I'm having (and I am very novice I will admit) is that I don't know how to properly place the pivot chart I am creating at the end of the below code... what I would like to do is have it on a different sheet but I have tried a few things without results. I'm just very confused, feeling burnt out and I have a day off work here so I was wondering if maybe some of you can confuse me!
Expand|Select|Wrap|Line Numbers
- Public Sub CreateDAOChartM(strSourceName As String, _
- strChartLabel As String)
- 'On Error GoTo unexError
- Dim i As Integer
- Dim db As DAO.Database
- Dim rs As DAO.Recordset
- Set db = CurrentDb
- Set rs = db.OpenRecordset(strSourceName)
- Dim oApp As New Excel.Application
- Dim oBook As Excel.Workbook
- Dim oSheet As Excel.Worksheet
- Dim oSheetMain As Excel.Worksheet
- Dim FinalRow As Long
- Dim FinalCol As Long
- Dim oChartObj As Excel.Chart
- Dim oColPoint As Excel.Point
- Dim oSourceRange As Excel.Range
- Set oBook = oApp.Workbooks.Add
- 'This used to set the Worksheet to the first page of the sheet
- Set oSheet = oBook.Worksheets(1)
- 'OLD CREATION
- 'populate sheet 2 with data
- Dim iNumCols As Integer
- iNumCols = rs.Fields.Count
- For i = 1 To iNumCols
- oSheet.Cells(1, i).Value = rs.Fields(i - 1).Name
- Next
- 'Add Data to sheet
- oSheet.Range("A2").CopyFromRecordset rs
- 'format header as bold and autofit columns
- With oSheet.Range("a1").Resize(1, iNumCols)
- .Font.Bold = True
- .EntireColumn.AutoFit
- End With
- Dim pt As PivotTable
- Dim pf As PivotField
- 'oBook.PivotCaches.Create(xlDatabase, oSheet.UsedRange).CreatePivotTable TableDestination:="", TableName:="PivotTable1"
- oBook.PivotCaches.Create(xlDatabase, oSheet.UsedRange).CreatePivotTable TableDestination:="", tableName:="PivotTable1"
- oSheet.PivotTableWizard TableDestination:=oSheet.Cells(1, 5), tableName:="Pivot1"
- Set pt = oSheet.PivotTables("Pivot1")
- Set pf = pt.PivotFields(strChartLabel)
- 'Going to have to figure out how to get copper out
- oSheet.PivotTables("Pivot1").AddFields ColumnFields:=Array("equipmentID", "sampleDate")
- With pf
- .Orientation = xlDataField
- .Function = xlAverage
- End With
- pt.PivotFields("equipmentID").Orientation = xlColumnField
- pt.PivotFields("sampleDate").Orientation = xlRowField
- Dim shp As Shape
- Set shp = oSheet.Shapes.AddChart(xlColumnClustered)
- shp.Chart.SetSourceData Source:=oSheet.PivotTables(1).TableRange2, _
- PlotBy:=xlColumns
- With shp.Chart.Axes(xlCategory)
- .TickLabels.Orientation = 80
- End With
- Dim rng As String
- rng = (Replace(("A" & Str(rs.RecordCount + 5) & ":V" & Str(rs.RecordCount + 32)), " ", ""))
- oSheet.Select
- oSheet.Activate
- oSheet.Range(rng).Select
- With Selection
- shp.Left = .Left
- shp.Top = .Top
- shp.Width = .Width
- shp.Height = .Height
- End With
- With shp.Chart.PivotLayout.PivotTable
- .PivotFields("equipmentID").Orientation = xlColumnField
- .PivotFields("sampleDate").Orientation = xlRowField
- .GetPivotData
- End With
- shp.Chart.ChartType = xlColumnClustered
- shp.Chart.Refresh
- oApp.Visible = True
- shp.Chart.ChartType = xlColumnClustered
- shp.Chart.Refresh
- 'Display
- 'On Error GoTo closeerror:
- oApp.Visible = True
- oApp.UserControl = True
- 'close out everything
- rs.Close
- db.Close
- Exit Sub
- unexError:
- MsgBox Str$(Err.Number)
- On Error GoTo closeerror:
- 'close out everything
- rs.Close
- db.Close
- closeerror:
- Exit Sub
- End Sub