Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Access to Excel 2007 curious behavior
I have written an application that worked fine with Excel 2003.
I have upgraded to Excel 2007 and some problems occur when translating Access tables into Excel charts. Here is the sample of code of that worked fine with Excel 2003 : Dim Excel As Excel.Application Set Excel = New Excel.Application Excel.Workbooks.Add Dim worksheet As Excel.worksheet Dim r As Recordset I = 1 Screen.MousePointer = 11 Excel.Sheets(1).Select With Excel.ActiveSheet 'Columnheaders ..Range("A" & I).Value = "profileNumber" ..Range("B" & I).Value = "templateNumber" ..Range("C" & I).Value = "typeNumber" ..Range("D" & I).Value = "dates" I = I + 1 On Error Resume Next Set r = G_DBManager.GetModelElementsCount Dim count As Long r.MoveFirst Do Until r.EOF ..Range("A" & I).Value = r("profileNumber") ..Range("B" & I).Value = r("templateNumber") ..Range("C" & I).Value = r("typeNumber") ..Range("D" & I).Value = r("dates") I = I + 1 r.MoveNext Loop ' End If count = r.RecordCount + 1 r.Close ..Range("D").NumberFormat = "m/d/yy" Excel.Visible = True End With Excel.Charts.Add With Excel.ActiveChart ..ChartType = xlLineMarkers ..HasAxis(xlCategory, xlPrimary) = True ..HasAxis(xlValue, xlPrimary) = True ..Axes(xlCategory).AxisBetweenCategories = True ..Axes(xlCategory, xlPrimary).CategoryType = xlCategoryScale ..Axes(xlCategory, xlPrimary).HasTitle = True ..Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "date" ..Axes(xlValue, xlPrimary).HasTitle = True ..Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = _ "Count of Model Elements" ..HasTitle = True ..ChartTitle.Characters.Text = "Count of Model Elements" ..SetSourceData Source:=Excel.ActiveSheet.Range("A1:C" & count), PlotBy:= _ xlColumns ..SeriesCollection(1).Values = "=Sheet1!R2C1:R" & count & "C1" ..SeriesCollection(1).name = "=Sheet1!R1C1" ..SeriesCollection(2).Values = "=Sheet1!R2C2:R" & count & "C2" ..SeriesCollection(2).name = "=Sheet1!R1C2" ..SeriesCollection(3).Values = "=Sheet1!R2C3:R" & count & "C3" ..SeriesCollection(3).name = "=Sheet1!R1C3" ..SeriesCollection(1).XValues = "=Sheet1!R2C4:R" & count & "C4" ..Location Whe=xlLocationAsObject, name:="Sheet1" The chart is a Line type. The first 3 columns are the 3 parameters.( Y axis value) .The 4th column is the date column ( X axis). The problem is that the chart put the 4th column(dates) as a Y axis value in Excel 2007.This is curious , especially since no problem was found for Excel 2003. What is the problem with excel 2007 ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Where did my Publish to Excel Services go? | Setting up and Configuration of Excel | |||
Using Access in Excel VB Code | Excel Discussion (Misc queries) | |||
Changing the format of an Excel output file made by Microsoft Access | Excel Discussion (Misc queries) | |||
Excel aficionado wants to learn Access | Excel Discussion (Misc queries) | |||
Excel user desires to learn ABC of Access | Excel Discussion (Misc queries) |