none
Creating a table for excel from access vba RRS feed

  • Question

  • I want to create an excel chart from access data. My code is in access vba, and I am trying to use a chart object to build a chart. I know there are probably more sensible ways to do this, but I want to do it this way to gain a better understand how objects work, and become familiar with built-in methods vba has. For now, I am using data from a small range of cells in excel instead of a field in my database. (which I plan on changing after I get this to work).

    Currently, my file does open upon running the code, but the chart it creates is blank (but is visible).

    Most of the sources I found seemed vague and confusing. So if someone could provide me with specific examples of what I should do with my current code instead of linking me to a source, that would be greatly appreciated.

    Dim xlapp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim xlchart As Excel.Chart
    Dim xldata As Excel.Range

    Set xlapp = CreateObject("Excel.Application")
    Set xlBook = xlapp.Workbooks.Open("C:\Users\HELLERLO\Documents\Logan.xlsx")
    Set xlSheet = xlBook.Worksheets.Item(1)
    Set xldata = xlSheet.Range("A1:A4")

    Set xlchart = xlSheet.ChartObjects.Add(50, 40, 300, 200).Chart
    xlchart.SetDefaultChart Name:="Monthly Sales"

    xlchart.ChartWizard (xldata), Excel.XlChartType.xl3DColumn, PlotBy:=Excel.XlRowCol.xlColumns
    xlchart.Visible = True

    Friday, July 7, 2017 12:36 PM

Answers

  • Try replacing

    xlchart.ChartWizard (xldata), Excel.XlChartType.xl3DColumn, PlotBy:=Excel.XlRowCol.xlColumns
    xlchart.SetDefaultChart Name:="Monthly Sales"

    with

    xlchart.SetSourceData xlData, xlColumns
    xlchart.ChartType = xl3DColumn


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by LoganAccess Friday, July 7, 2017 1:11 PM
    Friday, July 7, 2017 1:00 PM

All replies

  • Try replacing

    xlchart.ChartWizard (xldata), Excel.XlChartType.xl3DColumn, PlotBy:=Excel.XlRowCol.xlColumns
    xlchart.SetDefaultChart Name:="Monthly Sales"

    with

    xlchart.SetSourceData xlData, xlColumns
    xlchart.ChartType = xl3DColumn


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by LoganAccess Friday, July 7, 2017 1:11 PM
    Friday, July 7, 2017 1:00 PM
  • Thank you! It works flawlessly now

    Friday, July 7, 2017 1:11 PM