vba 代码中的 OLEObjects 错误 (Excel 2010)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25245327/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Error with OLEObjects in vba code (Excel 2010)
提问by RobK
For a document I'm using multiple databases (in seperate worksheets) and multiple dropdown menus (combo boxes) which are populated by those databases. On change it's adding some information and a picture to the main sheet, which is retrieved from those databases. All works well, but... Once I try to change a cell in one of the databases (doesn't matter which one), it's giving me an error with the dropdown boxes:
对于一个文档，我使用了多个数据库（在单独的工作表中）和由这些数据库填充的多个下拉菜单（组合框）。在更改时，它会将一些信息和图片添加到从这些数据库中检索的主表中。一切正常，但是...一旦我尝试更改其中一个数据库中的单元格（与哪个数据库无关），下拉框就会出现错误：
"Run-time error '1004': Method 'OLEObjects' of object '_worksheet' failed"
"Run-time error '1004': Method 'OLEObjects' of object '_worksheet' failed"
(Debug highlights on the line with.OLEObjects)
（调试高亮就行了.OLEObjects）
What am I overseeing here? Any help is much appreciated!
我在这里监督什么？任何帮助深表感谢！
Private Sub CDL1_change()
Dim c As Range, MyPath, MyFile As String, WS As Worksheet
MyPath = Sheets("TeamChart").Range("C95").Value
Set WS = ActiveSheet
With Worksheets("CDL").Range("rngCDL")
Set c = .Find(CDL1.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Sheets("TeamChart").Range("L5") = c.Offset(0, 0).Value
Sheets("TeamChart").Range("M6") = c.Offset(0, 1).Value
Sheets("TeamChart").Range("M7") = c.Offset(0, 2).Value
MyFile = c.Offset(0, 3).Value
With WS
.OLEObjects("picCDL").Object.Picture = LoadPicture(MyPath & MyFile)
End With
Else
Sheets("TeamChart").Range("L5") = "Not Found"
Sheets("TeamChart").Range("M6") = "Not Found"
Sheets("TeamChart").Range("M7") = "Not Found"
End If
End With
End Sub
This is cell C95's value: G:\xxx\Fotos\. I've already tried it with MyFileas being both the path and the file name, gave the same results.
这是单元格C95的值：G:\xxx\Fotos\。我已经尝试过将它MyFile作为路径和文件名，给出了相同的结果。
采纳答案by smagnan
I finally managed to get the same error (and many others by the way ^^) and to resolve it (and all the other error)
我终于设法得到相同的错误（顺便说一下还有许多其他错误 ^^）并解决了它（以及所有其他错误）
To help you solve your case I'm going to put the exampleI used:
为了帮助您解决您的问题，我将举出我使用的示例：
So This code works perfectly:
所以这段代码完美地工作：
Private Sub testOleobj()
Dim MyPath, MyFile As String, WS As Worksheet
MyPath = Sheets("testSh1").Range("C95").Value ' A path equivalent to G:\xxx\Fotos\ for me
Set WS = Sheets("testSh1") ' Here is the change
MyFile = "test.jpg"
With WS
.OLEObjects("picCDL").Object.Picture = LoadPicture(MyPath & MyFile)
End With
End Sub
But this code does notwork (on purpose):
但是此代码不起作用（故意）：
Note: I execute this code from, let's say a sheet called "sheet1"
注意：我从一个名为“sheet1”的工作表中执行此代码
Private Sub testOleobj()
Dim MyPath, MyFile As String, WS As Worksheet
MyPath = Sheets("testSh1").Range("C95").Value ' A path equivalent to G:\xxx\Fotos\ for me
Set WS = ActiveSheet
MyFile = "test.jpg"
With WS
.OLEObjects("picCDL").Object.Picture = LoadPicture(MyPath & MyFile)
End With
End Sub
I am sure the path and the image are both valid because I they are not I will get an other error (Error 481-> here Invalid image) BUT
我确信路径和图像都是有效的，因为我它们不是我会收到其他错误（Error 481-> 此处无效图像）但是
I get "Run-time error '1004': Method 'OLEObjects' of object '_worksheet' failed"and .OLEObjects("picCDL").Object.Picture = LoadPicture(MyPath & MyFile)is the line which causes the error ... why?
我得到"Run-time error '1004': Method 'OLEObjects' of object '_worksheet' failed"并且.OLEObjects("picCDL").Object.Picture = LoadPicture(MyPath & MyFile)是导致错误的行......为什么？
Because the picture will be loaded in picCDLwhich is for me in sheet "testSh1"but I try to find it in ActiveSheetwhich corresponds to ... "sheet1"
因为图片将被加载到picCDL我在“testSh1”表中，但我尝试在ActiveSheet中找到它对应于 ... “sheet1”
To conclude:
总结：
I think it's the same for you (but I would have needed more of you workboot info to be sure): You use Set WS = ActiveSheetbut my theoryis .OLEObjects("picCDL").Object.Picturedoes not actually exist on this sheet (the active sheet actually, that's why it fails) but is on an other sheet so you should not use ActiveSheetbut the direct reference like in my second example.
我认为这是对你同样的（但我会需要更多的你workboot信息，以确保）：您可以使用Set WS = ActiveSheet，但我的理论是.OLEObjects("picCDL").Object.Picture实际上并不存在这个表（活动板实际上，这就是为什么它失败），但在另一个工作表，所以你不应该使用ActiveSheet直接引用，就像我的第二个例子一样。
I hope this will resolve your issue, If notplease add more information about your workbook, the worksheets you use etc ...
我希望这能解决您的问题，如果没有，请添加有关您的工作簿、您使用的工作表等的更多信息...
Last note: Why use With WSsince you just set one property? (I used it too to match you code as much as I can but it has no use)
最后一点：With WS既然只设置了一个属性，为什么要使用？（我也用它来尽可能多地匹配你的代码，但它没有用）
