在Excel 2007中将工作表复制到另一个工作簿时，插入的图像无法显示 [英] Inserted image fails to display when sheet is copied to another workbook in Excel 2007

问题描述

其中一个复制的表格包含两个插入的.PNG图像，但这些图像一旦被复制到工作簿A，就无法在工作簿上显示。

在我添加网络文件夹工作簿B后，中心设置并勾选高级选项下的剪切，复制，父母单元格排序选项，我可以看到带有错误消息的图像轮廓

图像可以显示..可能没有足够的内存...或图像已损坏..

在复制片。

我怀疑错误是否正确，因为如果手动复制该图表，图像会成功显示。

我记录了一个执行此操作的宏，并将代码插入到宏中，但是当我运行它时会收到上述错误，这表明VBA是罪魁祸首。

我还解压缩工作簿一个xlsx文件，以确认两个图像都存储在xlsx文件中，而不是从其他地方导入。

我认为编写代码以显式地复制和粘贴图像，但是在VBA中看不到任何方式，我可以在目标表上编辑确切的位置，我想要粘贴图像。

我在XP上运行Excel 2007

任何想法？

解决方案

我无法解决复制图像无法显示的问题（因为发布后我发现是否正确显示或生成错误消息似乎是随机发生的），但是我已经找出了一个可行的解决方法是删除复制的表单上的图像容器，然后从文件中插入徽标，并将它们放在工作表上。

我修改了VBA代码，我发现： http://www.exceltip.com/st/Insert_pictures_using_VBA_in_Microsoft_Excel/486.html
如下：

 函数InsertImageInRange（Image1_Filepath As String，Image2_Filepath As String，TargetSheet As String，TargetCell1 As Range，TargetCell2 As Range）
'插入图片并调整大小以适应TargetCells范围
'此解决方法从文件中删除图像容器并以原始徽标复制。 
 
 Dim dblTop As Double，dblLeft As Double，dblWidth As Double，dblHeight As Double 
 Dim objImage As Object 
 
表格（TargetSheet）。选择
 '检查图像是否有效
 bUnexpectedImage = True 
对于每个img在ActiveSheet.Shapes 
如果img.Name =图片1或img.Name =图22然后
 img.Delete 
 Else 
 bUnexpectedImage = False 
结束如果
下一个
如果bUnexpectedImage = False然后MsgBox（意外的图像找到）
 
如果TypeName（ActiveSheet）<> 工作表然后退出函数
如果Dir（Image1）=然后退出函数
 
'插入第一个徽标
设置objImage = ActiveSheet.Pictures.Insert（Image1）
'确定位置
与TargetCell1 
 dblTop = .Top 
 dblLeft = .Left 
 dblWidth = .Offset（0，.Columns.Count）.Left  -  .Left 
 dblHeight = .Offset（.Rows.Count，0）.Top  -  .Top 
 End With 
'Position&大小图像
带objImage 
 .Top = dblTop 
 .Left = dblLeft + 13 
 .Width = dblWidth + 25 
 .Height = dblHeight + 15 
结束
设置objImage =没有
 
'插入第二个标志，如上所述... 
结束函数
  

In workbook A I have a macro that opens read-only workbook B, copies 4 sheets into workbook A, then closes workbook B.

One of the copied sheets contains two inserted .PNG images but these images fail to display on the sheet once it's copied over to workbook A.

After I added the network folder workbook B resides in to the Trust Center settings and ticked the 'Cut, copy, sort with parent cells' option under Advanced options, I could see the image outlines with the error message

"The image canot be displayed.. may not have enough memory..or image is corrupted.."

on the copied sheet.

I doubt either error is correct because if I manually copy the sheet over, the images display successfully.

I recorded a macro doing this and inserted the code into the macro but just get the above error when I run it, which suggests VBA is the culprit.

I also unzipped the workbook A xlsx file to confirm both images are stored in the xlsx file and not imported from elsewhere.

I considered writing code to explicitly copy and paste the images but can't see any way in VBA that I can code the exact locations on the target sheet I want the images pasted.

I am running Excel 2007 on XP.

Any ideas?

解决方案

I have been unable to resolve the problem of copied images not displaying (and since posting I have found that whether they display correctly or generate an error message seems to occur randomly), however I have figured out a viable workaround that deletes the image containers on the copied sheet then inserts the logos from file, and positions them on the sheet.

I modified VBA code I found at: http://www.exceltip.com/st/Insert_pictures_using_VBA_in_Microsoft_Excel/486.html as follows:

Function InsertImageInRange(Image1_Filepath As String, Image2_Filepath As String, TargetSheet As String, TargetCell1 As Range, TargetCell2 As Range)
    ' Insert a picture(s) and resize to fit the TargetCells range
    ' This workaround deletes the image containers and copies in the original logos from file.

    Dim dblTop As Double, dblLeft As Double, dblWidth As Double, dblHeight As Double   
    Dim objImage As Object         

    Sheets(TargetSheet).Select  
    ' Check that images are valid
    bUnexpectedImage = True
    For Each img In ActiveSheet.Shapes
        If img.Name = "Picture 1" Or img.Name = "Picture 22" Then
            img.Delete
        Else
            bUnexpectedImage = False
        End If
    Next
    If bUnexpectedImage = False Then MsgBox ("Unexpected images found.")

    If TypeName(ActiveSheet) <> "Worksheet" Then Exit Function
    If Dir(Image1) = "" Then Exit Function

    ' Insert first logo
    Set objImage = ActiveSheet.Pictures.Insert(Image1)
    ' Determine positions
    With TargetCell1
        dblTop = .Top
        dblLeft = .Left
        dblWidth = .Offset(0, .Columns.Count).Left - .Left
        dblHeight = .Offset(.Rows.Count, 0).Top - .Top
    End With
    ' Position  & size image
    With objImage
        .Top = dblTop
        .Left = dblLeft + 13
        .Width = dblWidth + 25
        .Height = dblHeight + 15
    End With
    Set objImage = Nothing

    ' Insert second logo, as above...    
End Function

这篇关于在Excel 2007中将工作表复制到另一个工作簿时，插入的图像无法显示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！