Excel VBA保存屏幕截图 [英] Excel VBA save screenshot

问题描述

我尝试使用VBA代码在Excel中拍摄工作表的屏幕截图，然后将其保存在指定的路径中，但是我无法正确保存它...

I try to take a screenshot of a Worksheet in Excel with VBA code and then to save it in a specified path, but I do not manage to save it properly...

Sub My_Macro(Test, Path)
  Dim sSheetName As String
  Dim oRangeToCopy As Range
  Dim FirstCell As Range, LastCell As Range

  Worksheets(Test).Activate
  Set LastCell = Cells(Cells.Find(What:="*", SearchOrder:=xlRows, _
      SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
      Cells.Find(What:="*", SearchOrder:=xlByColumns, _
      SearchDirection:=xlPrevious, LookIn:=xlValues).Column)
  Set FirstCell = Cells(Cells.Find(What:="*", After:=LastCell, SearchOrder:=xlRows, _
      SearchDirection:=xlNext, LookIn:=xlValues).Row, _
      Cells.Find(What:="*", After:=LastCell, SearchOrder:=xlByColumns, _
      SearchDirection:=xlNext, LookIn:=xlValues).Column)

  sSheetName = Test ' worksheet to work on

  With Worksheets(sSheetName)
      .Range(FirstCell, LastCell).CopyPicture xlScreen, xlPicture
      .Export Filename:=Path + Test + ".jpg", Filtername:="JPG"
  End With

End Sub

Excel不想在截屏后直接执行方法.Export.... 因此，我尝试将图片粘贴到新图表中. Excel将图表图片保存在正确的位置，并在我的图片上放置了图表...我也尝试将其粘贴到临时工作表中，但是Excel不想导出它...

Excel doesn't want to execute the method .Export... directly after taking the screenshot. So I tried to paste the picture in a new chart. Excel save the chart picture at the right place with a chart on my picture... I also tried to paste it in a temporary worksheet but Excel doesn't want to export it...

任何想法

推荐答案

LubošSuk曾经有过这个想法.

Was busy with the the idea Luboš Suk had.

只需更改图表的大小即可.参见下面的脚本.

Just change the size of the Chart. See script below.

Sub My_Macro(Test, Path)


 Test = "UNIT 31"
    Dim sSheetName As String
    Dim oRangeToCopy As Range
    Dim FirstCell As Range, LastCell As Range

    Worksheets(Test).Activate

    Set LastCell = Cells(Cells.Find(What:="*", SearchOrder:=xlRows, _
        SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
        Cells.Find(What:="*", SearchOrder:=xlByColumns, _
        SearchDirection:=xlPrevious, LookIn:=xlValues).Column)

    Set FirstCell = Cells(Cells.Find(What:="*", After:=LastCell, SearchOrder:=xlRows, _
        SearchDirection:=xlNext, LookIn:=xlValues).Row, _
        Cells.Find(What:="*", After:=LastCell, SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, LookIn:=xlValues).Column)

    sSheetName = Test ' worksheet to work on

    With Worksheets(sSheetName).Range(FirstCell, LastCell)

        .CopyPicture xlScreen, xlPicture
        'Getting the Range height
        PicHeight = .Height
        'Getting the Range Width
        PicWidth = .Width

        ''.Export Filename:=Path + Test + ".jpg", Filtername:="JPG"   'REMOVE THIS LINE


    End With


    With Worksheets(sSheetName)

        'Creating the Chart
        .ChartObjects.Add(30, 44, PicWidth, PicHeight).Name = "TempChart"

        With .ChartObjects("TempChart")

            'Pasting the Image
            .Chart.Paste
            'Exporting the Chart
            .Chart.Export Filename:=Path + Test + ".jpg", Filtername:="JPG"

        End With

        .ChartObjects("TempChart").Delete

    End With

End Sub

这篇关于Excel VBA保存屏幕截图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！