一个看似简单的问题.但是设置ChartObject的宽度和高度或它的Shape不会这样做,它们似乎只是该白色矩形的一些内部部分,即嵌入图表. ChartArea似乎也不是我感兴趣的对象.
我想要的是在下面的例子中导出的文件的尺寸为800×600(我并不意味着重新缩放导出的图像或试验和错误,直到大小意外地适合).图表周围一定有一些我忽略的对象.
Sub mwe()
Dim filepath As String
Dim sheet As Worksheet
Dim cObj As ChartObject
Dim c As Chart
Dim cShape As Shape
Dim cArea As chartArea
filepath = Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, "\"))
Set sheet = ActiveSheet
Set cObj = sheet.ChartObjects(1)
Set c = cObj.chart
Set cShape = sheet.Shapes(cObj.Name)
Set cArea = c.chartArea
cObj.Width = 800
cObj.Height = 400
MsgBox cArea.Width & " x " & cArea.Height '793x393
c.Export filepath & "test1.png" '1067x534, this is also the size on screen
cShape.Width = 800
cShape.Height = 400
MsgBox cArea.Width & " x " & cArea.Height '794x393
c.Export filepath & "test2.png" '1068x534, this is also the size on screen
End Sub
更新:
事实证明,ChartObject和属于同一图表的Shape已经将工作表作为父级.但宽度和高度没有像我假设的那样以像素为单位指定,而是以点为单位,其中1点= 1/72英寸.
在大多数情况下,Windows似乎假设每英寸96像素.
感谢史蒂夫的评论,我现在正在使用以下内容,这是非常可靠的.在导出时未激活的ChartObject似乎生成宽度和高度比应该高1的文件.
它仍然是如何自动确定如何确定因子px2ptH和px2ptV.
Sub mwe()
Dim filepath As String
Dim sheet As Worksheet
Dim cObj As ChartObject
Dim c As Chart
Dim px2ptH As Double: px2ptH = 72 / 96
Dim px2ptV As Double: px2ptV = 72 / 96
Dim w As Double: w = 800 * px2ptH
Dim h As Double: h = 400 * px2ptV
filepath = Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, "\"))
Set sheet = ActiveSheet
Set cObj = sheet.ChartObjects(1)
Set c = cObj.Chart
'otherwise image size may deviate by 1x1
cObj.Activate
cObj.Width = w
cObj.Height = h
c.Export filepath & "test.png"
End Sub