Generating Charts in Excel through VB
Generating Charts in Excel through VB
Generating Charts in Excel through VB
Generating Charts in Excel through VB
Generating Charts in Excel through VB
Generating Charts in Excel through VB Generating Charts in Excel through VB Generating Charts in Excel through VB Generating Charts in Excel through VB Generating Charts in Excel through VB Generating Charts in Excel through VB Generating Charts in Excel through VB Generating Charts in Excel through VB
Generating Charts in Excel through VB Generating Charts in Excel through VB
Generating Charts in Excel through VB
Go Back  Xtreme Visual Basic Talk > > > Generating Charts in Excel through VB


Reply
 
Thread Tools Display Modes
  #1  
Old 06-23-2004, 01:26 PM
Stacey244 Stacey244 is offline
Regular
 
Join Date: Jun 2004
Location: Michigan
Posts: 74
Default Generating Charts in Excel through VB


Hey everyone, Ive been working on sending data from Access to Excel so i can generate charts. I get the data sent correctly and can generate a chart, but the data source isn't correct. For the first chart the data is set up where the first column is what I want on the X axis, the the next 2-10 columns depending, should be the line graphs (There may be up to 10 lines on one graph). For some reason, this code doesn't give me the option to do a line graph, only column, bar, etc, and i dont know why.
Code:
ochart = _ DirectCast(oXL.Charts.Add(After:=oSheet), Excel.Chart) Dim cellRange As Excel.Range = _ DirectCast(oSheet.Cells(3, 2), Excel.Range) ochart.ChartWizard( _ Source:=cellRange.CurrentRegion, _ Gallery:=Excel.Constants.xlColumn, _ 'xlColumn can't be changed to xlline PlotBy:=Excel.XlRowCol.xlColumns, _ CategoryLabels:=1, _ SeriesLabels:=2, _ HasLegend:=False, _ Title:="da/dN vs. Delta K") ' Apply some formatting to the chart. ochart.Name = oSheet.Name & " Chart" With DirectCast(ochart.ChartGroups(1), _ Excel.ChartGroup) .GapWidth = 20 .VaryByCategories = True End With With ochart.ChartTitle .Font.Size = 16 .Shadow = True .Border.LineStyle = Excel.Constants.xlSolid End With

I though it was the gallery part that allowed you to choose the type, but a choice for a line graph won't show up. Any ideas? Also, I have another one that is set up where it alternates x and y values for the column. Meaning that column 2 is the x values, then 3 is the y, then 4 is a different x, etc. The code above always assumes that the first column is the x values, how can I change that?

Ive been lookin at all the posts that are up for automating Excel from .NET and I know theres lots of problems. If there are any other sites that are good sources that I can check out besides the ones you have links to, please let me know, thanks.

Stacey
__________________
~Stacey~

All the world's a stage and all the men and women merely players...
Reply With Quote
  #2  
Old 06-25-2004, 03:30 AM
herilane's Avatar
herilaneGenerating Charts in Excel through VB herilane is offline
Unashamed geek

Retired Moderator
* Expert *
 
Join Date: Jul 2003
Location: London, England
Posts: 8,988
Default

