Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create Chart Function (2003)

    I found the following function:

    Large chunk of code moved to attachment by HansV.

    I have a few questions: In the immediate window I put the following to create the chart in Excel:
    ?CreateChart("qrySalesByCountry","Cocuments and Settingslid7kseMy documentsSales.xls")

    If I wanted to create this chart from the Switchboard for example, how would I do this.
    I am thinking I can use this function in any database and change the above query (to a different query) and location and name of Excel file accordingly. I just don't know the syntax for using it.

    Also, this creates a bar chart. Would it be too hard (and where in the code would you do this) to change to another type of chart - say a Pie chart?

    Thanks for any help you can provide.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create Chart Function (2003)

    You can create a procedure (sub) that calls the function:

    Public Sub MyChart()
    Call CreateChart("qrySalesByCountry","Cocuments and Settingslid7kseMy documentsSales.xls")
    End Sub

    In the Switchboard Manager, create a new item with Run Code as Command and the name of the procedure (MyChart in this example) as Function.

    The lines in the code that set the chart type are:

    ' Specify chart type as 3D.
    .ChartType = xl3DColumn

    To create a 'flat' Pie chart, use

    .ChartType = xlPie

    or to create a 3D pie chart:

    .ChartType = xl3DPie

    You can look up these values in the online help - click in the word ChartType and press F1.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create Chart Function (2003)

    Thanks Hans. The smallest tip (click in the word ChartType and press F1.) is so helpful.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create Chart Function (2003)

    Here's another small tip then: if you click in xl3DColumn and press F1, you'll get a message that no help is available. However, you can right-click instead and select Definition from the popup menu. This will open the Object Browser and show information about the word you clicked on.

    You can open the Object Browser at any time in the Visual Basic Editor by pressing F2, and type a search term in the Search box.

  5. #5
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Create Chart Function (2003)

    Is it possible instead of quitting Excel, to make it visible? That would address the inability to close it, and would work better since I would not have to navigate to the directory to locate the file to open it.

  6. #6
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Create Chart Function (2003)

    I borrowed and modified the code in an attempt to create a spreadsheet with a couple of charts. However, I have a few of questions relating to its use. First, I want to run through the code twice to create two charts in the same spreadsheet. I used the following code in an attempt to accomplish the task:

    Private Sub cmd_ExcelChart_Click()
    On Error GoTo Err_cmd_ExcelChart_Click
    Dim strPath As String

    'Delete MySpreadsheet.xls here
    strPath = "C:MYDIRMySpreadsheet.xls"
    Kill strPath

    Call CreateChart("sqRateLocal", "C:MYDIRMySpreadsheet.xls","Rate DOMESTIC")
    Call CreateChart("sqRateALL", "C:MYDIRMySpreadsheet.xls", "RATE ALL")

    Exit_cmd_ExcelChart_Click:
    Exit Sub

    Err_cmd_ExcelChart_Click:
    MsgBox Err.Description
    Resume Exit_cmd_ExcelChart_Click

    End Sub

    The problems are:
    1. I am not sure how to test for the existence of a file in VB, so if MySpreadsheet.xls does not exist I don't need to kill it.
    2. The second time through the code, it gets an error. "1004 Method ' ActiveChart' of object' _Global' failed". Opening the spreadsheet shows both data sets, but only the first chart (sqRateLocal data set).
    3. Excel continues to run in the background. Only way to quit it is via TaskManager "Processes". It doesn't show to be running as an Application on the task manager, only on the Processes tab. Is there a way to quit Excel without using the TaskManager?

    TIA for any help.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create Chart Function (2003)

    To test the existence of a file, you can use the Dir function, e.g.

    If Not Dir(strPath) = "" Then
    Kill strPath
    End If

    The code always refers to the first worksheet instead of to the relevant worksheet. This wasn't important when there was only one worksheet, but now it is.

    There are some unqualified references to ActiveChart. They cause an extra instance of Excel to be created and to remain in memory.

    I have attached a modified version of the code that remedies this and that makes the workbook visible at the end.

  8. #8
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Create Chart Function (2003)

    Hans,

    I'll give it a shot. As usual, THANKS for you assistance.

    Ken

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •