Cannot Plot Null Series

[kylephilly]

Hi All,

I'm exporting a table from Access into separate Excel sheets to be plotted and graphed. The problem I am running into is that on about the 20th export it is trying to plot a chart that is missing Series(4) of 7. Therefore, there is no data to plot. I know this is problem a pretty simple issue but I can't find a way to code around it.

-I've tried an if statement that sums the row and says if <> 0 then don't plot
-I've tried the built in Excel commands ActiveChart.DisplayBlankAs = xlNotPlotted

I can't find a work around. The error is related to runtime error 1004: Unable to set the XValue of Series

Here is my code:

Option Compare Database


Public Sub Excel_Export()
'Creates Queries that will eventually go to Excel

Dim db As DAO.Database
Dim counter As Integer, intMaxRow As Long, intMaxCol As Integer
Dim stfFileName As String

intMaxCol = 8 'might have to change this if more columns are graphed!

' first create objects for the excel application:

Dim objApp As Excel.Application
Dim objWb As Excel.Workbook
Set objApp = Nothing
Set objWb = Nothing
Dim objSheet As Excel.Worksheet
Dim objChart As Object ' Excel chart object
Dim txtBox As TextBox

Dim db2 As DAO.Database
Dim qry As DAO.QueryDef
Dim rst, rstState, rstDrugName As DAO.Recordset
Dim objfield As DAO.Field
Dim x As Integer

Dim strSQL, strDrugName, strDrug As String
Dim cellRange1, cellRange2, cellRange3, cellRange4, cellRange5, cellRange6, cellRange7 As String




'Create Recordsets for Each NDC



'strDrugName = "insert into tb_temp_Drug_Name select tb_temp_NDCs.[Drug Name], tb_temp_NDCs.State, tb_temp_NDCs.NDC from tb_temp_NDCs WHERE NDC = '" & rst![NDC] & "' AND STATE = '" & rst![State] & "'"
'DoCmd.RunSQL (strDrugName)

'strState = "select tb_temp_NDCs.[State2] from tb_temp_NDCs where tb_temp_NDCs.[State] = rst![State]"
'DoCmd.OpenQuery (strState)


Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT tbl_Graphing_Data_All.NDC, tbl_Graphing_Data_All.STATE FROM [tbl_Graphing_Data_All] WHERE NDC IS NOT NULL GROUP BY tbl_Graphing_Data_All.NDC, tbl_Graphing_Data_All.STATE")
rst.MoveFirst



Set db = CurrentDb
Set rstState = db.OpenRecordset("SELECT tbl_Graphing_Data_All.NDC, max(tbl_Graphing_Data_All.[Drug Name]) AS DRUGNAME, tbl_Graphing_Data_All.State, tbl_Graphing_Data_All.State2 FROM tbl_Graphing_Data_All WHERE NDC IS NOT NULL GROUP BY tbl_Graphing_Data_All.NDC, tbl_Graphing_Data_All.STATE, tbl_Graphing_Data_All.STATE2")
rstState.MoveFirst



Do Until rst.EOF

strSQL = "delete * from tb_temp_NDCs"
DoCmd.RunSQL (strSQL)

strSQL = "insert into tb_temp_NDCs select tbl_Graphing_Data_All.[Period], tbl_Graphing_Data_All.[Avg Reimbursement], tbl_Graphing_Data_All.Flag, tbl_Graphing_Data_All.[Avg Reimb less Dispensing Fee], AWP, tbl_Graphing_Data_All.[Medicaid AWP], EAC, FUL, tbl_Graphing_Data_All.WAC, tbl_Graphing_Data_All.[Drug Name], tbl_Graphing_Data_All.[AWP Percent], tbl_Graphing_Data_All.[State], tbl_Graphing_Data_All.[State2], tbl_Graphing_Data_All.NDC FROM tbl_Graphing_Data_All WHERE NDC = '" & rst![NDC] & "' AND STATE = '" & rst![State] & "'"
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings False
strSQL3 = "delete * from tb_temp_StateNames"
DoCmd.RunSQL (strSQL3)