In general, I would avoid the chart wizard, and set the chart properties one by one instead. (But then again, I'm the kind of person who avoids all wizards and office assistants... )

To make a line chart, use oChart.ChartType. To specify the x and y values for each series, use oChart.Seriescollection(i).XValues and .Values.

To find out the syntax for each property, the macro recorder in Excel is a good starting point. It'll give you VBA code and not VB.Net of course, but you'll see which property changes which part of the graph.
Reply With Quote
  #3  
Old 06-25-2004, 09:52 AM
Stacey244 Stacey244 is offline
Regular
 
Join Date: Jun 2004
Location: Michigan
Posts: 74
Default

Ok thanks, I'll try that. Im not really sure what the macro recorder in Excel is.. can you elaborate more? Also, it tells me that xlLine is not declared. Do I have to import something??
__________________
~Stacey~

All the world's a stage and all the men and women merely players...

Last edited by Stacey244; 06-25-2004 at 10:29 AM.
Reply With Quote
  #4  
Old 06-25-2004, 11:44 AM
herilane's Avatar
herilaneGenerating Charts in Excel through VB herilane is offline
Unashamed geek

Retired Moderator
* Expert *
 
Join Date: Jul 2003
Location: London, England
Posts: 8,988
Default

The macro recorder is a tool in Excel that allows you to record VBA code. Turn on the macro recorder, then do something in Excel (such as change the formatting of a chart), stop the macro recorder, and go have a look at the code. It tends to generate messy, ugly, dirty code... But at least it shows you what the name of a certain property is, or what the syntax of a method is, etc. I find it especially useful for generating code that deals with charts and pivot tables, and other components of Excel that have a somewhat complicated object structure. Also, the macro recorder generates VBA code, which is similar but not the same as the code you'd use in VB.Net. But it's useful for getting started in the right direction.

To turn on the macro recorder, go to Tools... Macros... Record new macro. Once you're done, Tools... Macros... Visual Basic Editor will take you to the code.

(I'll let somebody who actually has VB.Net answer the part about Excel constants... )
Reply With Quote
  #5  
Old 06-25-2004, 12:30 PM
Stacey244 Stacey244 is offline
Regular
 
Join Date: Jun 2004
Location: Michigan
Posts: 74
Default

Thanks a lot, that might help a lot
__________________
~Stacey~

All the world's a stage and all the men and women merely players...
Reply With Quote
  #6  
Old 06-25-2004, 01:08 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

As for 'xlLine', it is a member of the xlChartType Enumerated Constant, and has a value of 4.

Are you using Late Binding? If you instead added a reference to the "Microsoft Excel 11.0 Object Library" (or 10.0 or whatever your version is) then your compiler will not complain about such values. On the other hand, if you need to use Late Binding for cross-compatibility issues, then you should use
Code:
Const xlLine = 4
etc. You can look up these values within Excel Visual Basic Editor using the Object Browser.

-- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #7  
Old 07-01-2004, 11:55 AM
Stacey244 Stacey244 is offline
Regular
 
Join Date: Jun 2004
Location: Michigan
Posts: 74
Default

herilane,

Before you told me not to use the chart wizard and use seriescollection.. But Im having some problems with them. The spreadsheet is set up where the first column is the x values, then the next is y, the next is x of a new series, etc. If you have an example of using the seriescollection commands or know of a different site, that would be very useful. Ive been lookin up stuff on msdn and other sites but cannot find the information Im looking for. Any help would be appreciative, thank you. Any one else who knows a site, please let me know, thanks.

Oh and thank you Mike, I did have Excel referenced but it still wasnt working, however when I told it that xlline = 4 it works now, thanks.

Stacey
__________________
~Stacey~

All the world's a stage and all the men and women merely players...
Reply With Quote
  #8  
Old 07-01-2004, 01:57 PM
herilane's Avatar
herilaneGenerating Charts in Excel through VB herilane is offline
Unashamed geek

Retired Moderator
* Expert *
 
Join Date: Jul 2003
Location: London, England
Posts: 8,988
Default

Here's a VBA example. I can't give you a VB.Net version unfortunately since I don't actually have .Net myself. You'll have to modify all the object references to things like xlBook.ActiveChart etc etc. Hope this helps.
Code:
Sub CreateChart() 'Creates a scatter plot of data, assuming the data are located in Sheet1, 'arranged side by side in columns, from rows 1 to 10. 'Series 1 has X values in A1:A10, Y values in B1:B10. 'Series 2 has X values in C1:C10, Y values in D1:D10. ActiveWorkbook.Charts.Add ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1" With ActiveChart .ChartType = xlXYScatter .SeriesCollection.NewSeries .SeriesCollection(1).XValues = Worksheets("Sheet1").Range("a1:a10") .SeriesCollection(1).Values = Worksheets("Sheet1").Range("b1:b10") .SeriesCollection.NewSeries .SeriesCollection(2).XValues = Worksheets("Sheet1").Range("c1:c10") .SeriesCollection(2).Values = Worksheets("Sheet1").Range("d1:d10") .Axes(xlValue).MajorGridlines.Delete .PlotArea.ClearFormats End With End Sub
Reply With Quote
  #9  
Old 07-01-2004, 02:22 PM
Stacey244 Stacey244 is offline
Regular
 
Join Date: Jun 2004
Location: Michigan
Posts: 74
Default

Thanks, that def. gives me a good place to start. It's telling me a lot of type mismatch. So Im assuming thats because its not the correct syntax. Here's what I changed for mine
Code:
ochart2 = DirectCast(oXL.Charts.Add(After:=oSheet), Excel.Chart) ochart2.Name = "S-N Chart" With ochart2 .ChartType = xlline .SeriesCollection.NewSeries() .SeriesCollection(1).XValues = oSheet.Range("B30:B55") '<---Error here .SeriesCollection(1).Values = oSheet.Range("C30:C55") .SeriesCollection.NewSeries() .SeriesCollection(2).XValues = oSheet.Range("F30:F55") .SeriesCollection(2).Values = oSheet.Range("G30:G55") .PlotArea.ClearFormats() End With
It will run but it wont generate the graph cause of the line specified. I'm sure all will give me errors. Anything seem completly wrong that you see? Am I on the right track?
__________________
~Stacey~

All the world's a stage and all the men and women merely players...
Reply With Quote
  #10  
Old 07-01-2004, 03:19 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Ok, my Excel is currently unavailable, so I can't really test anything out at the moment... but I think you have to make sure that your Types are the same. Let's key on the followng line:
Code:
.SeriesCollection(1).XValues = oSheet.Range("B30:B55")
What Data Type is the .XYValues Property Let expecting? An array, I would assume, no? If it is looking for an array of values, then you need to be explicit about it and use:
Code:
.SeriesCollection(1).XValues = oSheet.Range("B30:B55").Value
Default parameters are not recognized in .Net if they do not take a parameter, and .Value takes no parameters.

This is my first guess. If this does not work, you'll have to look in help and tell me what .XYValues is expecting (what data type) for I just don't have any access to Excel nor the help files at the moment.

Hope this helps!
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #11  
Old 07-01-2004, 03:29 PM
Stacey244 Stacey244 is offline
Regular
 
Join Date: Jun 2004
Location: Michigan
Posts: 74
Default

It's telling me

"Unable to set the XValues property of the Series class"

when I add the .Value behind. Theres also a choice of .Value2. Do you know the difference? And how do I find what data type it needs? Cause when I type :

SeriesCollection(1).

it doesnt give me any options, but it allows XValues and Values.
__________________
~Stacey~

All the world's a stage and all the men and women merely players...
Reply With Quote
  #12  
Old 07-01-2004, 03:51 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Go with .Value for now. .Value2 is a restricted "variant" that does not allow for Dates and Currency data types. Frankly, it's unlikely to matter which one you use, but it's very, very rare that you need to use .Value2. And I don't see that it's an issue here at all. (You could try it though, but I don't see why that would help...)

This is tough for me at the moment, for I'd really need to kick this around in VBA to see what's going on, and I can't right now. Maybe Helen will be back or someone else will wonder by... I'll see who's around and can maybe give this a look...

Edit: Ok, actually, I uninstalled a bunch of DLL's and got my Excel up and going again... I'm trying your code now...
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by Mike Rosenblum; 07-01-2004 at 03:59 PM.
Reply With Quote
  #13  
Old 07-01-2004, 04:00 PM
Stacey244 Stacey244 is offline
Regular
 
Join Date: Jun 2004
Location: Michigan
Posts: 74
Default

Whether I can figure it out or not, thanks a lot for your help. This forum has helped me so much, and encouraged me to dig deeper in a lot of areas than I thought I would before. So thanks to all.
__________________
~Stacey~

All the world's a stage and all the men and women merely players...
Reply With Quote
  #14  
Old 07-01-2004, 04:05 PM
herilane's Avatar
herilaneGenerating Charts in Excel through VB herilane is offline
Unashamed geek

Retired Moderator
* Expert *
 
Join Date: Jul 2003
Location: London, England
Posts: 8,988
Default

No no no, you don't want values! Range.Value would paste the values as an array, but you would (normally) want the chart to be linked to the underlying range. So you want the Values property to be set to a Range.

To quote the help files:
Quote:
The XValues property can be set to a range on a worksheet or to an array of values, but it cannot be a combination of both. Read/write Variant.
Reply With Quote
  #15  
Old 07-01-2004, 04:10 PM
Stacey244 Stacey244 is offline
Regular
 
Join Date: Jun 2004
Location: Michigan
Posts: 74
Default

Ok, so no .Value on the end. Right? Ok.
__________________
~Stacey~

All the world's a stage and all the men and women merely players...
Reply With Quote
  #16  
Old 07-01-2004, 04:22 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

LOL, I'm such a noobie with Charts! Sorry!

Ok, I'm still having trouble within Excel VBA though? Get this fixed and we should be able to port to .Net fairly easily... Hope, hope....

Here's what I've got (with the '.Values' removed!):
Code:
Sub CreateChart() ' Modified from Herilane 2004 0701 Dim xlApp As Excel.Application Dim xlChart As Excel.Chart Dim rngXValues1 As Excel.Range Dim rngXValues2 As Excel.Range Dim rngSeries1 As Excel.Range Dim rngSeries2 As Excel.Range Set xlApp = Application With xlApp.Worksheets("Sheet1") Set rngXValues1 = .Range("a1:a10") Set rngXValues2 = .Range("b1:b10") Set rngSeries1 = .Range("c1:c10") Set rngSeries2 = .Range("d1:d10") End With Call XValuesLet(rngXValues1) Call XValuesLet(rngXValues2) Call SeriesValuesLet(rngSeries1) Call SeriesValuesLet(rngSeries2) Set xlChart = xlApp.ActiveWorkbook.Charts.Add Call xlChart.Location(xlLocationAsObject, Name:="Sheet1") With xlChart .ChartType = xlXYScatter ' <-- *** Automation Error *** .SeriesCollection.NewSeries .SeriesCollection(1).XValues = rngXValues1 .SeriesCollection(1).Values = rngSeries1 .SeriesCollection.NewSeries .SeriesCollection(2).XValues = rngXValues2 .SeriesCollection(2).Values = rngSeries2 .Axes(xlValue).MajorGridlines.Delete .PlotArea.ClearFormats End With End Sub Sub XValuesLet(XValuesRange As Excel.Range) ' Assigns a progressive series of values from 1..Count. Dim rngCell As Excel.Range Dim i As Long For Each rngCell In XValuesRange i = i + 1 rngCell.Value = i Next rngCell End Sub Sub SeriesValuesLet(SeriesRange As Excel.Range) ' Randomizes a series of values. Dim rngCell As Excel.Range Dim i As Long For Each rngCell In SeriesRange i = i + 1 rngCell.Value = i + Rnd * 2 Next rngCell End Sub
I'm getting an "Automation Error" at the line '.ChartType = xlXYScatter'. Helen, any idea how to get around this?

-- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by Mike Rosenblum; 07-01-2004 at 04:29 PM.
Reply With Quote
  #17  
Old 07-01-2004, 04:22 PM
herilane's Avatar
herilaneGenerating Charts in Excel through VB herilane is offline
Unashamed geek

Retired Moderator
* Expert *
 
Join Date: Jul 2003
Location: London, England
Posts: 8,988
Default

Sorry, that probably sounded more vehement than it was meant to be...
Reply With Quote
  #18  
Old 07-01-2004, 04:25 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

[Edit: You used a smiley, so I think it's ok... Stacey likes you. ]

Btw, I got Stacy's "Unable to set the XValues property of the Series class" when I tried to run it on blank cells... Which is why I added the XValuesLet() and SeriesValuesLet() routines.

Stacy, it is possible that it may be only the presence of Blank Cells that are giving you trouble? But I am personally still stuck on the XY Scatter aspect...
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by Mike Rosenblum; 07-01-2004 at 06:00 PM.
Reply With Quote
  #19  
Old 07-01-2004, 04:28 PM
herilane's Avatar
herilaneGenerating Charts in Excel through VB herilane is offline
Unashamed geek

Retired Moderator
* Expert *
 
Join Date: Jul 2003
Location: London, England
Posts: 8,988
Default

Mike, to fix your automation error, do this:
Code:
Set xlChart = xlApp.ActiveWorkbook.Charts.Add Call xlChart.Location(xlLocationAsObject, Name:="Sheet1") Set xlChart = xlApp.ActiveChart With xlChart 'code End With
When a chart is moved from being xlLocationAsNewSheet to being xlLocationAsObject, it gets recreated, so any old variables pointing to it will no longer work. The automation error is coming up because xlChart Is Nothing (or something like that).
Reply With Quote
  #20  
Old 07-01-2004, 04:34 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Ah, ok, that's weird, eh? Ok, I'm not sure that it looks right, but now it runs...

Stacy, you can try the following in VBA:
Code:
Sub CreateChart() Dim xlApp As Excel.Application Dim xlChart As Excel.Chart Dim rngXValues1 As Excel.Range Dim rngXValues2 As Excel.Range Dim rngSeries1 As Excel.Range Dim rngSeries2 As Excel.Range Set xlApp = Application With xlApp.Worksheets("Sheet1") Set rngXValues1 = .Range("a1:a10") Set rngXValues2 = .Range("b1:b10") Set rngSeries1 = .Range("c1:c10") Set rngSeries2 = .Range("d1:d10") End With Call XValuesLet(rngXValues1) Call XValuesLet(rngXValues2) Call SeriesValuesLet(rngSeries1) Call SeriesValuesLet(rngSeries2) Set xlChart = xlApp.ActiveWorkbook.Charts.Add Call xlChart.Location(xlLocationAsObject, Name:="Sheet1") Set xlChart = xlApp.ActiveChart With xlChart .ChartType = xlXYScatter .SeriesCollection.NewSeries .SeriesCollection(1).XValues = rngXValues1 .SeriesCollection(1).Values = rngSeries1 .SeriesCollection.NewSeries .SeriesCollection(2).XValues = rngXValues2 .SeriesCollection(2).Values = rngSeries2 .Axes(xlValue).MajorGridlines.Delete .PlotArea.ClearFormats End With End Sub Sub XValuesLet(XValuesRange As Excel.Range) ' Assigns a progressive series of values from 1..Count. Dim rngCell As Excel.Range Dim i As Long For Each rngCell In XValuesRange i = i + 1 rngCell.Value = i Next rngCell End Sub Sub SeriesValuesLet(SeriesRange As Excel.Range) ' Randomizes a series of values. Dim rngCell As Excel.Range Dim i As Long For Each rngCell In SeriesRange i = i + 1 rngCell.Value = i + Rnd * 2 Next rngCell End Sub
And now I'll try porting it to VB.Net...

-- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Generating Charts in Excel through VB
Generating Charts in Excel through VB
Generating Charts in Excel through VB Generating Charts in Excel through VB
Generating Charts in Excel through VB
Generating Charts in Excel through VB
Generating Charts in Excel through VB Generating Charts in Excel through VB Generating Charts in Excel through VB Generating Charts in Excel through VB Generating Charts in Excel through VB Generating Charts in Excel through VB Generating Charts in Excel through VB
Generating Charts in Excel through VB
Generating Charts in Excel through VB
 
Generating Charts in Excel through VB
Generating Charts in Excel through VB
 
-->