I am interested in copying the curve from the excel scatter with smooth lines only to MS Visio using VBA code. I have tried selecting the curve from a excel XYScatterSmoothNoMarkers graph, but not able to just copy and paste just the curve, but the complete chart with the curve only.
I have written some VBA code (taking pieces from various articles), to generate the chart and to make only the curve visible and copy this. This will work for me, I am able to insert it into Visio, but this method gives me two issues to resolve. If I am unable to just copy the curve itself, but need to copy the chart, then I need to resolve the following 2 issues:
1) I need the graph is have not distortion from having different scaling for the x and y coordinates. That is if I move on x axes 1 unit, that must be exactly the same as moving on y axes 1 unit. The x and y 1 unit must be the same or I have a distortion of the plot. I haven't found a way to control this with VBA, and the closes thing is manually doing.
2) The last issue is to located the start of the graph or curve and for me to adjust the placement of the chart to a certain location of shapes on my Visio drawing. The shape is a circle and I just need the curve touching the bottom part of the circle. If a solution can be found to do this with VBA code too, then copying the chart will work for me. I do have the (x,y) location of the connection location I want.
Below if a sample of some VBA Code that I am able to generate the curve and making the chart invisible when pasted into Visio. But, the 2 issues need to be resolve unless I am able to copy the curve itself and determine the end points. Then I can write VBA Code to connect to my Visio drawing.
Here is my sample VBA Code:
Dim rng As Range
Dim cht As ChartObject
' Your data range for the chart
Set rng = ActiveSheet.Range("B31:C65")
' Create a chart
Set cht = ActiveSheet.ChartObjects.Add(Left:=ActiveCell.Left, Width:=450, Top:=ActiveCell.Top, Height:=250)
' Determine the chart type
cht.Chart.ChartType = xlXYScatterSmoothNoMarkers
' Give chart some data
cht.Chart.SetSourceData Source:=rng
' Adjust y-axis Scale
cht.Chart.Axes(xlValue).MinimumScale = 36.63
cht.Chart.Axes(xlValue).MaximumScale = 49.06
' Adjust x-axis Scale
cht.Chart.Axes(xlCategory).MinimumScale = 33.46
cht.Chart.Axes(xlCategory).MaximumScale = 40.58
' Remove Gridlines
cht.Chart.Axes(xlValue).MajorGridlines.Delete
cht.Chart.Axes(xlValue).MinorGridlines.Delete
' Remove X-Axis
cht.Chart.Axes(xlCategory).Delete
' Remove Y-Axis
cht.Chart.Axes(xlValue).Delete
' Remove Legend
cht.Chart.Legend.Delete
' Remove ChartArea border
cht.Chart.ChartArea.Border.LineStyle = xlNone
' No background color fill
cht.Chart.ChartArea.Format.Fill.Visible = msoFalse
cht.Chart.PlotArea.Format.Fill.Visible = msoFalse
I would appreciate any suggestion. I was referred to this site from MS community.
Thank You,
