Link to home
Start Free TrialLog in
Avatar of uncle_med
uncle_med

asked on

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.Activate
   
    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
Avatar of OblivionSY
OblivionSY

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
End Sub

Although this is using DDE, the macro held in CMD variable should work if you can call it. It might help you.
ASKER CERTIFIED SOLUTION
Avatar of EDDYKT
EDDYKT
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of uncle_med

ASKER

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