Try taking your Access data to Notepad and then from Notepad into Excel. It's possible that there are extra characters coming through which are not visible, yet they mess up Excel. In notepad look for those square boxes to indicate non-ASCII characters.
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 2:32 PM To: [email protected] Subject: [ms_access] Re: So Strange, not sure what to call it! 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 -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.10.5/403 - Release Date: 7/28/2006 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/
