[AccessD] Need creating graph in excel

Lonnie Johnson prodevmg at yahoo.com
Sat Oct 29 12:49:14 CDT 2005


Thanks Marty.

MartyConnelly <martyconnelly at shaw.ca> wrote: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



-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




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.  


More information about the AccessD mailing list