VB operate with Excel

Posted on 2003-03-11
Medium Priority
Last Modified: 2010-04-07
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.ChartArea.Font.Size = 15
xlChart.ChartArea.Font.Color = vbRed
Image1.Picture = Clipboard.GetData(vbCFBitmap)

Please help me to olve these two problems with coding and explaination  
Question by:ryanlam2k3
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 11

Accepted Solution

Steiner earned 200 total points
ID: 8111804
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).AxisTitle.Text="My categories"
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.


Expert Comment

ID: 8114289
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.


Author Comment

ID: 8119792
can I able to set the chart size while it reture to VB form?
Independent Software Vendors: 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!

LVL 11

Expert Comment

ID: 8120121
You could change:

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)

Author Comment

ID: 8124965
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?

Expert Comment

ID: 9056441
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 
Post your closing recommendations!  No comment means you don't care.
LVL 49

Expert Comment

ID: 9097068
Moderator, my recommended disposition is:

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

DanRollins -- EE database cleanup volunteer

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question