我有一个连接到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/

10-13 07:30