使用 Excel 2016 将范围保存为图片

该代码的目的是将一系列单元格保存为桌面上的图片。

该文件已创建，但不包含任何单元格数据，它是具有范围相对大小的空白图像。

该问题出现在 Office 2016 中。在 2013 中有效。

Sub SendSnapshot2()

    Dim strRng As Range
    Dim strPath As String
    Dim strFile As String
    Dim Cht As Chart

    Set strRng = ActiveWorkbook.Sheets("Snapshot").Range("A2:Q31")
    strPath = CreateObject("WScript.Shell").specialfolders("Desktop")
    strFile = "HeartBeat Snapshot - " & Format(Now(), "yyyy.mm.dd.Hh.Nn") & ".png"

    strRng.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
    'strRng.CopyPicture Appearance:=xlScreen, Format:=xlPicture
    'strRng.CopyPicture xlScreen, xlBitmap

    Application.DisplayAlerts = False
    Set Cht = Charts.Add
    With Cht
        .Paste
        '.Export Filename:=strFile, Filtername:="JPG"
        .Export Filename:="C:\downloads\SavedRange.jpg", Filtername:="JPG"
        '.Delete
    End With

End Sub

感谢@Axel Richter 给我指出了这个帖子：Link

成功的代码如下所示：

' convert snapshot to picture
strRng.CopyPicture xlScreen, xlPicture
lWidth = strRng.Width
lHeight = strRng.Height

Set Cht = ActiveSheet.ChartObjects.Add(Left:=0, Top:=0, Width:=lWidth, Height:=lHeight)
Cht.Activate
With Cht.Chart
  .Paste
  .Export Filename:=strPath & "\" & strFile, Filtername:="JPG"
End With

Cht.Delete