how to extract chart of excel in visual basic 2005
how to extract chart of excel in visual basic 2005
how to extract chart of excel in visual basic 2005
how to extract chart of excel in visual basic 2005
how to extract chart of excel in visual basic 2005
how to extract chart of excel in visual basic 2005 how to extract chart of excel in visual basic 2005 how to extract chart of excel in visual basic 2005 how to extract chart of excel in visual basic 2005 how to extract chart of excel in visual basic 2005 how to extract chart of excel in visual basic 2005 how to extract chart of excel in visual basic 2005 how to extract chart of excel in visual basic 2005
how to extract chart of excel in visual basic 2005 how to extract chart of excel in visual basic 2005
how to extract chart of excel in visual basic 2005
Go Back  Xtreme Visual Basic Talk > > > how to extract chart of excel in visual basic 2005


Reply
 
Thread Tools Display Modes
  #1  
Old 06-18-2008, 09:31 PM
abelchoo abelchoo is offline
Newcomer
 
Join Date: Jun 2008
Posts: 20
Default how to extract chart of excel in visual basic 2005


Dear all, i would like to know, how do i copy a chart of excel into vb 2005.
Many thanks!

I have already done the macro:

This is the VBA:
Sub Macro1()
'
' Macro1 Macro
' This macro records the graph for audiometer
'