strSQL2 = _
"insert into tb_temp_StateNames select tbl_Graphing_Data_All.NDC, tbl_Graphing_Data_All.State, tbl_Graphing_Data_All.State2, tbl_Graphing_Data_All.[Drug Name]FROM TBL_GRAPHING_DATA_ALL WHERE NDC = '" & rstState![NDC] & "' AND STATE = '" & rstState![State] & "' GROUP BY tbl_Graphing_Data_All.NDC, tbl_Graphing_Data_All.STATE, tbl_Graphing_Data_All.STATE2, tbl_Graphing_Data_All.[Drug Name]"
DoCmd.RunSQL (strSQL2)

strFileName = "G:\FAS\FAS_DA&I\Schering Plough\State Specific Analyses\8 Albuterol States\Export\" & rst![NDC] & "_" & rst![State] & ".xls"
DoCmd.TransferSpreadsheet transfertype:=acExport, tablename:="tb_temp_NDCs", Filename:=strFileName

Set objApp = CreateObject("Excel.Application")
objApp.Visible = False
Set objWb = objApp.Workbooks.Open(strFileName)
Set objSheet = objWb.Worksheets("tb_temp_NDCs")
objSheet.Name = rst![NDC] & "_" & rst![State]

intMaxRow = objSheet.Cells(objSheet.Rows.Count, 1).End(xlUp).Row
objSheet.Range(objSheet.Cells(2, 2), objSheet.Cells(intMaxRow, intMaxCol)).NumberFormat = "$#,##0.00"

objApp.Application.Charts.Add
With objApp
.ActiveChart.ChartType = xlLineMarkers
.ActiveChart.Location WHERE:=xlLocationAsNewSheet
'.ActiveChart.SetSourceData (objsheet.Range(objsheet.Cells(2,1), objsheet.Cells(intmaxrow,intmaxcol)), xlcolumns)
.ActiveChart.SetSourceData Source:=objSheet.Range(objSheet.Cells(1, 1), objSheet.Cells(intMaxRow, intMaxCol)), _
PlotBy:=xlColumns
.ActiveChart.Name = rst![NDC] & "_" & rst![State] & "_Chart"
With objApp.ActiveChart
.DisplayBlanksAs = 0
.PlotVisibleOnly = False
.SizeWithWindow = False
.PlotArea.Interior.ColorIndex = 2 'Changes background of chart to white
.PlotArea.Height = 334
.PlotArea.Width = 661
.HasLegend = True
.Legend.Position = xlLegendPositionBottom
.Legend.Font.Size = 8
.Legend.Font.FontStyle = "Regular"
.Legend.Font.Name = "Arial"
.Legend.Left = 31
.Legend.Top = 351
.Legend.Width = 638
.Legend.Height = 48
.PageSetup.CenterHeader = "&B" & rstState![State2] & " State" & " Medicaid Program" & Chr(10) & rstState![DRUGNAME] & " " & rst![NDC] & Chr(10) & "Average Reimbursement Statistics by Quarter"
.PageSetup.LeftFooter = "Privileged & Confidential"
.PageSetup.CenterFooter = "DRAFT"
.PageSetup.RightFooter = "Attorney Work Product"
End With

'Addding Textbox For Notes

objApp.ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 29.17, 406.07, 634.6, 45).TextFrame.Characters.Text = _
"Note:" & Chr(10) & _
"(1) Reimbursement and utilization data obtained from Centers for Medicare and Medicaid Services." & Chr(10) & _
"(2) High and Low price per unit data is derived from the wholesale price as described in the Warrick Pharmaceuticals Pricing Update letters."

'Period Series
objApp.ActiveChart.Axes
With objApp.ActiveChart.Axes(xlCategory)
'.CrossesAt = 1
.TickLabelSpacing = 1
.TickMarkSpacing = 1
'.AxisBetweenCategories = True
'.ReversePlotOrder = False
End With
With objApp.ActiveChart.Axes(xlCategory).TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
With objApp.ActiveChart.Axes(xlCategory).TickLabels
.Alignment = xlCenter
.Offset = 100
.ReadingOrder = xlContext
.Orientation = xlUpward
End With


