Excel 2007 VBA复制范围作为图片并导出到JPG文件截断图片 [英] Excel 2007 VBA copy range as picture and export to JPG file truncates the picture

问题描述

我有一个包含77行或更多行的许多不同数据范围的工作簿。我需要复制它们并保存为JPG文件，这些文件将由其他应用程序使用。

I have a workbook with many different ranges of data with 77 rows or more. I need to copy them and save as JPG files, which will be used by other application.

下面是我正在使用的代码示例。它适用于最多68行的范围，但对于超过该范围的范围，文件显示该范围的高度约为1360像素，其余部分（底部）为白色。

Below is the example of code that I'm using for that. It works fine for ranges with up to 68 rows, but for ranges with more than that, the file shows up to around 1360 pixels height of the range and the rest of it (the bottom part) is white.

Sub Create_jpg（）

Dim MyPath As String

Dim rgExp As Range

  

    MyPath = ThisWorkbook.Path& " \ ScorecardJPEGs \"

      

   表格（"LocalMetrics"）。选择

   

   设置rgExp =范围（"A1：AL77"）

   

    rgExp.CopyPicture外观：= xlScreen，格式：= xlBitmap

     

   使用ActiveSheet.ChartObjects.Add（左：= rgExp.Left，Top：= rgExp.Top，_
$
                                       宽度：=（rgExp.Width - 10），高度：=（rgExp.Height
- 5））

         .Name =" ChartTempEXPORT"

        .Activate

   结束时与
  

    ActiveChart.Paste

    ActiveSheet.ChartObjects（" ChartTempEXPORT"）。Chart.Export FileName：= MyPath& " Scorecard.jpg"，_

                                                              
Filtername：=" jpg"

    ActiveSheet.ChartObjects（" ChartTempEXPORT"）。删除

       

End Sub

Sub Create_jpg()
Dim MyPath As String
Dim rgExp As Range
  
    MyPath = ThisWorkbook.Path & "\ScorecardJPEGs\"
      
    Sheets("LocalMetrics").Select
   
    Set rgExp = Range("A1:AL77")
   
    rgExp.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
     
    With ActiveSheet.ChartObjects.Add(Left:=rgExp.Left, Top:=rgExp.Top, _
                                      Width:=(rgExp.Width - 10), Height:=(rgExp.Height - 5))
        .Name = "ChartTempEXPORT"
        .Activate
    End With
  
    ActiveChart.Paste
    ActiveSheet.ChartObjects("ChartTempEXPORT").Chart.Export FileName:=MyPath & "Scorecard.jpg", _
                                                             Filtername:="jpg"
    ActiveSheet.ChartObjects("ChartTempEXPORT").Delete
       
End Sub

我确认图表和粘贴的创建看起来很好，因为我删除了代码行这会删除最后的图表，显然图表上的图片还可以。但是当文件被创建时，图片的底部就消失了，
并且那里有一个空格。

这种情况​​发生在有很多行的所有范围内。

I verified that the creation of the chart and the paste looks fine, as I removed the line of code that deletes the chart at the end and apparently the picture on the chart was ok. But when the file is created, the bottom part of the picture simply vanished, and there is a white space there.
This happened to all ranges with many rows.

推荐答案

>>但对于超过此范围的范围，文件显示为该范围的1360像素高度及其余部分（底部）为白色。<<<

>>but for ranges with more than that, the file shows up to around 1360 pixels height of the range and the rest of it (the bottom part) is white.<<

您是否介意上传图形以使其清晰？

Would you mind uploading a figure to make it clear?

我在Excel 2013中测试了这个宏，它对我来说很好。

I have tested the macro in Excel 2013, it works fine for me.

由于我的Office 2007环境已损坏，我将花一些时间来修复它。

Since my Office 2007 environment is broken, I will take some time to repair it.

可能会有一些延迟。我将在Excel 2007中测试宏并给你反馈。

There may be some delay. I will tested the macro in Excel 2007 later and give you feedback.

感谢您的理解。

这篇关于Excel 2007 VBA复制范围作为图片并导出到JPG文件截断图片的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！