'
ActiveCell.FormulaR1C1 = "Audiometer Monitoring Control"
Range("A3").Select
ActiveCell.FormulaR1C1 = "Left Ear"
Range("A4").Select
ActiveCell.FormulaR1C1 = "Decibels"
Range("A5").Select
ActiveCell.FormulaR1C1 = "Frequency"
Range("A7").Select
ActiveCell.FormulaR1C1 = "Right Ear"
Range("A8").Select
ActiveCell.FormulaR1C1 = "Decibels"
Range("A9").Select
ActiveCell.FormulaR1C1 = "Frequency"
Range("B4").Select
Range("A12").Select
ActiveCell.FormulaR1C1 = "Y-Axis"
Range("B12").Select
ActiveCell.FormulaR1C1 = "X-Axis"
Range("A13").Select
ActiveCell.FormulaR1C1 = "60"
Range("A14").Select
ActiveCell.FormulaR1C1 = "50"
Range("A15").Select
ActiveCell.FormulaR1C1 = "40"
Range("A16").Select
ActiveCell.FormulaR1C1 = "30"
Range("A17").Select
ActiveCell.FormulaR1C1 = "20"
Range("A18").Select
ActiveCell.FormulaR1C1 = "10"
Range("A19").Select
ActiveCell.FormulaR1C1 = "0"
Range("A20").Select
ActiveCell.FormulaR1C1 = "-10"
Range("B13").Select
ActiveCell.FormulaR1C1 = "500"
Range("B14").Select
ActiveCell.FormulaR1C1 = "1000"
Range("B15").Select
ActiveCell.FormulaR1C1 = "2000"
Range("B16").Select
ActiveCell.FormulaR1C1 = "300"
ActiveWindow.LargeScroll Down:=1
Range("B16").Select
ActiveCell.FormulaR1C1 = "4000"
Range("B17").Select
ActiveCell.FormulaR1C1 = "6000"
Range("B18").Select
ActiveCell.FormulaR1C1 = "8000"
Range("B1").Select
ActiveSheet.Shapes.AddChart.Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1")
ActiveChart.ChartType = xlLineMarkers
ActiveChart.Axes(xlValue).Select
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.Axes(xlValue).Select
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.Axes(xlValue).MinimumScale = 0
ActiveChart.Axes(xlValue).MinimumScale = -10
ActiveChart.Axes(xlValue).MaximumScale = 1
ActiveChart.Axes(xlValue).MaximumScale = 60
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.PlotArea.Select
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.PlotArea.Select
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.SeriesCollection(1).Name = "=Sheet1!$A$3"
ActiveChart.SeriesCollection(1).Values = "=Sheet1!$B$4:$G$4"
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!$B$13:$B$18"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "=Sheet1!$A$7"
ActiveChart.SeriesCollection(2).Values = "=Sheet1!$B$8:$G$8"
ActiveChart.SeriesCollection(2).XValues = "=Sheet1!$B$13:$B$18"
Range("B9").Select
ActiveCell.FormulaR1C1 = "500"
Range("C9").Select
ActiveCell.FormulaR1C1 = "1000"
Range("D9").Select
ActiveCell.FormulaR1C1 = "2000"
Range("E9").Select
ActiveCell.FormulaR1C1 = "4000"
Range("F9").Select
ActiveCell.FormulaR1C1 = "6000"
Range("G9").Select
ActiveCell.FormulaR1C1 = "8000"
Range("B5").Select
ActiveCell.FormulaR1C1 = "500"
Range("C5").Select
ActiveCell.FormulaR1C1 = "1000"
Range("D5").Select
ActiveCell.FormulaR1C1 = "2000"
Range("E5").Select
ActiveCell.FormulaR1C1 = "4000"
Range("F5").Select
ActiveCell.FormulaR1C1 = "6000"
Range("G5").Select
ActiveCell.FormulaR1C1 = "8000"
Range("B8").Select
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.Axes(xlValue).Select
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.Axes(xlValue).ReversePlotOrder = True
ActiveChart.Axes(xlValue).ScaleType = xlLogarithmic
ActiveChart.Axes(xlValue).ScaleType = xlLinear
ActiveChart.Axes(xlValue).DisplayUnit = xlHundreds
ActiveChart.Axes().DisplayUnit = xlThousands
ActiveChart.Axes(xlValue).DisplayUnit = xlNone
ActiveChart.Axes(xlValue).ScaleType = xlLogarithmic
ActiveChart.Axes(xlValue).ScaleType = xlLinear
Selection.MajorTickMark = xlNone
Selection.MajorTickMark = xlInside
Selection.MinorTickMark = xlInside
Selection.MinorTickMark = xlOutside
Selection.MinorTickMark = xlCross
Selection.MinorTickMark = xlNone
Selection.TickLabelPosition = xlHigh
Selection.TickLabelPosition = xlLow
Selection.TickLabelPosition = xlNone
Selection.TickLabelPosition = xlNextToAxis
ActiveChart.Axes(xlValue).MinimumScale = -10
Range("M20").Select
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.PlotArea.Select
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.Axes(xlCategory).Select
ActiveChart.Axes(xlCategory).TickLabels.Offset = 1
ActiveChart.Axes(xlCategory).TickLabels.Offset = 1000
ActiveChart.Axes(xlCategory).TickLabels.Offset = 500
ActiveChart.Axes(xlCategory).TickLabels.Offset = 400
ActiveChart.Axes(xlCategory).TickLabels.Offset = 450
ActiveChart.Axes(xlCategory).TickLabels.Offset = 460
ActiveChart.Axes(xlCategory).TickLabels.Offset = 480
ActiveChart.Axes(xlCategory).TickLabels.Offset = 500
ActiveChart.Axes(xlCategory).TickLabels.Offset = 550
ActiveChart.Axes(xlCategory).TickLabels.Offset = 400
ActiveChart.Axes(xlCategory).TickLabels.Offset = 390
ActiveChart.Axes(xlCategory).TickLabels.Offset = 380
ActiveChart.Axes(xlCategory).TickLabels.Offset = 370
ActiveChart.Axes(xlCategory).TickLabels.Offset = 360
ActiveChart.Axes(xlCategory).TickLabels.Offset = 350
ActiveChart.Axes(xlCategory).TickLabels.Offset = 340
ActiveChart.Axes(xlCategory).TickLabels.Offset = 320
ActiveChart.Axes(xlCategory).TickLabels.Offset = 300
ActiveChart.Axes(xlCategory).TickLabels.Offset = 1000
Selection.TickLabelPosition = xlLow
Selection.TickLabelPosition = xlHigh
Selection.TickLabelPosition = xlLow
Selection.TickLabelPosition = xlHigh
Selection.TickLabelPosition = xlNone
Selection.TickLabelPosition = xlLow
ActiveChart.Axes(xlCategory).AxisBetweenCategories = False
ActiveChart.Axes(xlCategory).AxisBetweenCategories = True
ActiveChart.Axes(xlCategory).AxisBetweenCategories = False
ActiveChart.Axes(xlCategory).AxisBetweenCategories = True
ActiveChart.Axes(xlCategory).AxisBetweenCategories = False
ActiveChart.Axes(xlCategory).AxisBetweenCategories = True
Selection.TickLabelPosition = xlHigh
Selection.TickLabelPosition = xlLow
Selection.TickLabelPosition = xlNone
Selection.TickLabelPosition = xlHigh
ActiveChart.Axes(xlCategory).TickLabels.Offset = 500
ActiveChart.Axes(xlCategory).TickLabels.Offset = 400
ActiveChart.Axes(xlCategory).TickLabels.Offset = 200
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.PlotArea.Select
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.PlotArea.Select
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveWindow.SelectedSheets.PrintPreview
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.PlotArea.Select
ActiveSheet.ChartObjects("Chart 2").Activate
Range("B8").Select
End Sub
Reply With Quote
  #2  
