我正在尝试创建一个按钮,允许我附加Word / Excel / PDF文件 . 我已经阅读了一些其他文章和教程,所以我现在有了一个按钮和一个VBA宏,它给了我一个对话框来浏览我选择的文件 . 该文件可以选择并嵌入Excel中 .
我遇到的问题是将嵌入文件的位置放在我创建的按钮旁边 . 目前它始终默认为活动工作表的左上角,尽管我尽最大努力硬编码不同的位置 .
所以有两个问题:
-
如何设置OLEObject的位置?
-
有没有办法可以识别命令按钮的单元格引用/位置,然后设置OLEObject相对于它的位置?例如,按钮右侧的两列 .
谢谢JK
到目前为止,这是我的代码:
Sub AttachFile()
'Identify the cell the command button is in and set the location for attachment icon to be 3 columns to the right
Dim buttonName As String
Dim buttonAddress As String
Dim buttonLocation As Range
Dim iconLocation As Range
buttonName = ActiveSheet.Shapes(Application.Caller).Name
buttonAddress = ActiveSheet.Shapes(buttonName).TopLeftCell.Address
Set iconLocation = Range(buttonAddress).Offset(0, 3)
'Browse for the file
Dim vFile As Variant
vFile = Application.GetOpenFilename("All Files,*.*", Title:="Find file to insert")
If LCase(vFile) = "false" Then Exit Sub
'Embed the selected file
Dim attachment As OLEObject
Set attachment = ActiveSheet.OLEObjects.Add( _
Filename:=vFile, _
Link:=False, _
DisplayAsIcon:=True)
'Reposition the icon to be next to the command button
ActiveWindow.Zoom = 100
With attachment
.Top = iconLocation.Top
.Left = iconLocation.Left
End With
ActiveWindow.Zoom = 70
End Sub
1 回答
我认为你有正确的方法 . 尝试将代码更改为这样的代码
我目前不在VBA附近,但它就是这样的