Need help creating graph with automation 1

[LonnieJohnson]

In MS Access 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

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[SkipVought]

Hi,

Is your query returning 4 columns...

one category or XValues column and

three Values columns for your 3 series?

Please post a small sample of your query results.

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[SkipVought]

An additional comment that has no bearing on the question at hand.

When you ADD a Chart, it already IS a NewSheet. So the statement...

      ' Specify that the chart is located on a new sheet.
      .Location Where:=xlLocationAsNewSheet

is unnecessary.

However, if you had, instead, specified the Where property as xlLocationAsObject, you would have been required to name a SHEET where the chart is to be embedded. This would ALSO change the Chart from a Chart Object to a ChartObject Object. Sounds a bit confusing, and it is.

Charts & VBA faq707-4811

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[LonnieJohnson]

Thanks. Here is the query output. It Year column are the three lines that should be plotted, Amount will be the Y axis and Pe will be the X axis. It's a crosstab query.

Can this be done via vba? Thanks for taking an intrest in this.

Client_Name Year Pe Amount
Aur Resources Inc. 2004 01 $542.57
Aur Resources Inc. 2004 02 $6,044.61
Aur Resources Inc. 2004 03 $1,061.19
Aur Resources Inc. 2004 06 $49.76
Aur Resources Inc. 2004 12 $350.00
Aur Resources Inc. 2005 01 $6,050.00
Aur Resources Inc. 2005 02 $2,250.00
Aur Resources Inc. 2005 03 $250.00
Aur Resources Inc. 2005 04 $2,384.65
Aur Resources Inc. 2005 05 $5,878.55
Aur Resources Inc. 2005 06 $4,571.22
Aur Resources Inc. 2005 07 $3,500.74
Aur Resources Inc. 2005 08 $802.00
Aur Resources Inc. Variance 01 $5,507.43
Aur Resources Inc. Variance 02 ($3,794.61)
Aur Resources Inc. Variance 03 ($811.19)
Aur Resources Inc. Variance 06 $4,521.46

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[SkipVought]

It doesn't look like a crosstab result???

You can do a Pivot Table/Chart using the Query Table Name as the source data reference (this will make the pivot table/chart dynamic).

I'd set the whole thing up in Excel, and run MS Query to get the resultset from Access.

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[LonnieJohnson]

I'm sorry. It is not a crosstab. It's the result of a union query between a regular select query and a crosstab. There is a program that allows a user to choose one or multiple clients from a list box. The program then iterates through the client names and generates four graphs. This query result is one of the four. A report object with the four graphs and the query detail along with each graph is then sent to a printer. Now the user wants to be able to create an excel file for each customer selected in the list box as well to email to the clients. This is the final step of a larger project.

I was looking for something simple to result from what I already have in place. I know it was not going to be too simple, but I was trying to avoid re-creating what I have in excel.

Can you be clearer on "'d set the whole thing up in Excel, and run MS Query to get the resultset from Access."?

Thanks again.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[SkipVought]

In Excel on a new sheet...

menu item Data/Get External Data/New Database Query -- access files -- Your access database -- your table or access query.....

The query can be a parameter query so the user can select a client and the query result will be for that selection.

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[LonnieJohnson]

Thanks for your help SkipVought. I created a pivot table from the exported data and recorded the process in a macro. I then took the code from the macro and put into my Access vba code. It works.

I have another question. How do I rename the new active worksheet that the graph is created on?

This is the new code...

Function CreateChart2(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.
   
   
     ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "excelChart1!R1C1:R18C3").CreatePivotTable TableDestination:="", _
        TableName:="PivotTable4", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    Charts.Add
    ActiveChart.Location Where:=xlLocationAsNewSheet
    ActiveWorkbook.ShowPivotTableFieldList = True
    ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _
        PivotTable.PivotFields("Amount"), "Sum of Amount", xlSum
    With ActiveChart.PivotLayout.PivotTable.PivotFields("Year")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With ActiveChart.PivotLayout.PivotTable.PivotFields("Pe")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.Location Where:=xlLocationAsNewSheet
    ActiveChart.HasLegend = True
    ActiveChart.Legend.Select
    Selection.Position = xlRight
   
   

   ' 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

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[SkipVought]

The Chart's Name property.

    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="New Name"

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[LonnieJohnson]

Thanks again.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!