excel - 循环期间关闭 Excel 时的错误处理
问题描述
我正在运行一个循环(如下)来逐行从 excel 中写入数据。但是,如果在循环期间关闭了 excel,我想要一个错误消息框,说明“脚本已终止”。目前,如果我关闭 excel,我会收到 Windows 脚本主机严重消息错误:“需要对象:'rw.Cells'
知道如何防止出现此 Windows 脚本宿主错误并显示脚本已被取消的自定义消息框吗?我在循环中使用了下面的 if 语句,但它不起作用。
IF 声明:
If (Err.Number <> 0) Then
On Error GoTo 0
Wscript.Echo "Script Has Been Terminated"
Wscript.Quit
End If
On Error GoTo 0
下面是循环:
Sub Main
subGetSession
subGoToScreen "DELP", "********", "0000000001"
Dim rw
Dim TITLE
TITLE = "Script Completion Confirmation"
Set atlDirectorObject = CreateObject("atlDirectorObject.atlDirector")
Set objExcel = createobject("Excel.Application")
Set objExcelWb =
objExcel.Workbooks.Open("S:\Scripting\0711_Settlement2.xlsx")
Set objExcelSht = objExcelWb.Worksheets("Test")
Set oFileObject = CreateObject("Scripting.FileSystemObject")
rw = 2
objExcel.Visible = True
Do While objExcel.CountA(objExcelSht.Rows(rw)) > 0
subDoWork objExcelSht.Rows(rw)
rw = rw + 1
If (Err.Number <> 0) Then
On Error GoTo 0
Wscript.Echo "Script Has Been Terminated"
Wscript.Quit
End If
On Error GoTo 0
Loop
Msgbox "End of Worksheet Has Been Reached" & chr(13) & "" & chr(13) & "Script
Completed!", vbOkOnly, TITLE
End Sub
编辑:所以我试图实现你在评论中所说的。我一定做错了什么。它不起作用,我做错了什么?这是我到目前为止所拥有的:
我将全局变量声明为:
Public runningScript
然后在循环开始时,我将其设置为 true,在循环结束时将其设置为 false:
Do While objExcel.CountA(objExcelSht.Rows(rw)) > 0
runningScript = True
subDoWork objExcelSht.Rows(rw)
rw = rw + 1
runningScript = False
Loop
如果变量 runningScript 为真,则 subDoWork 执行:
Sub subDoWork(rw)
If runningScript = True Then
' all the code goes here
Else
subTerminateScript "Script Canceled"
End If
End Sub
然后在excel中,我在关闭事件之前有以下内容:
Public Sub Workbook_BeforeClose(Cancel As Boolean)
runningScript = False
End Sub
我究竟做错了什么?????
解决方案
推荐阅读
- scala - 将列表的嵌套列表与列表进行比较
- docker - how to replicate docker volume host machine to another host machine volume--volume high availability host to host
- asp.net - Dependency injection @ Runtime
- c# - 如何从 Microsoft Bot C# 调用 On Premise API
- go - 新连接失败时使用数据库/sql 库并从保管库中获取密码
- c - How to pass "a pointer to a function with parameters" to another function
- bash - 如何检查文件是否存在并且是文本文件?
- vue.js - 在事件总线事件上打开模式对话框
- laravel - 如何在组件的脚本部分使用 vue-i18n 翻译功能
- rdf - rdfproc took forever to parse a 8.33 MB file?