excel形状

excel形状

关注
发信
关注(28)粉丝(399)

vba excel形状

本文介绍了vba excel形状的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

  ActiveSheet.Pictures.Insert（URL）我使用一个小的子例程将图片插入到表格中。选择

Excel 2003（Windows）工作正常，但不适用于Excel 2011（Mac ）另外，



因此，我修改了我的子程序
（像建议的），
，但子例程停止在

  theShape.Fill.UserPicture URL

与错误信息



- 2147024894（80070002）Fehler der Methode UserPicture des Objekts FillFormat



矩形是绿色的

  Sub Q1（）
 
 Dim wks As Worksheet 
 Dim URL As String 
 Dim i As Long 
 Dim lastRow As Long 
 Dim theShape As Shape 
 Dim pasteCell As Range 
 
'Used Worksheet 
设置wks = Worksh eets（Blatt1）
 
'删除已存在的形状
对于每个sShape在wks.Shapes 
 theShape.Delete 
下一步theShape 
 
'检查列K中的所有现有行
 lastRow = Cells（Rows.Count，K）。End（xlUp）.Row 
 For i = 2 To lastRow 
 
 '这些URL已经被计算并存储在列K 
 URL = wks.Range（K& i）.Value 
 
'尝试将图像放在列L 
设置pasteCell = wks.Range（L& i）
 pasteCell.Select 
 
'创建一个形状，将图像放入
'ActiveSheet.Pictures.Insert（URL）.Select已弃用，不再工作！ 
设置Shape = wks.Shapes.AddShape（msoShapeRectangle，pasteCell.Left，pasteCell.Top，200,200）
 
'绿化后填充图像的形状
 theShape。 Fill.BackColor.RGB = RGB（0，255，0）
 theShape.Fill.UserPicture URL 
 
下一个i 
 
 End Sub

任何建议或提示？可能我是盲人的蝙蝠....

解决方案

你是否尝试过这样的语法来设置一个形状到一个URL：

  Sub Picadder（）
 
 Dim Pic As Shape 
 
 Set Pic = ActiveSheet.Shapes.AddPicture（http://stackoverflow.com/content/stackoverflow/img/apple-touch-icon.png，msoFalse，msoTrue，0，0，100，100）
 
 End Sub

此代码适应您的努力时，可能会沿着这行的一行：

  Sub Q1（）
 
 Dim wks As Worksheet 
 Dim URL As String 
 Dim i As Long 
 Dim lastRow As Long 
 Dim theShape As Shape 
 Dim pasteCell As Range 
 
'Used Worksheet 
 Set wks =工作表（Blatt1）
 
'删除已存在的形状
对于每个sShape在wks.Shapes 
 theShape.Delete 
下一步theShape 
 
'检查列K中的所有现有行
 lastRow =单元格（Rows.Cou nt，K）。End（xlUp）.Row 
 For i = 2 To lastRow 
 
'这些URL已经被计算并存储在列K 
 URL = wks中。范围（K& i）.Value 
 
'尝试将图像放在列L 
设置pasteCell = wks.Range（L& i）
 pasteCell.Select 
 
'创建一个形状，将图像放入
'ActiveSheet.Pictures.Insert（URL）.Select已弃用，不再工作！ 
设置Shape = wks.Shapes.AddPicture（URL，pasteCell.Left，pasteCell.Top，200,200）
 
'设置形状图像backcolor。 
 theShape.Fill.BackColor.RGB = RGB（0，255，0）
 
下一个i 
 
 End Sub

您的网址需要正确格式化 - 我不得不在我的网址上使用引号将初始代码段有效地实现，但可能是一个办法。



对于Mac-Excel 2011，有一个解决方法由他的博客。显然，在Mac-Excel 2011中将图像绑定到单元格并不容易，如果有的话。此外，研究表明，将图像插入到excel工作簿中的问题已被多次询问。在研究中，似乎还没有通过图片方法来解决这个问题。因此，解决方案可能是最好的解决方案。



从迈克尔博客中非常适应和移植的代码片段如下：

 函数InsertImageCommentAsWorkAround（title As String，cellAddress As Range）
 
'定义注释中使用的变量。 
 Dim ImageCommentContainer作为注释
 
'添加新的注释之前，清除任何现有的注释。 
 Application.ActiveCell.ClearComments 
 
'将注释定义为局部变量，并将_ 
'_ cellAddress中的文件名作为单元格注释的输入参数分配在它的cellAddress。 
 
'添加评论。 
设置ImageCommentContainer = Application.ActiveCell.AddComment 
 
'使用注释，设置参数。 
使用ImageCommentContainer 
 .Text文本：=
 
'覆盖该注释的形状设置参数。 
与.Shape 
 .Fill.UserPicture（cellAddress.Value）
 .ScaleHeight 3＃，msoFalse，msoScaleFormTopLeft 
 .ScaleWidth 2.4，msoFalse，msoScaleFromTopLeft 
结束与
 
结束
 
 InsertImageCommentAsWorkAround = title 
 
结束功能

