DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 11 of 11

Thread: Help with excel chart from vb script

  1. #1
    Join Date
    Feb 2005

    Help with excel chart from vb script

    I have been using the script found on this website to output excel charts from asp / vb code found here:

    However, I am having troubles making any other sort of graph besides a line errors out constantly saying:

    Error Type:
    Microsoft VBScript runtime (0x800A000D)
    Type mismatch
    /kfc/welcome.asp, line 281

    This is due to the fact, I am assuming, that I am trying to display the incorrect data to a graph...or vise versa. My code is here:

    <%Sub HandleRepeatVisit()
    	Dim ExcelApp 	' Our Excel App
        Dim WorkbookApp 		' Our Workbook within the Excel App
        Dim WorksheetApp		' Our Worksheet within the Workbook	
        Dim ChartApp 	' The chart object
        Dim SourceRangeApp ' The Source Range for the chart object
        Const xlWorkSheet = -4167 
    	Const xlChartType = 1
    		' -- Create an instance of Excel Application
        Set ExcelApp=Server.CreateObject("Excel.Application")
        ' -- Create a new workbook
        Set WorkbookApp=ExcelApp.Workbooks.Add(xlWorksheet)
    	' -- Grab the first worksheet of the new workbook
        Set WorksheetApp=WorkbookApp.Worksheets(1)
    	' -- Insert the data the user requested
    	' -- First, the title
        WorksheetApp.Range("A1").Value = "Category Percentages" 
    	' -- Then the data in two vertical columns
            WorksheetApp.Range("A2").Value = "C"
            WorksheetApp.Range("B2").Value = "=" & CPercentage  
            WorksheetApp.Range("A3").Value = "H"
            WorksheetApp.Range("B3").Value = "=" & HPercentage
            WorksheetApp.Range("A4").Value = "A"
            WorksheetApp.Range("B4").Value = "=" & APercentage
            WorksheetApp.Range("A5").Value = "M"
            WorksheetApp.Range("B5").Value = "=" & MPercentage
            WorksheetApp.Range("A6").Value = "P"
            WorksheetApp.Range("B6").Value = "=" & MPercentage
            WorksheetApp.Range("A7").Value = "S"
            WorksheetApp.Range("B7").Value = "=" & SPercentage
            WorksheetApp.Range("A8").Value = "Score"
            WorksheetApp.Range("B8").Value = "=" & RoundedActualScorePercentage
    ' -- Set our source range	    
        Set SourceRangeApp = WorksheetApp.Range("A2:B8")
    	' -- Create a new Chart Object
        Set ChartApp = WorksheetApp.ChartObjects.Add(20, 20, 300, 200)
    	' -- Generate the Chart using the ChartWizard
    	' -- Syntax is: 
    	' -- crt.Chart.ChartWizard Source:=SourceRange, gallery:=x1Line(4), PlotBy:=xlColumns(default), _
    	' -- categorylabels:=1, serieslabels:=0, HasLegend:=2, Title:="Test"
    	 ChartApp.Chart.ChartWizard SourceRangeApp , 4, , 2, 1, 0, 2, Session("username") & " Category Averages"
        ' -- Configure the Chart 
        ChartApp.Chart.ChartType = xlChartType 
        ChartApp.Chart.SeriesCollection(1).Name = "=Sheet1!R1C1"
        ChartApp.Chart.HasTitle = True
        ChartApp.Chart.Axes(1, 1).HasTitle = True
        ChartApp.Chart.Axes(1, 1).AxisTitle.Characters.Text = "Tests"
        ChartApp.Chart.Axes(2, 1).HasTitle = True
        ChartApp.Chart.Axes(2, 1).AxisTitle.Characters.Text = "Values"
    	' -- Determine the name to save this chart as. Use the current Seconds value, overwriting previous
    	' -- ones	
    	FileName =  "test" & Second(Now()) & ".jpg"
    	' -- Save the chart on web server 
        ChartApp.Chart.Export Server.Mappath("\kfc\charts") & "\" & FileName, "jpg"
    	' -- Fool Excel into thinking the Workbook is saved
        WorkbookApp.Saved = True
    	' -- Set all objects back to nothing
        Set ChartApp = Nothing
        Set WorkbookApp = Nothing
    	' -- Quit Excel to conserve resources
        Set ExcelApp = Nothing
    	' -- Make sure the Image is not cached but is loaded fresh from the web server
    	Response.AddHeader "expires","0"
    	Response.AddHeader "pragma", "no-cache"
    	Response.AddHeader "cache-control","no-cache"
    End Sub
    Code to notice is Const xlChartType = 1...this produces an area line graph, if I change it to 4, then it produces a regular line graph. If I change it to 2, which should be a bar graph, then I get that error...the line it points to is:
    line 281 is ChartApp.Chart.ChartType = xlChartType

    What am I doing wrong and what do I need to change? I am thinking it is this line:

    ChartApp.Chart.ChartWizard SourceRangeApp , 4, , 2, 1, 0, 2, Session("username") & " Category Averages"

    but, I am not!

  2. #2
    Join Date
    Nov 2004
    Are you using code to create the chart completly or do you have a form with the MSChart obj on it???? if you are using the MSChart obj from the toolbox, then when you right click on the obj you can select Properties, then in the properties you can select the tab for Chart, which should alow you to select a Chart Type -- 2D or 3D and select which type of chart like Bar/Pictograph or Line etc.

  3. #3
    Join Date
    Feb 2005
    The code is creating the chart itself, exporting it to a pic, and I display the pic.

  4. #4
    Join Date
    Nov 2004
    Could there be a problem with the Series Type Vs. the Chart type ???

  5. #5
    Join Date
    Feb 2005
    Where is the series type in the code? Is it the 20, 20, 300, 200 section? I played around with that a little bit and it only gave me page can't be displayed

  6. #6
    Join Date
    Nov 2004
    I don't know. I just looked at the MSChart object (which I use in one of my programs) and realized that the Series type seems to work inconjuntion with the Chart type. It's set to a number simuler to setting the Chart type to a number. When set to 0 it's set for a 3D Bar and 1 is a 2D Bar graph.

  7. #7
    Join Date
    Feb 2005
    This currently displays a 2d graph, and that would definately make sense; hoewever, I wish to make the bar chart 2d, by changing the chart type it should, hehe, should, work...I would think.

    Any recommendations on what to change in the code?

  8. #8
    Join Date
    Nov 2004
    no recommendations at this time. it's getting beyond my capabilitys. Sorry.

  9. #9
    Join Date
    Feb 2005
    Thanks for your help! Its beyond mine it from this webpage and apparantly I am unable to manipulate it to what I want

  10. #10
    Join Date
    Nov 2004
    I have a program that uses the object MSChart on a form. Maybe that might be a way to go for you. I could give you code for that and I would think that might be easyer to change to a bar type.

  11. #11
    Join Date
    Feb 2005
    Oh yes! If I got the mschart program, then I could probably modify it with no problem...I was looking at mschart the other day, it seemed a little hard, but I could probably get it to work. Email addy is or you can post here...thanks


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
HTML5 Development Center
Latest Articles
Questions? Contact us.
Web Development
Latest Tips
Open Source

   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center