'Series 11111111111111111
Set cellRange1 = objSheet.Range(objSheet.Cells(2, 1), objSheet.Cells(intMaxRow, 1))
If objApp.WorksheetFunction.Sum(objSheet.Range(objSheet.Cells(2, 1), objSheet.Cells(intMaxRow, 1))) = Null Then cellRange1 = "N/A" Else
objApp.ActiveChart.DisplayBlanksAs = xlNotPlotted
objApp.ActiveChart.SeriesCollection(1).XValues = objSheet.Range(objSheet.Cells(2, 1), objSheet.Cells(intMaxRow, 1))
objApp.ActiveChart.SeriesCollection(1).Select
With objApp.ActiveChart.SeriesCollection(1).Border
.ColorIndex = 57
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With objApp.ActiveChart.SeriesCollection(1)
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlDiamond
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With

With objApp.ActiveChart.SeriesCollection(1)
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlSquare
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
'Format spacing of ticklabels
With objApp.ActiveChart.Axes(xlCategory)
.CrossesAt = 1
.TickLabelSpacing = 1
.TickMarkSpacing = 1
.AxisBetweenCategories = True
.ReversePlotOrder = False
End With
'Format the font for ticklabels in the x axis
With objApp.ActiveChart.Axes(xlCategory).TickLabels
.Font.Name = "Arial"
.Font.Size = 8
.Alignment = xlCenter
.Offset = 100
.ReadingOrder = xlContext
.Orientation = xlUpward
End With
'This next line must come at the end or it will error out
objApp.ActiveChart.SeriesCollection(1).Name = "Avg Reimbursement Dollars per mg/ml, Including Dispensing Fees"



'Series 2222222222222222222
Set cellRange2 = objSheet.Range(objSheet.Cells(2, 2), objSheet.Cells(intMaxRow, 2))
If objApp.WorksheetFunction.Sum(objSheet.Range(objSheet.Cells(2, 2), objSheet.Cells(intMaxRow, 2))) = Null Then cellRange2 = "N/A" Else
objApp.ActiveChart.DisplayBlanksAs = xlNotPlotted
objApp.ActiveChart.SeriesCollection(2).XValues = objSheet.Range(objSheet.Cells(2, 2), objSheet.Cells(intMaxRow, 2))
objApp.ActiveChart.SeriesCollection(2).Select
With objApp.ActiveChart.SeriesCollection(2).Border
.ColorIndex = 1
.Weight = xlThick
.LineStyle = xlDot
End With
With objApp.ActiveChart.SeriesCollection(2)
.MarkerBackgroundColorIndex = 1
.MarkerForegroundColorIndex = 1
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With

With objApp.ActiveChart.Axes(xlValue).TickLabels
.Font.Name = "Arial"
.Font.Size = 8
'.Alignment = xlCenter
.Offset = 100
.ReadingOrder = xlContext
'.Orientation = xlUpward
End With
'This next line must come at the bottom or it will error out
objApp.ActiveChart.SeriesCollection(2).Name = "Avg Reimbursement Dollars per mg/ml, Excluding Dispensing Fees"


'Series 33333333333333333
Set cellRange3 = objSheet.Range(objSheet.Cells(2, 3), objSheet.Cells(intMaxRow, 3))
If objApp.WorksheetFunction.Sum(objSheet.Range(objSheet.Cells(2, 3), objSheet.Cells(intMaxRow, 3))) = Null Then cellRange3 = "N/A" Else
'If objApp.WorksheetFunction.Sum(objSheet.Range(objSheet.Cells(2, 3), objSheet.Cells(intMaxRow, 3))) <> 0 Then
objApp.ActiveChart.DisplayBlanksAs = xlNotPlotted
objApp.ActiveChart.SeriesCollection(3).XValues = objSheet.Range(objSheet.Cells(2, 3), objSheet.Cells(intMaxRow, 3))
objApp.ActiveChart.SeriesCollection(3).Select
With objApp.ActiveChart.SeriesCollection(3).Border
.ColorIndex = 44
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With objApp.ActiveChart.SeriesCollection(3)
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlSquare
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
'This next line must come at the bottom or it will error out
objApp.ActiveChart.SeriesCollection(3).Name = "AWP from First DataBank"


