title: VBA Access Format Chart in Excel
body: <p><strong>EDITED</strong></p>
  
  <p>If you guys have a link that might help with this problem I'd really like to read it, because so far I haven't seen anything very useful.</p>
  
  <p>In access I'm trying to export arbitrary data to excel, create multiple charts (right now just working on a pie chart), format these charts and then send them to a blank (Chart) sheet.  So far I've exported the data and am able to create the charts, I just have no idea how to format them.</p>
  
  <p>The formatting I want to do is to get rid of the legend, put data labels with the name, value, and percentage, and move it to a "Chart" sheet.</p>
  
  <p><strong>Edit I am now able to get rid of the legend as well as insert the data labels with name, value and percentage.  I am still stuck on moving the Chart object to a new sheet, code at bottom.</strong></p>
  
  <p>I've also tried to record a macro in excel, edit it slightly and then move it over to access but I keep erroring out, usually with an error similar to "This object doesn't have that method".  Below I'll include a test table that I might get and how I create the pie chart.</p>
  
  <p>Code:</p>
  
  <pre><code>Function Excel_Export_Two_Column()
  Dim db As DAO.Database, rs As DAO.Recordset
  Dim WBO As Object, WSO As Object, WSO2 As Object, XLO As Object, oChart As Object
  Dim x As Long, y As Long, z As Integer, strTab As String, strcompany As String
  Dim endTable As Long
  Dim tempName As String, tempNum1 As Long, tempNum2 As Long, totalEnd As Long
  
  z = 1
  Set db = CurrentDb()
  Set rs = db.OpenRecordset("QRY2Col")
  
  Set XLO = CreateObject("Excel.Application")
  XLO.Application.Workbooks.Add
  
  Set WBO = XLO.Application.ActiveWorkbook
  Set WSO = WBO.Worksheets(1)
  Set WSO2 = WBO.Worksheets(2)
  
  WSO.Name = Left("export", 31)
  
  For y = 0 To rs.Fields.Count - 1
      WSO.Cells(1, 1) = "Num"
      WSO.Cells(1, y + 2) = rs(y).Name
  Next y
  
  x = 1
  Do While Not rs.EOF()
      x = x + 1
      WSO.Cells(x, 1) = x - 1
      For y = 0 To rs.Fields.Count - 1
          WSO.Cells(x, y + 2) = Trim(rs(y))
      Next y
  
      rs.MoveNext
      DoEvents
  Loop
  
  WSO.Cells.Rows(1).AutoFilter
  WSO.Application.Cells.Select
  WSO.Cells.EntireColumn.AutoFit
  
  x = 1
  Do While WSO.Cells(x, 1) &lt;&gt; ""
      x = x + 1
  Loop
  
  endTable = x - 1
  
  WSO2.Cells(1, 1) = "Name"
  WSO2.Cells(1, 2) = "Num"
  totalEnd = 2
  For x = 2 To endTable
      If (WSO.Cells(x, 2) &lt;&gt; "") Then
          tempName = WSO.Cells(x, 2)
          tempNum1 = WSO.Cells(x, 3)
  
          For y = 2 To totalEnd
              If (WSO2.Cells(y, 1) = tempName) Then
                  tempNum2 = WSO2.Cells(y, 2)
                  WSO2.Cells(y, 2) = tempNum1 + tempNum2
                  Exit For
              ElseIf (y = totalEnd) Then
                  WSO2.Cells(y, 1) = tempName
                  WSO2.Cells(y, 2) = tempNum1
                  totalEnd = totalEnd + 1
              End If
          Next y
      End If
  Next x
  
  Set oChart = WSO2.ChartObjects.Add(500, 100, 500, 300).Chart
  oChart.SetSourceData Source:=WSO2.Range("A1").Resize(totalEnd - 1, 2)
  oChart.ChartType = 5
  
  strcompany = "Export"
  If Dir(CurrentProject.Path &amp; "\COLA_AR_" &amp; Format(Date, "yyyymm") &amp; "_XXX_" &amp; strcompany &amp; ".xlsx") &lt;&gt; "" Then
      Kill CurrentProject.Path &amp; "\COLA_AR_" &amp; Format(Date, "yyyymm") &amp; "_XXX_" &amp; strcompany &amp; ".xlsx"
  End If
  
  Call WBO.SaveAs(CurrentProject.Path &amp; "\COLA_AR_" &amp; Format(Date, "yyyymm") &amp; "_test_2_Col.xlsx")
  
  
  WBO.Close savechanges:=True
  Set WBO = Nothing
  
  XLO.Application.Quit
  Set XLO = Nothing
  
  rs.Close
  db.Close
  End Function
  </code></pre>
  
  <p>Table: Note that this table is in a Query (named "QRY2Col") in Access</p>
  
  <pre><code>Field1          Field2
  CTOD            64646515
  BFTBC2          6656532
  WTOW            451512355
  DT3             684321818
  STC2            652553548
  BFTBC2          12
  DT3             84954987
  ATCR            99999999
  CTOD            64185435
  BFTBC2          321569846
  STC2            6543518
  STC2            3518684
  ATCR            35481354
  </code></pre>
  
  <p><strong>Code for data labels</strong></p>
  
  <pre><code>Set oChart = WSO2.ChartObjects.Add(500, 100, 500, 300).Chart
  oChart.SetSourceData Source:=WSO2.Range("A1").Resize(totalEnd - 1, 2)
  ' Number corresponds to a pie chart
  oChart.ChartType = 5
  
  ' Adds data Labels
  oChart.SeriesCollection(1).HasDataLabels = True
  
  ' Format chart
  oChart.SeriesCollection(1).DataLabels.ShowCategoryName = True
  oChart.SeriesCollection(1).DataLabels.ShowPercentage = True
  oChart.SeriesCollection(1).HasLeaderLines = True
  oChart.Legend.Delete
  </code></pre>
  
  <p><strong>Attempted code to move chart</strong></p>
  
  <p>Below is an example of what I recorded (edited by adding "oChart") but this still doesn't work.  The problem that gets highlighted is the "xlLocationAsNewSheet" and VBA says that the "Variable is not defined".</p>
  
  <pre><code>oChart.Location Where:=xlLocationAsNewSheet
  </code></pre>
  
  <p>Thank you,</p>
  
  <p>Jesse Smothermon</p>