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/