'Series 44444444444444444
Set cellRange2 = objSheet.Range(objSheet.Cells(2, 4), objSheet.Cells(intMaxRow, 4))
If objApp.WorksheetFunction.Sum(objSheet.Range(objSheet.Cells(2, 4), objSheet.Cells(intMaxRow, 4))) = Null Then cellRange4 = "N/A" Else
objApp.ActiveChart.DisplayBlanksAs = xlNotPlotted
objApp.ActiveChart.SeriesCollection(4).XValues = objSheet.Range(objSheet.Cells(2, 4), objSheet.Cells(intMaxRow, 4))
objApp.ActiveChart.SeriesCollection(4).Select
With objApp.ActiveChart.SeriesCollection(4).Border
.ColorIndex = 10
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With objApp.ActiveChart.SeriesCollection(4)
.MarkerBackgroundColorIndex = 10
.MarkerForegroundColorIndex = 10
.MarkerStyle = xlTriangle
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
objApp.ActiveChart.SeriesCollection(4).Name = "Medicaid AWP per mg/ml"


'Series 5555555555555555555
If objApp.WorksheetFunction.Sum(objSheet.Range(objSheet.Cells(2, 5), objSheet.Cells(intMaxRow, 5))) <> 0 Then
objApp.ActiveChart.DisplayBlanksAs = xlNotPlotted
objApp.ActiveChart.SeriesCollection(5).XValues = objSheet.Range(objSheet.Cells(2, 5), objSheet.Cells(intMaxRow, 5))
objApp.ActiveChart.SeriesCollection(5).Select
With objApp.ActiveChart.SeriesCollection(5).Border
.ColorIndex = 54
.Weight = xlThick
.LineStyle = xlContinuous
End With
With objApp.ActiveChart.SeriesCollection(5)
.MarkerBackgroundColorIndex = 54
.MarkerForegroundColorIndex = 54
.MarkerStyle = xlCircle
.Smooth = False
.MarkerSize = 7
.Shadow = False
End With
objApp.ActiveChart.SeriesCollection(5).Name = "EAC or (Estimated Acquisition Cost) per mg/ml"
End If

'Series 66666666666666666
If objApp.WorksheetFunction.Sum(objSheet.Range(objSheet.Cells(2, 6), objSheet.Cells(intMaxRow, 6))) <> 0 Then
objApp.ActiveChart.DisplayBlanksAs = xlNotPlotted
objApp.ActiveChart.SeriesCollection(6).XValues = objSheet.Range(objSheet.Cells(2, 6), objSheet.Cells(intMaxRow, 6))
objApp.ActiveChart.SeriesCollection(6).Select
With objApp.ActiveChart.SeriesCollection(6).Border
.ColorIndex = 1
.Weight = xlThick
.LineStyle = xlContinuous
End With
With objApp.ActiveChart.SeriesCollection(6)
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 9
.Shadow = False
End With
objApp.ActiveChart.SeriesCollection(6).Name = "FUL or (Federal Upper Limit) per mg/ml"
End If


'Series 77777777777777777777777
If objApp.WorksheetFunction.Sum(objSheet.Range(objSheet.Cells(2, 7), objSheet.Cells(intMaxRow, 7))) <> 0 Then
objApp.ActiveChart.DisplayBlanksAs = xlNotPlotted
objApp.ActiveChart.SeriesCollection(7).XValues = objSheet.Range(objSheet.Cells(2, 7), objSheet.Cells(intMaxRow, 7))
objApp.ActiveChart.SeriesCollection(7).Select
With objApp.ActiveChart.SeriesCollection(7).Border
.ColorIndex = 3
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With objApp.ActiveChart.SeriesCollection(7)
.MarkerBackgroundColorIndex = 3
.MarkerForegroundColorIndex = 3
.MarkerStyle = xlSquare
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
objApp.ActiveChart.SeriesCollection(7).Name = "WAC from First DataBank"
End If

objWb.Save
objWb.Close (True)
objApp.Quit
Set objApp = Nothing
Set objWb = Nothing
Set objSheet = Nothing

'Moves to next record and starts of Loop for each sheet
End With
rst.MoveNext
Loop


End Sub

 

[kylephilly]

Why is there no edit post option?

 

[kylephilly]

I resolved this issue.

I did a loop that checked for null cells. The trick was that and IF statement that checked for nulls had to be placed in front of EVERY line that effects a series.