Sign in to follow this  
Followers 0
silvaa

Embedded Chart in excel

2 posts in this topic

Looking for some help with embedding a chart in an excel worksheet. I have played with an example I found in the forums which works great, but the chart is in a chart sheet and I need to display it on the worksheet over a range of cells where the starting row of the chart will vary depending on the rows of data the script inserts. I have only recently started using Autoit and its been great, i've managed to extract data from an old database system a friend is using and put it into a mysql database, created some GUI's to view data and export to excel, hasnt been too hard so far, but I just cant figure out how to get the chart where I want it. I have found some VBS code that gives me some clues, but I dont know VBS. I have trawled through Randallc's Excelcom but cant figure it out yet, although I managed to figure out cell sizing, colours and borders, font sizes etc.

This is the example I have been using:

$EA=ObjCreate("Excel.Application")

$EA.Workbooks.Add

$EA.Worksheets(3).Delete

$EA.Worksheets(2).Delete

For $I=1 To 20

For $J=1 To 20

$EA.Cells($I,$J).Value=Random(1,20,1)

Next

Next

$EA.Range("A1:T20").Select

$EA.Charts.Add()

$EA.ActiveChart.HasTitle=TRUE

$EA.ActiveChart.ChartTitle.Characters.Text="20 by 20 Random Numbers 1 to 20"

$EA.ActiveChart.ChartType=62

$EA.Visible = True

msgbox(4096,"Excel Chart","Click OK When Done...")

$EA.Quit

$Tit="Microsoft Excel"

Opt("WinTitleMatchMode",3)

WinWait($Tit)

ControlClick($Tit,"","[iD:7]")

this is the bit of VBS I found at http://peltiertech.com/Excel/ChartsHowTo/ResizeAndMoveAChart.html

Sub CoverRangeWithAChart()

Dim RngToCover As Range

Dim ChtOb As ChartObject

Set RngToCover = ActiveSheet.Range("D5:J19")

Set ChtOb = ActiveChart.Parent

ChtOb.Height = RngToCover.Height ' resize

ChtOb.Width = RngToCover.Width ' resize

ChtOb.Top = RngToCover.Top ' reposition

ChtOb.Left = RngToCover.Left ' reposition

End Sub

but I think the chart sheet is a different object than the ChartObject

any help to point me in the right direction would be great

Share this post


Link to post
Share on other sites



In case anyone else is interested this works

$oExcel=ObjCreate("Excel.Application")
$oExcel.Workbooks.Add
$oExcel.Worksheets(3).Delete
$oExcel.Worksheets(2).Delete
$wsheet ="Sheet1"
For $I=1 To 20
For $J=1 To 20
$oExcel.Cells($I,$J).Value=Random(1,20,1)
Next
Next
$oExcel.Range("A1:T20").Select
$oExcel.Charts.Add()
$oExcel.ActiveChart.HasTitle=TRUE
$oExcel.ActiveChart.ChartTitle.Characters.Text="20 by 20 Random Numbers 1 to 20"
$oExcel.ActiveChart.ChartType=51
$oExcel.ActiveChart.Location (2, $wsheet) ;xlLocationAsObject
    With $oExcel.ActiveChart.Parent
        .Top = $oExcel.ActiveSheet.Range("A21:T50").Top
        .Left = $oExcel.ActiveSheet.Range("A21:T50").Left
        .Height = $oExcel.ActiveSheet.Range("A21:T50").Height
        .Width = $oExcel.ActiveSheet.Range("A21:T50").Width
     EndWith
$oExcel.Visible = True
msgbox(4096,"Excel Chart","Click OK When Done...")
$oExcel.Quit
$Tit="Microsoft Excel"
Opt("WinTitleMatchMode",3)
WinWait($Tit)
ControlClick($Tit,"","[ID:7]")

anyone know how I can do this using R1C1 ?

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0