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
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/ms_access/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
