我有一个连接到MySQL服务器的Excel工作簿。我放置了一个刷新按钮来刷新数据,它包含工作表的unprotect和保护语句。
我的问题是,当打开excel文件并在不在网络中的计算机上单击“刷新”时,我得到一个DSN创建向导,并且如果在向导中按“取消”,该表将变得不受保护。
我想放置一个IF条件,以检查DSN是否可用,如果没有,则应退出sub。
有任何想法吗?
这是我的带有错误处理程序的代码,但是我仍然获得DSN创建向导,并且在关闭msgbox之后,工作表不受保护
On Error GoTo handler
Application.ScreenUpdating = False
Sheets("DEC-2015").Unprotect Password:="password"
ActiveWorkbook.Connections("Query from Sample").Refresh
Sheets("DEC-2015").Protect _
Password:="password", _
UserInterfaceOnly:=True, _
AllowFiltering:=True, _
AllowSorting:=True, _
AllowUsingPivotTables:=True
handler:
MsgBox "Server Connection Lost...", vbOKOnly + vbCritical, "Warning"
Exit Sub
最佳答案
通过将代码更改为以下内容来关闭警报:
Public Sub DoSomething()
On Error GoTo handler
With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With
With ThisWorkbook
.Sheets("DEC-2015").Unprotect Password:="password"
.Connections("Query from Sample").Refresh
.Sheets("DEC-2015").Protect _
Password:="password", _
UserInterfaceOnly:=True, _
AllowFiltering:=True, _
AllowSorting:=True, _
AllowUsingPivotTables:=True
End With
With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With
Exit Sub
handler:
ThisWorkbookSheets("DEC-2015").Protect _
Password:="password", _
UserInterfaceOnly:=True, _
AllowFiltering:=True, _
AllowSorting:=True, _
AllowUsingPivotTables:=True
With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With
MsgBox "Server Connection Lost...", vbOKOnly + vbCritical, "Warning"
End Sub
关于mysql - 检查DSN是否在excel vba中存在,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/39425992/