[AccessD] Need creating graph in excel

Try this method to find a solution
http://www.vb123.com/toolshed/99_graphs/msgraph6.htm


Lonnie Johnson wrote:

>I have code that will create a graph in excel based on a query that I export to excel. The problem is that I need a three line graph for the particular query that I have. Does anyone know how to instruct Excel to plot three lines. My code is below: 
> 
> 
>Function CreateChart(strSourceName As String, _
>      strFileName As String)
>   Dim xlApp As Excel.Application
>   Dim xlWrkbk As Excel.Workbook
>   Dim xlChartObj As Excel.Chart
>   Dim xlSourceRange As Excel.Range
>   Dim xlColPoint As Excel.Point
>   'On Error GoTo Err_CreateChart
>   ' Create an Excel workbook file based on the
>   ' object specified in the second argument.
>   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
>         strSourceName, strFileName, False
>   ' Create a Microsoft Excel object.
>   Set xlApp = CreateObject("Excel.Application")
>   ' Open the spreadsheet to which you exported the data.
>   Set xlWrkbk = xlApp.Workbooks.Open(strFileName)
>   ' Determine the size of the range and store it.
>   Set xlSourceRange = _
>         xlWrkbk.Worksheets(1).Range("a1").CurrentRegion
>   ' Create a new chart.
>   Set xlChartObj = xlApp.Charts.Add
>   ' Format the chart.
>   With xlChartObj
>      ' Specify chart type as 3D.
>      .ChartType = xlLine
>      ' Set the range of the chart.
>      .SetSourceData Source:=xlSourceRange, _
>            PlotBy:=xlColumns
>      ' Specify that the chart is located on a new sheet.
>      .Location Where:=xlLocationAsNewSheet
>      ' Create and set the title; set title font.
>      .HasTitle = True
>      With .ChartTitle
>         .Characters.Text = _
>            "Total Sales by Country"
>         .Font.Size = 18
>      End With
>      ' Rotate the x-axis labels to a 45-degree angle.
>      '''''''''''.Axes(xlCategory).TickLabels.Orientation = 45
>      ' Delete the label at the far right of the x-axis.
>      ''''''''''.Axes(xlSeries).Delete
>      ' Delete the legend.
>      .HasLegend = False
>      ' Set each datapoint to show the dollar amount
>      ' and format the datapoint to be currency
>      ' with no decimals.
>      With .SeriesCollection(1)
>         .ApplyDataLabels Type:=xlDataLabelsShowValue
>         .DataLabels.NumberFormat = "$#,##0"
>      End With
>   End With
>   ' Position the points further from the tops
>   ' of the columns.
>   For Each xlColPoint In _
>         xlChartObj.SeriesCollection(1).Points
>      xlColPoint.DataLabel.Top = _
>            xlColPoint.DataLabel.Top - 11
>   Next xlColPoint
>   ' Save and close the workbook
>   ' and quit Microsoft Excel.
>   With xlWrkbk
>      .Save
>      .Close
>   End With
>   xlApp.Quit
>Exit_CreateChart:
>   Set xlSourceRange = Nothing
>   Set xlColPoint = Nothing
>   Set xlChartObj = Nothing
>   Set xlWrkbk = Nothing
>   Set xlApp = Nothing
>   Exit Function
>Err_CreateChart:
>   MsgBox CStr(Err) & " " & Err.Description
>   Resume Exit_CreateChart
>End Function
>
>
>
>
>May God bless you beyond your imagination!
>Lonnie Johnson
>ProDev, Professional Development of MS Access Databases
>Visit me at ==> http://www.prodev.us
>
>
>
>
>
> 
>
>
>
>
>
>		
>---------------------------------
> Yahoo! FareChase - Search multiple travel sites in one click.  
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada