Excel VBA: FOR…on error goto NEXT

I use a For ... Next loop to insert pictures to an Excel worksheet. The name of the picture comes from column B. Sometimes, there is no corresponding picture on the hard disk. In such cases, Excel will throw an error message and stop running the codes. First, I tried "On Error Resume Next". This is not good because all the codes after the error occurs are executed. So, I tried the following code to try to avoid running codes when the picture is not found. This, however, only catches the first error. Excel still throws an error message ("unable to get the insert property of the pictures class") when the second time a picture is not found. All I want is if an error occurs, Excel would skip the rest of the code and go to the next case. How can this be done? Thanks for any help.

我用a表示……下一个循环将图片插入到Excel工作表中。图片的名称来自b列，有时硬盘上没有对应的图片。在这种情况下，Excel将抛出一个错误消息并停止运行代码。首先，我尝试了“错误简历上的下一步”。这并不好，因为错误发生后所有的代码都被执行。因此，我尝试了以下代码，以避免在找不到图片时运行代码。然而，这只能捕获第一个错误。当第二次找不到图片时，Excel仍然会抛出一个错误消息(“无法获得pictures类的insert属性”)。我想要的是，如果发生错误，Excel将跳过其余的代码，转到下一种情况。怎么做呢?感谢任何帮助。

......
On Error GoTo gotoNext
For Each cell In rng
......
Set p = Workbooks(ActiveSheet.Parent.Name).Sheets(Sheet_to_Insert_Picture).Pictures.Insert(Path_Prefix & "\" & _
Replace(cell.Value, "/", "-") & ".jpg") 'when the picture is not found, Excel throws an error
......
gotoNext:
Err.Clear
Next

1 个解决方案

#1

You can quickly check the existence of the image file with the Dir command. It will return the name of the file (hence a returned string length greater than zero) if it is found.

您可以使用Dir命令快速检查映像文件的存在。如果找到它，它将返回文件的名称(因此返回的字符串长度大于零)。

For Each cell In rng
  if cbool(len(dir(Path_Prefix & "\" & Replace(cell.Value, "/", "-") & ".jpg"))) then
    Set p = Workbooks(ActiveSheet.Parent.Name).Sheets(Sheet_to_Insert_Picture).Pictures.Insert(Path_Prefix & "\" & Replace(cell.Value, "/", "-") & ".jpg")
  end if
next cell