Dumping data into Excel from VB - How do I position the "Chart object"??

Posted on 2003-03-13
Medium Priority
Last Modified: 2010-04-07
I have the following procedure which works quite well. To cut a long story short, oXL is a global excel object, I read a lot of numbers into arrays, dump them onto an excel sheet, send in the row and offset to the following function and it creates the chart. I do this about 6 times. So I have 6 blocks of data on a sheet with a chart associated with each block.
So far so good. My problem is that all the charts are on top of each other and I have to manually move them every time the sheet is created. How do I position the chart by code. I tried the move method but it failed with a run time error 1004:

Sub DoGraph(ByVal nSheet As Long, ByVal nRow As Long, ByVal nRowOffset As Long, _
            ByVal sTitle As String)
    Dim oChart As Excel.Chart
    Dim oSheet As Excel.Worksheet
    Dim oRange As Excel.Range
    Dim sAdd1 As String
    Dim sAdd2 As String
    Set oSheet = Sheets(nSheet)
    ' set up range address
    sAdd1 = CStr(oSheet.Cells(nRow, 1).Address)
    sAdd2 = CStr(oSheet.Cells(nRow, 1).Offset(-nRowOffset, g_objProject.LastYear).Address)

    Set oRange = oSheet.Cells()
    Set oChart = oXL.ActiveWorkbook.Charts.Add
    oChart.ChartType = xlAreaStacked
    oChart.SetSourceData oSheet.Range(sAdd1 & ":" & sAdd2), PlotBy:=xlRows
    oChart.Location Where:=xlLocationAsObject, Name:=oSheet.Name

    oXL.ActiveChart.HasTitle = True
    oXL.ActiveChart.ChartTitle.Text = sTitle
End Sub
Question by:uncle_med
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

Expert Comment

ID: 8126727
could you put the charts on a new spreadsheet and there is an arrange all command which can automatically arrange them

Private Sub Form_Click ()
   Dim Cmd, I, Q, Row, Z   ' Declare variables.
   Q = Chr(34)   ' Define quotation marks.
   ' Create a string containing Microsoft Excel macro commands.
   Cmd = "[ACTIVATE(" & Q &"SHEET1" & Q & ")]"
   Cmd = Cmd & "[SELECT(" & Q & "R1C1:R5C2" & Q & ")]"
   Cmd = Cmd & "[NEW(2,1)][ARRANGE.ALL()]"
   If Text1.LinkMode = vbNone Then
      Z = Shell("Excel", 4)   ' Start Microsoft Excel.
      Text1.LinkTopic = "Excel|Sheet1"   ' Set link topic.
      Text1.LinkItem = "R1C1"   ' Set link item.
      Text1.LinkMode = vbLinkManual   ' Set link mode.
   End If
   For I = 1 To 5
      Row = I   ' Define row number.
      Text1.LinkItem = "R" & Row & "C1"   ' Set link item.
      Text1.Text = Chr(64 + I)   ' Put value in Text.
      Text1.LinkPoke   ' Poke value to cell.
      Text1.LinkItem = "R" & Row & "C2"   ' Set link item.
      Text1.Text = Row   ' Put value in Text.
      Text1.LinkPoke   ' Poke value to cell.
   Next I
   On Error Resume Next
   Text1.LinkExecute Cmd   ' Carry out Microsoft Excel commands.
   MsgBox "LinkExecute DDE demo with Microsoft Excel finished.", 64
End Sub

Although this is using DDE, the macro held in CMD variable should work if you can call it. It might help you.
LVL 26

Accepted Solution

EDDYKT earned 200 total points
ID: 8127405
Try this

To move chart

ActiveSheet.Shapes("Chart 1").IncrementLeft 408#
ActiveSheet.Shapes("Chart 1").IncrementTop 18#

Author Comment

ID: 8127801
Thanks, that's Exactly what I was looking for.

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month15 days, 10 hours left to enroll

743 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