Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • Last Modified:

VB operate with Excel

I can call Excel to create a chart automatically, and display is on VB form. However, I face two fatal prolbems now
1. How can I set the chart title, axis title by VB coding
2. How can I control the size while a chart displays on VB form? As my coding as following will show a chart very small and unclear.

Dim xlApp As Excel.Application
Set xlApp = New Excel.Application
Dim xlWkb As Excel.Workbook
Set xlWkb = xlApp.Workbooks.Open("E:\FYP\FYPtest\test\book1.xls")
Dim xlSht As Excel.Worksheet
Set xlSht = xlWkb.Worksheets(1)
Dim xlChart As Excel.Chart
Set xlChart = xlWkb.Charts.Add
xlChart.ChartType = 74
xlChart.SetSourceData xlSht.Range("A1:B10"), xlColumns
xlChart.Visible = xlSheetVisible
xlChart.Legend.Clear
xlChart.ChartArea.Font.Size = 15
xlChart.ChartArea.Font.Color = vbRed
xlChart.ChartArea.Select
xlChart.ChartArea.Copy
Image1.Picture = Clipboard.GetData(vbCFBitmap)

Please help me to olve these two problems with coding and explaination  
0
ryanlam2k3
Asked:
ryanlam2k3
1 Solution
 
SteinerCommented:
For the titles, try the following (right before you copy the chart into the clipboard):

xlChart.HasTitle = True   'A title is shown
xlChart.ChartTitle.Text ="My chart title"  'Set the text of the title
xlChart.Axes(xlCategory).HasTitle=True
xlChart.Axes(xlCategory).AxisTitle.Text="My categories"
xlChart.Axes(xlvalue).HasTitle=True
xlChart.Axes(xlValue).AxisTitle.Text="My Values"

As for the size, try to set the following property:
Image1.Stretch = True

This should stretch the chart to the size of your image box.

Greets
Steiner
0
 
jrollesCommented:
Another very help tool/hint is to turn on the "record macro" feature in excel, perform a set of task, stop the recorder and view the code.

Enjoy,
John
0
 
ryanlam2k3Author Commented:
can I able to set the chart size while it reture to VB form?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SteinerCommented:
You could change:
xlChart.PlotArea.Width
xlChart.PlotArea.Height

but since you copy the chart via the clipboard and paste it into a image-box (with the property Stretch = True), changing the size in Excel wouldn't do much good (except you want to use the chart in excel further, of course)
0
 
ryanlam2k3Author Commented:
However, If a image box size is small, the chart is very unclear, whould you like to suggest me that what can I do to solve this problem?
0
 
CleanupPingCommented:
ryanlam2k3:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 
DanRollinsCommented:
Moderator, my recommended disposition is:

    Accept Steiner's comment(s) as an answer.

DanRollins -- EE database cleanup volunteer
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now