如何使用图像填充Excel单元格？ [英] How can I populate an Excel cell with an image?

问题描述

代码简单：

 函数AddImage（路径As String，filename As String）
 Dim file As String 
 file = path +/+ filename +.png
 
 ActiveSheet.Range（A1）。Pictures.insert（file）。选择
结束功能
  

但这不行。当我在文件上设置手表时，我可以看到它包含一个有效路径到我的硬盘上的图像。

我需要做些什么来填充单元格图像？

解决方案

您不能将照片放在单元格中，只能将其放在上。所有图片浮动在工作表上。您可以通过将顶部和左侧属性设置到单元格的顶部和左侧来将图像放置在单元格上。

  Sub AddPicOverCell（path As String，filename As String，rngRangeForPicture As Range）
 With Application 
 Dim StartingScreenUpdateing As Boolean 
 Dim StartingEnabledEvent As Boolean 
 Dim StartingCalculations As XlCalculation 
 
 StartingScreenUpdateing = .ScreenUpdating 
 StartingEnabledEvent = .EnableEvents 
 StartingCalculations = .Calculation 
 
 .ScreenUpdating = False 
 .EnableEvents = False 
 .Calculation = xlCalculationManual 
 End with 
 
 Dim Top As Single，Left As Single，Height As单个，宽度为单个
 Dim file As String 
 Dim ws As Worksheet 
 
 file = path +/+ filename +.png
 
顶部= rngRangeForPicture.Top 
 Left = rngRangeForPicture.Left 
 Height = rngRangeForPicture.Height 
宽度= rngRangeForPicture.Width 
 
设置ws = rngRangeForPicture.Worksheet 
 
 ws.Shapes.AddPicture文件，msoCTrue，msoTrue，左，顶，宽，高
 
应用程序
 .ScreenUpdating = StartingScreenUpdateing 
 .EnableEvents = StartingEnabledEvent 
 .Calculation = StartingCalculations 
 End with 
 End Sub 
  

然后你会称之为：

  AddPicOverCellC：\，Pic，ActiveSheet.Range（A1）
  

注意： 这样可以将图像的位置和大小调整为与调用子页面时指定的单元格相同的大小和位置。这将会将图片插入您想要图片的单元格或范围。这也可以是像 B5：G25 之类的单元格范围，或者像我的例子中的单个单元格 Range（A1），图片将覆盖范围内的所有单元格。

I'm trying to insert an image into an excel worksheet.

The code is simply:

Function AddImage(path As String, filename As String)
    Dim file As String
    file = path + "/" + filename + ".png"

    ActiveSheet.Range("A1").Pictures.insert(file).Select
End Function

but this doesn't work. When I set a watch on file I can see that it contains a valid path to an image on my hard drive.

What do I need to do to populate a cell with an image?

解决方案

You cannot put pictures "in" a cell, only "over" it. All pictures "float" on the worksheet. You can position a picture over a cell by setting its Top and Left properties to the Top and Left of the cell.

Sub AddPicOverCell(path As String, filename As String, rngRangeForPicture As Range)
With Application
Dim StartingScreenUpdateing As Boolean
Dim StartingEnabledEvent As Boolean
Dim StartingCalculations As XlCalculation

StartingScreenUpdateing = .ScreenUpdating
StartingEnabledEvent = .EnableEvents
StartingCalculations = .Calculation

    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With

Dim Top As Single, Left As Single, Height As Single, Width As Single
Dim file As String
Dim ws As Worksheet

file = path + "/" + filename + ".png"

Top = rngRangeForPicture.Top
Left = rngRangeForPicture.Left
Height = rngRangeForPicture.Height
Width = rngRangeForPicture.Width

Set ws = rngRangeForPicture.Worksheet

ws.Shapes.AddPicture file, msoCTrue, msoTrue, Left, Top, Width, Height

With Application
    .ScreenUpdating = StartingScreenUpdateing
    .EnableEvents = StartingEnabledEvent
    .Calculation = StartingCalculations
End With
End Sub

And then you would call it like:

AddPicOverCell "C:\", "Pic", ActiveSheet.Range("A1")

NOTES: This will position and resize the image to the same size and position on the sheet as the Cell you specify when calling the sub. This will insert the picture over the cell OR range you want the picture in. This could also be a range of cells like B5:G25 or as in my example a single cell like Range("A1") and the picture will cover all cells in the range.

这篇关于如何使用图像填充Excel单元格？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！