Hi:
I used the following code found on Microsoft's support website (see below) to export a dataset from Access to Excel and create a 3D column chart from that dataset in Excel. Unfortunately, the chart isn't in the format that I want. I had created the chart (with the correct format in Access). Could anyone please give me some advice on how to modify the VBA code (found on Microsoft's website) in order to generate the correct chart. I have included an attachment displaying the chart that I want (created in Access), along with its rowsource property and underlying table. Thanks in advance for any assistance.
Function CreateChart(strSourceName As String, _
strFileName As String)
Dim xlApp As Excel.Application
Dim xlWrkbk As Excel.Workbook
Dim xlChartObj As Excel.Chart
Dim xlSourceRange As Excel.Range
Dim xlColPoint As Excel.Point
On Error GoTo Err_CreateChart
' Create an Excel workbook file based on the
' object specified in the second argument.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, _
strSourceName, strFileName, False
' Create a Microsoft Excel object.
Set xlApp = CreateObject("Excel.Application")
' Open the spreadsheet to which you exported the data.
Set xlWrkbk = xlApp.Workbooks.Open(strFileName)
' Determine the size of the range and store it.
Set xlSourceRange = _
xlWrkbk.Worksheets(1).Range("a1").CurrentRegion
' Create a new chart.
Set xlChartObj = xlApp.Charts.Add
' Format the chart.
With xlChartObj
' Specify chart type as 3D.
.ChartType = xl3DColumn
' Set the range of the chart.
.SetSourceData Source:=xlSourceRange, _
PlotBy:=xlColumns
' Specify that the chart is located on a new sheet.
.Location Where:=xlLocationAsNewSheet
' Create and set the title; set title font.
.HasTitle = True
With .ChartTitle
.Characters.Text = _
"Total Sales by Country"
.Font.Size = 18
End With
' Rotate the x-axis labels to a 45-degree angle.
.Axes(xlCategory).TickLabels.Orientation = 45
' Delete the label at the far right of the x-axis.
.Axes(xlSeries).Delete
' Delete the legend.
.HasLegend = False
' Set each datapoint to show the dollar amount
' and format the datapoint to be currency
' with no decimals.
With .SeriesCollection(1)
.ApplyDataLabels Type:=xlDataLabelsShowValue
.DataLabels.NumberFormat = "$#,##0"
End With
End With
' Position the points further from the tops
' of the columns.
For Each xlColPoint In _
xlChartObj.SeriesCollection(1).Points
xlColPoint.DataLabel.Top = _
xlColPoint.DataLabel.Top - 11
Next xlColPoint
' Save and close the workbook
' and quit Microsoft Excel.
With xlWrkbk
.Save
.Close
End With
xlApp.Quit
Exit_CreateChart:
Set xlSourceRange = Nothing
Set xlColPoint = Nothing
Set xlChartObj = Nothing
Set xlWrkbk = Nothing
Set xlApp = Nothing
Exit Function
Err_CreateChart:
MsgBox CStr(Err) & " " & Err.Description
Resume Exit_CreateChart
End Function
I used the following code found on Microsoft's support website (see below) to export a dataset from Access to Excel and create a 3D column chart from that dataset in Excel. Unfortunately, the chart isn't in the format that I want. I had created the chart (with the correct format in Access). Could anyone please give me some advice on how to modify the VBA code (found on Microsoft's website) in order to generate the correct chart. I have included an attachment displaying the chart that I want (created in Access), along with its rowsource property and underlying table. Thanks in advance for any assistance.
Function CreateChart(strSourceName As String, _
strFileName As String)
Dim xlApp As Excel.Application
Dim xlWrkbk As Excel.Workbook
Dim xlChartObj As Excel.Chart
Dim xlSourceRange As Excel.Range
Dim xlColPoint As Excel.Point
On Error GoTo Err_CreateChart
' Create an Excel workbook file based on the
' object specified in the second argument.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, _
strSourceName, strFileName, False
' Create a Microsoft Excel object.
Set xlApp = CreateObject("Excel.Application")
' Open the spreadsheet to which you exported the data.
Set xlWrkbk = xlApp.Workbooks.Open(strFileName)
' Determine the size of the range and store it.
Set xlSourceRange = _
xlWrkbk.Worksheets(1).Range("a1").CurrentRegion
' Create a new chart.
Set xlChartObj = xlApp.Charts.Add
' Format the chart.
With xlChartObj
' Specify chart type as 3D.
.ChartType = xl3DColumn
' Set the range of the chart.
.SetSourceData Source:=xlSourceRange, _
PlotBy:=xlColumns
' Specify that the chart is located on a new sheet.
.Location Where:=xlLocationAsNewSheet
' Create and set the title; set title font.
.HasTitle = True
With .ChartTitle
.Characters.Text = _
"Total Sales by Country"
.Font.Size = 18
End With
' Rotate the x-axis labels to a 45-degree angle.
.Axes(xlCategory).TickLabels.Orientation = 45
' Delete the label at the far right of the x-axis.
.Axes(xlSeries).Delete
' Delete the legend.
.HasLegend = False
' Set each datapoint to show the dollar amount
' and format the datapoint to be currency
' with no decimals.
With .SeriesCollection(1)
.ApplyDataLabels Type:=xlDataLabelsShowValue
.DataLabels.NumberFormat = "$#,##0"
End With
End With
' Position the points further from the tops
' of the columns.
For Each xlColPoint In _
xlChartObj.SeriesCollection(1).Points
xlColPoint.DataLabel.Top = _
xlColPoint.DataLabel.Top - 11
Next xlColPoint
' Save and close the workbook
' and quit Microsoft Excel.
With xlWrkbk
.Save
.Close
End With
xlApp.Quit
Exit_CreateChart:
Set xlSourceRange = Nothing
Set xlColPoint = Nothing
Set xlChartObj = Nothing
Set xlWrkbk = Nothing
Set xlApp = Nothing
Exit Function
Err_CreateChart:
MsgBox CStr(Err) & " " & Err.Description
Resume Exit_CreateChart
End Function
