Wrong columns sometimes selected -->?

[Alan]

I am running Excel 2007 on Vista and have experienced a strange
problem. . . . What I am doing is finding CSV files in a directory
hierarchy, modifying their format, adding a couple of charts, and
saving them as Excel (.xls) files. The input (CSV) files all have a
standard format (produced by the same software) and appear normal in
Excel.

The problem: After processing a number of files correctly, I get
the error "Run-time Error '1004' Invalid Parameter. When I debug, I
see that it failed at a line in CreateWPMchart:

ActiveChart.SeriesCollection(3).Select

where it is attempting to change the color of a data series. When I
check on the source data for the chart, VBA appears to be selecting
the wrong columns for use in a chart! Instead of selecting A:A, C:C,
E:E and G:G for the plotting range, it selects "A:C, G:G". So, there
is no "SeriesCollection(3)" to select.

The input file has the correct columns in the correct order,
just like all the others. I have no idea why this problem is
occurring. I am not doing anything unusual, as far as I know.

It always hiccups on this particular file. However, if I get
rid of the file, the same problem occurs on another CSV file.

Any ideas on why this is failing and/or how I should further
debug? The pertinent code may be found below.

Thanks in advance, Alan

Sub FixCreateAndPrintAllCharts()

Dim StartDir As String
StartDir = ThisWorkbook.Path
Dim s As String
Dim currdir As String
Dim dirlist As New Collection

If Right$(StartDir, 1) <> "\" Then StartDir = StartDir & "\"
dirlist.Add StartDir

While dirlist.Count
' remove current directory from directory list
currdir = dirlist.Item(1)
dirlist.Remove 1
'find all files and subdirectories in current directory, and add
them to list
s = Dir$(currdir, vbDirectory)
While Len(s)
If (s <> ".") And (s <> "..") Then 'get rid of parent and
current directory
If GetAttr(currdir & s) = vbDirectory Then 'add the
subdirectory
dirlist.Add currdir & s & "\"
Else 'process the file if it is right name
If (s Like "FIXED_PerfWiz?*.csv") Then
Workbooks.Open (currdir & "\" & s)
FixAndCreateCharts
' delete any existing data chart file
Kill (currdir & "\" & "Data_Charts_FIXED_*.xls")
' save the workbook
LResult = Replace(ActiveWorkbook.FullName,
"FIXED_", "Data_Charts_FIXED_")
LResult = Replace(LResult, ".csv", ".xls")
ActiveWorkbook.SaveAs Filename:=LResult _
, FileFormat:=xlNormal, Password:="",
WriteResPassword:="", _
ReadOnlyRecommended:=False,
CreateBackup:=False
ActiveWorkbook.Close
End If
End If
End If
s = Dir$
Wend
Wend

End Sub


Sub FixAndCreateCharts()

' change format
ActiveCell.Columns("A:G").EntireColumn.Select
Selection.ColumnWidth = 17.71
ActiveCell.Rows("1:1").EntireRow.Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Font.Bold = True
FormatPageFileByte

' create charts
Call CreateWPMchart("A:A,B:B,D,F:F", "Memory Utilization", "Page
File Bytes")
Call CreateWPMchart("A:A,C:C,E:E,G:G", "CPU Utilization", "% Processor
Time")

End Sub


Sub FormatPageFileByte()
ActiveCell.Range("B:B,D,F:F").Select
Selection.NumberFormat = "0.00E+00"
End Sub

Sub CreateWPMchart(ColumnRange As String, ChartName As String,
yAxisTitle As String)

Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("FIXED_PerfWiz - 5 second
interv"). _
Range(ColumnRange), PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=ChartName
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = ChartName
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = _
"Time (5 sec. intervals)"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text =
yAxisTitle
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
ActiveChart.HasDataTable = False

ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(3).Select
With Selection.Border
.ColorIndex = 10
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = 10
.MarkerForegroundColorIndex = 10
.MarkerStyle = xlTriangle
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With

ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(2).Select
With Selection.Border
.ColorIndex = 9
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = 9
.MarkerForegroundColorIndex = 9
.MarkerStyle = xlSquare
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With

End Sub

 

[Alan]

This also fails in Excel 2003, with the same error message.
Alan

 

[Alan]

It turns out that the screwed up charts were a result of having
some cells containing a space in the columns being charted. Not sure
why Excel works that way, but I imagine without apply column
formatting, that it could not determine the data type of those
cells.

Hope this helps someone else avoid this problem. Alan