Excel/VBA - 如果网络连接不存在则中止脚本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23032162/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel/VBA - Abort script if network connection does not exist
提问by Josh
Are there any VBA code to look for a present internet connection?
是否有任何 VBA 代码来查找当前的 Internet 连接？
I have a code that will run on a timer. This code will open a file on a local network share drive. I am looking for some type of On Error Goto ErrorMessagecode if it tries to open the file when the network isn't connected.
我有一个可以在计时器上运行的代码。此代码将打开本地网络共享驱动器上的文件。如果在网络未连接时尝试打开文件，我正在寻找某种类型的On Error Goto ErrorMessage代码。
回答by Dan Wagner
You can check the Lenof Diron the shared drive you're trying to get to:
您可以检查Len的Dir你试图去共享驱动器上：
Option Explicit
Sub TestForNetworkDrive()
'suppose the file we want is at Z:\cool\vba\files\file.txt
If Len(Dir("Z:\cool\vba\files\file.txt")) = 0 Then
'handle network not available issue
MsgBox ("Network share not found...")
Else
'do timer-based code here
MsgBox ("Let's get to work!")
End If
End Sub
回答by Michael Seemann
This function works on both Mac and PC, with 32 as well as 64 bit Excel versions.
此功能适用于 Mac 和 PC，适用于 32 位和 64 位 Excel 版本。
Declaration:
宣言：
Option Explicit
#If VBA7 And Win64 Then
Private Declare PtrSafe Function InternetGetConnectedStateEx Lib "wininet.dll" (ByRef lpdwFlags As Long, ByVal lpszConnectionName As String, ByVal dwNameLen As Integer, ByVal dwReserved As Long) As Long
#Else
Private Declare Function InternetGetConnectedStateEx Lib "wininet.dll" (ByRef lpdwFlags As Long, ByVal lpszConnectionName As String, ByVal dwNameLen As Integer, ByVal dwReserved As Long) As Long
#End If
Function:
功能：
Function IsInternetConnected() As Boolean
Dim strConnType As String, lngReturnStatus As Long, MyScript As String
If Application.OperatingSystem Like "*Macintosh*" Then
MyScript = "repeat with i from 1 to 2" & vbNewLine
MyScript = MyScript & "try" & vbNewLine
MyScript = MyScript & "do shell script ""ping -o -t 2 www.apple.com""" & vbNewLine
MyScript = MyScript & "set mystatus to 1" & vbNewLine
MyScript = MyScript & "exit repeat" & vbNewLine
MyScript = MyScript & "on error" & vbNewLine
MyScript = MyScript & "If i = 2 Then set mystatus to 0" & vbNewLine
MyScript = MyScript & "end try" & vbNewLine
MyScript = MyScript & "end repeat" & vbNewLine
MyScript = MyScript & "return mystatus"
If MacScript(MyScript) Then IsInternetConnected = True
Else
lngReturnStatus = InternetGetConnectedStateEx(lngReturnStatus, strConnType, 254, 0)
If lngReturnStatus = 1 Then IsInternetConnected = True
End If
End Function
Using the function in a Sub:
在 Sub 中使用该函数：
If IsInternetConnected Then
MsgBox"Network Connection Detected"
Else
MsgBox"No Network Connection Detected"
End If
回答by WBuck
I usually use the code below to determine if a network connection exits. The function returns true or false.
我通常使用下面的代码来确定网络连接是否存在。该函数返回真或假。
Declaration:
宣言：
Private Declare Function InternetGetConnectedStateEx Lib "wininet.dll" (ByRef _
lpdwFlags As Long, ByVal ipszConnectionName As String, ByVal _
dwNameLen As Integer, ByVal dwReserved As Long) As Long
Function:
功能：
Public Function IsInternetConnected() As Boolean
Dim strConnType As String
Dim lngReturnStatus As Long
IsInternetConnected = False
lngReturnStatus = InternetGetConnectedStateEx(lngReturnStatus, strConnType, 254, 0)
If lngReturnStatus = 1 Then IsInternetConnected = True
End Function
Using the function in a Sub:
在 Sub 中使用该函数：
If IsInternetConnected = False Then
output = MsgBox("No Network Connection Detected!", vbExclamation, "No Connection")
Else
Do stuff that requires the network connection
End If
