Function CreateChart(strFileName As String, intRows As Integer, Bacteria As Integer)
Dim xlApp As Excel.Application
Dim xlWrkbk As Excel.Workbook
Dim xlWrkSh As Excel.Worksheet
Dim xlChartObj As Excel.Chart
Dim xlSourceRange As Excel.Range
Dim myRange As String
Dim firstlineflag As Boolean
Dim secondlineflag As Boolean
Dim blankcount As Integer
Dim BlankRange As String
Dim xlBlankTotal As Excel.Range
'Dim xlColPoint As Excel.Point
On Error GoTo Err_CreateChart
Select Case Bacteria
Case 7 'Enterococcus
myRange = "A1:A" & (intRows + 1) & ",G1:K" & (intRows + 1)
Case 8 'Fecal Coliforms
myRange = "A1:F" & (intRows + 1)
Case 49 'E. coli
myRange = "A1:A" & (intRows + 1) & ",L1:P" & (intRows + 1)
End Select
' Create an Excel workbook file based on the
' object specified in the second argument.
' 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("tmptbl_BacteriaStats").Range(myRange)
'make sure there is data for the first and second line or else you will get an error
Select Case Bacteria
Case 7 'Enterococcus
If DCount("[Sitenumber]", "tmptbl_BacteriaStats", "[Enterococcus - Mean - Geometric (Upper Limit)] & '' <> ''") = 0 Then firstlineflag = True
If DCount("[Sitenumber]", "tmptbl_BacteriaStats", "[Enterococcus - Maximum - Daily (Upper Limit)] & '' <> ''") = 0 Then secondlineflag = True
Case 8 'fecals
If DCount("[Sitenumber]", "tmptbl_BacteriaStats", "[Fecal Coliform - Mean - Geometric (Upper Limit)] & '' <> ''") = 0 Then firstlineflag = True
If DCount("[Sitenumber]", "tmptbl_BacteriaStats", "[Fecal Coliform - Percentile - 90th (Upper Limit)] & '' <> ''") = 0 Then secondlineflag = True
Case 49 'E coli
firstlineflag = True
secondlineflag = True
End Select
' Create a new chart.
Set xlChartObj = xlApp.Charts.Add
' Format the chart.
With xlChartObj
' Set the range of the chart.
.SetSourceData source:=xlSourceRange, PlotBy:=xlColumns
.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column"
' Specify that the chart is located on a new sheet.
Select Case Bacteria
Case 7 'Enterococcus
.Location WHERE:=xlLocationAsNewSheet, Name:="Enterococcus Results"
Case 8 'Fecal Coliforms
.Location WHERE:=xlLocationAsNewSheet, Name:="Fecal Results"
Case 49 'E. coli
.Location WHERE:=xlLocationAsNewSheet, Name:="E coli Results"
End Select
' Create and set the title; set title font.
.HasTitle = True
With .ChartTitle
Select Case Bacteria
Case 7 'Enterococcus
.Characters.Text = "Enterococcus Results vs Standards"
Case 8 'Fecal Coliforms
.Characters.Text = "Fecal Coliform Results vs Standards"
Case 49 'E. coli
.Characters.Text = "E. coli Results vs Standards"
Case Else
.Characters.Text = "Edit title Here"
End Select
.Font.Size = 18
End With
.PlotArea.ClearFormats
' Use the legend.
.HasLegend = True
With .Legend
.position = xlBottom
.LegendEntries(1).Delete
If firstlineflag = True Then
.LegendEntries(3).Delete
If secondlineflag = True Then .LegendEntries(3).Delete
Else
If secondlineflag = True Then .LegendEntries(4).Delete
End If
End With
'setup X-Axis
With .Axes(xlCategory, xlPrimary)
.HasTitle = False
.TickLabels.Orientation = 0
'.Border.LineStyle = xlAutomatic
.MajorTickMark = xlOutside
.MinorTickMark = xlNone
End With
.Axes(xlCategory, xlSecondary).HasTitle = False
.Axes(xlValue, xlSecondary).HasTitle = False
'Setup y-Axis
With .Axes(xlValue, xlPrimary)
.HasTitle = True
.MinimumScaleIsAuto = True
.MaximumScaleIsAuto = True
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLogarithmic
.DisplayUnit = xlNone
.AxisTitle.Characters.Text = "CFU/100mL"
'.Weight = xlHairline
'.LineStyle = xlAutomatic
.MajorTickMark = xlOutside
.MinorTickMark = xlNone
.TickLabelPosition = xlNextToAxis
End With
'hide the bars for the sample count series
With .SeriesCollection(1)
.Border.Weight = xlThin
.Border.LineStyle = xlNone
.Shadow = False
.InvertIfNegative = False
.Interior.ColorIndex = xlNone
.ApplyDataLabels Type:=xlDataLabelsShowValue, AutoText:=True, LegendKey:=False
End With
'format the sample count data labels
With .SeriesCollection(1).DataLabels
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.position = xlLabelPositionInsideBase
.Orientation = xlHorizontal
End With
With .SeriesCollection(2)
.Border.Weight = xlThin
.Border.LineStyle = xlAutomatic
.Shadow = False
.InvertIfNegative = False
.Interior.ColorIndex = 5
.Interior.Pattern = xlSolid
End With
With .SeriesCollection(3)
.Border.Weight = xlThin
.Border.LineStyle = xlAutomatic
.Shadow = False
.InvertIfNegative = False
.Interior.ColorIndex = 38
.Interior.Pattern = xlSolid
End With
'make sure there is data for the first line or else you will get an error
If firstlineflag = False Then
With .SeriesCollection(4)
.Border.Weight = xlMedium
.Border.ColorIndex = 5
.Border.LineStyle = xlContinuous
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
End If
'make sure there is data for the second line or else you will get an error
If secondlineflag = False Then
With .SeriesCollection(5)
.Border.Weight = xlMedium
.Border.ColorIndex = 3
.Border.LineStyle = xlContinuous
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
End If
End With
xlApp.ActiveWindow.Zoom = 100
' 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
