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

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 163
  • Last Modified:

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

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
1 Solution
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.
Try this

To move chart

ActiveSheet.Shapes("Chart 1").IncrementLeft 408#
ActiveSheet.Shapes("Chart 1").IncrementTop 18#
uncle_medAuthor Commented:
Thanks, that's Exactly what I was looking for.

Featured Post

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!

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