Old 06-19-2008, 07:47 AM
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

Hi abelchoo,

Welcome to the forum.

Charts are not an easy topic. For example, you might want to have a look at this thread here which is an ongoing discussion on the topic.

It also looks like you are brand new to using VB.NET with Excel, so you might want to have a look at the Automating Office Programs with VB.NET tutorial to learn some of the basics.

As for your code, some of your code could be converted something like this, assuming that your Excel instance is named 'xlApp':

Before:
Code:
ActiveCell.FormulaR1C1 = "Left Ear"
Range("A4").Select
ActiveCell.FormulaR1C1 = "Decibels"
Range("A5").Select
After:
Code:
Dim myCell As Excel.Range

myCell = xlApp.Range("A4")
myCell.FormulaR1C1 = "Left Ear"

myCell = xlApp.Range("A5")
myCell.FormulaR1C1 = "Decibels"
Or using .Value instead of .FormulaR1C1:
Code:
Dim myCell As Excel.Range

myCell = xlApp.Range("A4")
myCell.Value = "Left Ear"

myCell = xlApp.Range("A5")
myCell.Value = "Decibels"

As for your code regarding charts, creating a chart programmatically is not easy -- the chart object model is a bit complicated. My personal preferred choice is to create a chart manually, and then only programmatically adjust the values within the range that the chart accesses. That way the chart axes, formatting, etc. are all preset manually -- and you can visually inspect that it looks the way that you want -- and then the programmatic elements merely have to update the data. You can see a discussion of this on another thread which is going on here: this thread here.

I hope this helps get you going abelchoo. Again, I would definitely give the Automating Office Programs with VB.NET tutorial a look to help you get started with the basics...

Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #3  
Old 07-10-2008, 03:24 AM
abelchoo abelchoo is offline
Newcomer
 
Join Date: Jun 2008
Posts: 20
Default Excel in VB 2005

Dear Mike,

Thanks so much for your help! Anyway, would like to know, is it possible to tell the data to send in values into the excel 2005? But the chart will appear on the GUI in VB 2005.

This would mean that e.g i send a data to tell the graph to plot a line. Would that mean that the data will have to go through excel then come out to VB 2005?

Sorry, don't really know how to explain. Hope you can understand
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
how to extract chart of excel in visual basic 2005
how to extract chart of excel in visual basic 2005
how to extract chart of excel in visual basic 2005 how to extract chart of excel in visual basic 2005
how to extract chart of excel in visual basic 2005
how to extract chart of excel in visual basic 2005
how to extract chart of excel in visual basic 2005 how to extract chart of excel in visual basic 2005 how to extract chart of excel in visual basic 2005 how to extract chart of excel in visual basic 2005 how to extract chart of excel in visual basic 2005 how to extract chart of excel in visual basic 2005 how to extract chart of excel in visual basic 2005
how to extract chart of excel in visual basic 2005
how to extract chart of excel in visual basic 2005
 
how to extract chart of excel in visual basic 2005
how to extract chart of excel in visual basic 2005
 
-->