为什么我的excel图表不去一个新的VBA工作簿

Question

我有一个excel工作簿，其中有值。我采取这些值，并使用VBA创建一个图表。然后将图表保存到与值相同的excel工作表中的工作表中。我需要图表移动到不同的工作簿。我做错了什么?

下面的代码:

Sub createColumnBarChart()


'declare object variables to hold references to worksheet, source data cell range, created bar chart, and destination cell range
Dim myWorksheet As Worksheet
Dim mySourceData As Range
Dim myChart As Chart
Dim myChartDestination As Range

'Variable declaration
Dim sWorkbook As Workbook
'Create New Workbook
Set sWorkbook = Workbooks.Add


sWorkbook.SaveAs Filename:="C:UsersusernameDesktopmyFolderTest.xlsx"

'identify worksheet containing source data and created bar chart
'    Set myWorksheet = ThisWorkbook.Worksheets("ChartData")
Set myWorkbook = Workbooks("myFile.xlsx").Worksheets("ChartData")

With myWorkbook

'identify source data
Set mySourceData = .Range("A1:AA26")

'identify chart location
'        Set myChartDestination = .Range("A5:M24")
Set myChartDestination = Workbooks("Test.xlsx").Worksheets("Sheet1").Range("A4:M24")
'
'create bar chart
Set myChart = .Shapes.AddChart(XlChartType:=xlColumnStacked, Left:=myChartDestination.Cells(1).Left, Top:=myChartDestination.Cells(1).Top, Width:=myChartDestination.Width, Height:=myChartDestination.Height).Chart
Charts.Add


End With

'set source data for created bar chart
myChart.SetSourceData Source:=mySourceData
End Sub

Answer 1

不清楚是否要复制带有工作表(及其数据)的图表，或者只是复制"图表"。(这只是一张图片)。如果您复制Chart对象本身，它将链接回源工作簿中的原始数据(我猜这不是您想要的)。

您的原始代码创建一个图表(.Shapes.AddChart)，然后创建第二个图表(Charts.Add)，由于您创建新工作簿的方式可能是当前活动的工作簿。另外，Chart.Add创建了一个空的图表——这就是你所看到的。

下面的示例在与数据相同的工作表上创建图表，然后展示了如何复制或移动图表(和数据)的两个示例。

Option Explicit
Sub TestMove()
Dim myChart As Chart
Set myChart = CreateChart

Dim newWB As Workbook
Set newWB = Application.Workbooks.Add

Dim chartWS As Worksheet
Set chartWS = myChart.Parent.Parent
chartWS.Move Before:=newWB.Sheets(1)
'newWB.SaveAs Filename:="C:UsersusernameDesktopmyFolderTest.xlsx"
End Sub
Sub TestCopyPic()
Dim myChart As Chart
Set myChart = CreateChart

Dim newWB As Workbook
Set newWB = Application.Workbooks.Add

myChart.CopyPicture
newWB.Sheets(1).Paste
'newWB.SaveAs Filename:="C:UsersusernameDesktopmyFolderTest.xlsx"
End Sub
Function CreateChart() As Chart
Dim dataWS As Worksheet
Dim theData As Range
Set dataWS = ThisWorkbook.Sheets("ChartData")
Set theData = dataWS.Range("A1:AA26")

Dim myChartDestination As Range
Set myChartDestination = dataWS.Range("A4:M24")

Dim theChart As Chart
With myChartDestination
Set theChart = dataWS.Shapes.AddChart2(Left:=.Cells(1).Left, _
Top:=.Cells(1).Top, _
Width:=.Width, _
Height:=.Height).Chart
End With

With theChart
.ChartType = xlColumnStacked
.SetSourceData Source:=theData
End With

Set CreateChart = theChart
End Function