我建议将注释集合适用于您的循环，并使用它将图像设置到位，使用循环中的形状格式设置由适配代码生成的注释形状的格式。


I've used a small subroutine to insert a picture into my sheet by

ActiveSheet.Pictures.Insert(URL).Select

This works fine with Excel 2003 (Windows), but does not work with Excel 2011 (Mac) any more.

Therefore I modified my subroutine(like proposed http://www.launchexcel.com/google-maps-excel-demo/),but the subroutine stops at 

theShape.Fill.UserPicture URL

with the error message

"-2147024894 (80070002) Fehler der Methode UserPicture des Objekts FillFormat"

The rectangle is green!

Sub Q1()

Dim wks As Worksheet
Dim URL As String
Dim i As Long
Dim lastRow As Long
Dim theShape As Shape
Dim pasteCell As Range

' Used Worksheet
Set wks = Worksheets("Blatt1")

' Delete already existing shapes
For Each theShape In wks.Shapes
        theShape.Delete
Next theShape

' Check all existing rows in Column K
lastRow = Cells(Rows.Count, "K").End(xlUp).Row
For i = 2 To lastRow

' the URLs are already computed and stored in column K
URL = wks.Range("K" & i).Value

' try to put the images in column L
Set pasteCell = wks.Range("L" & i)
pasteCell.Select

' Create a Shape for putting the Image into
' ActiveSheet.Pictures.Insert(URL).Select is deprecated and does not work any more!!!
Set theShape = wks.Shapes.AddShape(msoShapeRectangle, pasteCell.Left, pasteCell.Top, 200, 200)

' fill the shape with the image after greening
theShape.Fill.BackColor.RGB = RGB(0, 255, 0)
theShape.Fill.UserPicture URL

Next i

End Sub

Any suggestions or hints? Probably I'm blind as a bat....

解决方案

Have you tried syntax along the lines of this for setting a shape to a URL:

Sub Picadder()

Dim Pic As Shape

Set Pic = ActiveSheet.Shapes.AddPicture("http://stackoverflow.com/content/stackoverflow/img/apple-touch-icon.png", msoFalse, msoTrue, 0, 0, 100, 100)

End Sub

This code, when adapted to your efforts, might look something along the lines of this:

Sub Q1()

Dim wks As Worksheet
Dim URL As String
Dim i As Long
Dim lastRow As Long
Dim theShape As Shape
Dim pasteCell As Range

' Used Worksheet
Set wks = Worksheets("Blatt1")

' Delete already existing shapes
For Each theShape In wks.Shapes
        theShape.Delete
Next theShape

' Check all existing rows in Column K
lastRow = Cells(Rows.Count, "K").End(xlUp).Row
For i = 2 To lastRow

' the URLs are already computed and stored in column K
URL = wks.Range("K" & i).Value

' try to put the images in column L
Set pasteCell = wks.Range("L" & i)
pasteCell.Select

' Create a Shape for putting the Image into
' ActiveSheet.Pictures.Insert(URL).Select is deprecated and does not work any more!!!
Set theShape = wks.Shapes.AddPicture(URL, pasteCell.Left, pasteCell.Top, 200, 200)

' Set shape image backcolor.
theShape.Fill.BackColor.RGB = RGB(0, 255, 0)

Next i

End Sub

Your urls will need to be properly formatted - I had to use quotations on my URL for the initial snippet to get it function effectively, but it may be a solution.

For Mac-Excel 2011, there is a workaround discussed by Michael McLaughlin on his blog. Evidently, it is not easy to tie images to cells in Mac-Excel 2011, if at all. Moreover, research reveals that the question of inserting images into an excel workbook has been asked many times. It also appears that it has not been readily solved through picture methods thus far in the research. Thus, a work-around may be the best solution.

The code snippet, which was very closely adapted and ported from Michael's blog, is as follows:

Function InsertImageCommentAsWorkAround(title As String, cellAddress As Range)

    ' Define variables used in the comment.
    Dim ImageCommentContainer As comment

  ' Clear any existing comments before adding new ones.
  Application.ActiveCell.ClearComments

  ' Define the comment as a local variable and assign the file name from the _
  ' _ cellAddress as an input parameter to the comment of a cell at its cellAddress.

  ' Add a comment.
  Set ImageCommentContainer = Application.ActiveCell.AddComment

  ' With the comment, set parameters.
  With ImageCommentContainer
    .Text Text:=""

        'With the shape overlaying the comment, set parameters.
        With .Shape
          .Fill.UserPicture (cellAddress.Value)
          .ScaleHeight 3#, msoFalse, msoScaleFormTopLeft
          .ScaleWidth 2.4, msoFalse, msoScaleFromTopLeft
        End With

  End With

  InsertImageCommentAsWorkAround = title

End Function

I would advise adapting the comment sets into your loop, and use that to set your images into place, using the shape formatting in your loop to set the formatting of the comment shapes generated by the adapted code.

这篇关于vba excel形状的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

08-22 17:40
Powered by LMLPHP ©2024 excel形状 0.052046