creating line graph from access query

[chainedtodesk]

I have an access query that gives me a count by days buy type, i want to create a line graph from this, with a line for each type. i can get a single line to work just by the dates and counts, but i cant get it to generate anything if i try to add the type in the code. the attached code works for the single line but i need to show them all types YTD.

ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=c:\users\rkepner-admin\Desktop\CustServ_2012.accdb;DefaultDir=c:\users\rkepner-admin\Desktop\;DriverId=25;FIL=MS Acc" _
), Array("ess;MaxBufferSize=2048;PageTimeout=5;")), Destination:=Range("$A$1") _
).QueryTable
.CommandText = Array( _
"SELECT `qryCC_TYPES`.CC_Date, `qryCC_TYPES`.CountofCCTYPE, `qryCC_TYPES`.CCTYPE" & Chr(13) & "" & Chr(10) & "FROM `c:\users\rkepner-admin\Desktop\CustServ_2012.accdb`.`qryCC_TYPES` `qryCC_TYPES`" _
)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_ExternalData_2"
.Refresh BackgroundQuery:=False
End With

'Count records
Dim vCount As Integer
vCount = 0
vCount = Range(Range("A1"), Range("A6655").End(xlUp)).Count

' Format data and name sheet
Range("A2:A" & vCount & "").Select
Selection.NumberFormat = "m/d/yyyy"
ActiveSheet.Name = "CC_TYPES"

'Set data up so that it can be graphed
Dim vStart As Date
Dim vEnd As Date
vEnd = Range("A" & vCount & "")

'create line chart
ActiveSheet.Shapes.AddChart.Select
With ActiveChart

.SetSourceData Source:=Range("'CC_TYPES'!$A$" & vCount - vCount + 1 & ":$B$" & vCount & "")
.ChartType = xlLine
.Parent.Height = 125
.Parent.Width = 200
.Axes(xlValue).HasTitle = True
.Axes(xlValue).AxisTitle.Characters.Text = "Issue Counts"
.Axes(xlValue).MinimumScale = 0
.HasTitle = True
.ChartTitle.Text = "CC_TYPES"
.ChartTitle.Font.Size = 9
.ChartTitle.Top = 0
.PlotArea.Top = 15.534
.PlotArea.Height = 90.645
.Legend.Delete
.PlotArea.Height = 100
.PlotArea.Width = 210
End With
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.NumberFormat = "m/d/yy;@"
' ActiveChart.SeriesCollection(1).Trendlines.Add
' With ActiveChart.SeriesCollection(1).Trendlines(1)
' .Type = xlPolynomial
' .Order = 5
' End With
ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlValue).MinimumScale = 0

End Sub

 

[Andrzejek]

I assume you are successful in getting the data itself from Access to Excel. Am I correct?

If so, can you do ‘by-hand’ in Excel: select the data and create a multi-line graph the way you want it?

If so, you can create a macro to see the code generated by Excel of how to achieve that.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.