Excel VBA:FOR ...出错，转到下一个 [英] Excel VBA: FOR...on error goto NEXT

问题描述

我使用For ... Next循环将图片插入到Excel工作表中.图片的名称来自B列.有时，硬盘上没有相应的图片.在这种情况下，Excel将抛出错误消息并停止运行代码.首先，我尝试了"On Error Resume Next".这不好，因为执行了发生错误后的所有代码.因此，我尝试使用以下代码来避免未找到图片时运行代码.但是，这仅捕获第一个错误.当第二次找不到图片时，Excel仍会引发错误消息(无法获取图片类的insert属性").我想要的是如果发生错误，Excel将跳过其余代码，转到下一种情况.如何才能做到这一点?感谢您的帮助.

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.

......
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

推荐答案

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

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.

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

这篇关于Excel VBA:FOR ...出错，转到下一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！