Dawn - Thanks for replying. I looked at that and no. It doesn't seem to be a cell-level issue. I could type over the name of the county and it would work fine. If there was formatting that applied to the cell, I assume it would also affect the text that I typed. No/yes?
Mike --- In [email protected], "Dawn Crosier" <[EMAIL PROTECTED]> wrote: > > When you brought in the information from Access, did Excel give it a > different data type? For instance, was your test data formatted with > "General" and now the new data is formatted with "Text" and "Number"? > > Dawn Crosier > "Education Lasts a Lifetime" > > This message is posted to a newsgroup. Please post replies and questions to > the newsgroup so that others can learn as well. > > -----Original Message----- > From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf > Of takeadoe > Sent: Thursday, August 03, 2006 12:16 PM > To: [email protected] > Subject: [ms_access] So Strange, not sure what to call it! > > 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/
