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).Add ress)
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
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).Add
Set oRange = oSheet.Cells()
Set oChart = oXL.ActiveWorkbook.Charts.
oChart.Activate
oChart.ChartType = xlAreaStacked
oChart.SetSourceData oSheet.Range(sAdd1 & ":" & sAdd2), PlotBy:=xlRows
oChart.Location Where:=xlLocationAsObject,
oXL.ActiveChart.HasTitle = True
oXL.ActiveChart.ChartTitle
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, that's Exactly what I was looking for.
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.