M
mtonkovich
The following program was written by "Bernie" and it worked beautifully
until I replaced the sample data with data pulled from Access using MS
Query. The program is designed to generate some very simple xy plots
with the data you see below. Ultimately I need to generate 88 charts,
hence the need to automate. Please read on for the problem.
The sample data looked just like this:
Washington 1981 898
Washington 1982 813
Washington 1983 600
Washington 1984 168
Washington 1985 419
Washington 1986 1076
Washington 1987 2013
Washington 1988 3828
Washington 1989 6414
Washington 1990 9823
Washington 1991 14595
Washington 1992 20926
Washington 1993 29545
Washington 1994 41193
Washington 1995 644
Washington 1996 526
Washington 1997 571
Washington 1998 415
Washington 1999 525
Washington 2000 572
Washington 2001 760
Washington 2002 773
Washington 2003 803
Washington 2004 759
Washington 2005 695
Allen 1981 468
Allen 1982 490
Allen 1983 522
Allen 1984 577
Allen 1985 674
Allen 1986 816
Allen 1987 967
Allen 1988 1146
Allen 1989 1308
Everything went south when I pulled the data from Access via MS Query
and tried to plot it. The data looked identical to the sample above.
Same column headings, same number of rows per county, same font, same
everything. As you'll see here in just a minute, apparently there was
something different about the data that came from the query, something
that was not apparent to the naked eye. The program would crash after
it successfully generated a chart for the first county in the list.
The offending line in the program is the 2nd to the last line of code.
I've tagged it with some ****. It crashed because it was trying to
create another sheet and name/rename the sheet using a sheet name
(sheets are given the county name) are being set equal to the name of
the county) that already existed. During the debugging process, I
discovered that the "program" thought that the first (1995) and last
entry (2005) for the name of the county were somehow different. In
this list of unique names (which I gather was the source for the
counter in the loops in the program) it had the county names listed
twice - once for the first entry in the series and once for the last.
I tried everything to remedy the problem. I cut the data from the
query recordset and appended it the list you see above. I pasted it
into the middle and the top of the list and still no luck. I even
tried to cut and paste just the values and still no luck. The only way
I could get it to work was to literally retype the county names for the
offending counties after pasting the data. After that, all went well.
Bear in mind that I could paste the year and harvest numbers in from
the recordset, but I had to retype the county name to get it to work.
Clearly, there was something hidden in the text that came from the
query. I did notice that the County name was padded with blanks and I
used the TRIM function to eliminate them. However, this still didn't
remedy the problem. If anyone has any idea at all, I would really love
to hear from you. I'm willing to go to plan "B." Problem is, I don't
really have one at this point. It took a long time to get to this
point.
Also, I might mention that when I commented out the line which set the
sheet name equal to the county name, it did several weird things.
First, there are 88 counties and it only generated 67 sheets named
chart 1 to chart 67. Second, there was no data plotted, only titles
listed.
Regards,
Mike
Sub GraphByUniqueCategory()
Dim myList() As Variant
Dim i As Integer
Dim j As Integer
Dim myCount As Integer
Dim chtDeer As Chart
Dim shtData As Worksheet
Dim rngData As Range
Dim myDataSet As Range
Dim strCounty As String
myCount = 1
Set shtData = Worksheets("Sheet1")
With shtData.Range("A2").CurrentRegion.Columns(1)
..AdvancedFilter Action:=xlFilterInPlace, Unique:=True
ReDim myList(1 To .SpecialCells(xlCellTypeVisible).Count)
With .SpecialCells(xlCellTypeVisible)
For j = 1 To .Areas.Count
For i = 1 To .Areas(j).Cells.Count
myList(myCount) = .Areas(j).Cells(i).Value
myCount = myCount + 1
Next i
Next j
End With
ActiveSheet.ShowAllData
End With
Set myDataSet = shtData.Range("B2").CurrentRegion
For i = LBound(myList) + 1 To UBound(myList)
'MsgBox "Now doing " & myList(i)
shtData.Range("A2").AutoFilter Field:=1, Criteria1:=myList(i)
Set rngData = Intersect(myDataSet,
shtData.Range("B:E").SpecialCells(xlCellTypeVisible))
strCounty = Trim(shtData.Range("A65536").End(xlUp).Value)
' make a chart
Set chtDeer = Charts.Add
With chtDeer
'ActiveSheet.ChartObjects.Activate
.ChartType = xlXYScatterLines
.SetSourceData Source:=rngData, PlotBy:=xlColumns
.Location Where:=xlLocationAsNewSheet
.HasTitle = True
.ChartTitle.Characters.Text = strCounty & " County" & vbCr & "
Accounting-style and Lang & Wood w Downing Population Estimates,
1981-present"
ActiveChart.ChartTitle.Select
Selection.Characters(Start:=1, Length:=7 + Len(strCounty)).Font.Size
= 18
Selection.Characters(Start:=8 + Len(strCounty),
Length:=80).Font.Size = 14
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Year"
.Axes(xlCategory).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Population
estimate"
.Axes(xlValue).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
.HasLegend = True
***** .Name = strCounty & " County"*****
End With
until I replaced the sample data with data pulled from Access using MS
Query. The program is designed to generate some very simple xy plots
with the data you see below. Ultimately I need to generate 88 charts,
hence the need to automate. Please read on for the problem.
The sample data looked just like this:
Washington 1981 898
Washington 1982 813
Washington 1983 600
Washington 1984 168
Washington 1985 419
Washington 1986 1076
Washington 1987 2013
Washington 1988 3828
Washington 1989 6414
Washington 1990 9823
Washington 1991 14595
Washington 1992 20926
Washington 1993 29545
Washington 1994 41193
Washington 1995 644
Washington 1996 526
Washington 1997 571
Washington 1998 415
Washington 1999 525
Washington 2000 572
Washington 2001 760
Washington 2002 773
Washington 2003 803
Washington 2004 759
Washington 2005 695
Allen 1981 468
Allen 1982 490
Allen 1983 522
Allen 1984 577
Allen 1985 674
Allen 1986 816
Allen 1987 967
Allen 1988 1146
Allen 1989 1308
Everything went south when I pulled the data from Access via MS Query
and tried to plot it. The data looked identical to the sample above.
Same column headings, same number of rows per county, same font, same
everything. As you'll see here in just a minute, apparently there was
something different about the data that came from the query, something
that was not apparent to the naked eye. The program would crash after
it successfully generated a chart for the first county in the list.
The offending line in the program is the 2nd to the last line of code.
I've tagged it with some ****. It crashed because it was trying to
create another sheet and name/rename the sheet using a sheet name
(sheets are given the county name) are being set equal to the name of
the county) that already existed. During the debugging process, I
discovered that the "program" thought that the first (1995) and last
entry (2005) for the name of the county were somehow different. In
this list of unique names (which I gather was the source for the
counter in the loops in the program) it had the county names listed
twice - once for the first entry in the series and once for the last.
I tried everything to remedy the problem. I cut the data from the
query recordset and appended it the list you see above. I pasted it
into the middle and the top of the list and still no luck. I even
tried to cut and paste just the values and still no luck. The only way
I could get it to work was to literally retype the county names for the
offending counties after pasting the data. After that, all went well.
Bear in mind that I could paste the year and harvest numbers in from
the recordset, but I had to retype the county name to get it to work.
Clearly, there was something hidden in the text that came from the
query. I did notice that the County name was padded with blanks and I
used the TRIM function to eliminate them. However, this still didn't
remedy the problem. If anyone has any idea at all, I would really love
to hear from you. I'm willing to go to plan "B." Problem is, I don't
really have one at this point. It took a long time to get to this
point.
Also, I might mention that when I commented out the line which set the
sheet name equal to the county name, it did several weird things.
First, there are 88 counties and it only generated 67 sheets named
chart 1 to chart 67. Second, there was no data plotted, only titles
listed.
Regards,
Mike
Sub GraphByUniqueCategory()
Dim myList() As Variant
Dim i As Integer
Dim j As Integer
Dim myCount As Integer
Dim chtDeer As Chart
Dim shtData As Worksheet
Dim rngData As Range
Dim myDataSet As Range
Dim strCounty As String
myCount = 1
Set shtData = Worksheets("Sheet1")
With shtData.Range("A2").CurrentRegion.Columns(1)
..AdvancedFilter Action:=xlFilterInPlace, Unique:=True
ReDim myList(1 To .SpecialCells(xlCellTypeVisible).Count)
With .SpecialCells(xlCellTypeVisible)
For j = 1 To .Areas.Count
For i = 1 To .Areas(j).Cells.Count
myList(myCount) = .Areas(j).Cells(i).Value
myCount = myCount + 1
Next i
Next j
End With
ActiveSheet.ShowAllData
End With
Set myDataSet = shtData.Range("B2").CurrentRegion
For i = LBound(myList) + 1 To UBound(myList)
'MsgBox "Now doing " & myList(i)
shtData.Range("A2").AutoFilter Field:=1, Criteria1:=myList(i)
Set rngData = Intersect(myDataSet,
shtData.Range("B:E").SpecialCells(xlCellTypeVisible))
strCounty = Trim(shtData.Range("A65536").End(xlUp).Value)
' make a chart
Set chtDeer = Charts.Add
With chtDeer
'ActiveSheet.ChartObjects.Activate
.ChartType = xlXYScatterLines
.SetSourceData Source:=rngData, PlotBy:=xlColumns
.Location Where:=xlLocationAsNewSheet
.HasTitle = True
.ChartTitle.Characters.Text = strCounty & " County" & vbCr & "
Accounting-style and Lang & Wood w Downing Population Estimates,
1981-present"
ActiveChart.ChartTitle.Select
Selection.Characters(Start:=1, Length:=7 + Len(strCounty)).Font.Size
= 18
Selection.Characters(Start:=8 + Len(strCounty),
Length:=80).Font.Size = 14
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Year"
.Axes(xlCategory).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Population
estimate"
.Axes(xlValue).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
.HasLegend = True
***** .Name = strCounty & " County"*****
End With