Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrong columns sometimes selected --?
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: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: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 Whe=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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrong columns sometimes selected --?
This also fails in Excel 2003, with the same error message.
Alan Alan wrote: 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: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: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 Whe=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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrong columns sometimes selected --?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro deletes wrong columns | Excel Discussion (Misc queries) | |||
Multiple cells or columns are selected instead of selected cell or | Excel Discussion (Misc queries) | |||
columns wrong data | Excel Discussion (Misc queries) | |||
Adding columns using dates, but getting wrong data | Excel Worksheet Functions | |||
Wrong data format in columns ... What's going on? | Excel Programming |