Check network connection with Excel VBA

1

I have a macro that saves three sheets of a PDF file to the network. Loops and checks which worksheets will save to PDF, and saves correctly. The macro hangs or gives error when the network is bad or it has fallen, needing to use the ESC key.

How to first check if the network is live and operational?

If the network is mapped as units, logically not finding is because the network is inactive or the path does not exist.

Use On Error Resume Next one line before saving in PDF, and shortly after If Err.Number > 0

Dim CaminhoArq As String
Dim NomeArq As String
CaminhoArq = ActiveSheet.Range("F2").Value
NomeArq = ActiveSheet.Range("F4").Value
If Right(CaminhoArq, 1) <> "\" Then
    CaminhoArq = CaminhoArq & "\"
    On Error Resume Next
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                                    Filename:=CaminhoArq & NomeArq & ".pdf", _
                                    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                                    IgnorePrintAreas:=False, OpenAfterPublish:=False
    If Err.Number > 0 Then
        MsgBox "Caminho da pasta não existe!", vbExclamation, "AAAAA"
        Exit Sub
    End If
End If

If there is no path, the error message is immediate.

But if the path and the network were inactive or weak, it was to give immediate error and fall into the IF Err.Number, but Excel hangs and does not respond, just by pressing ESC if you can stop the macro.

A code that I found on the Internet, which would like to know if it works for both the network and the internet.

And continue the code only if the network is good: being bad or with some problem or inactive, for the code.

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

Public Function IsInternetConnected() As Boolean
    IsInternetConnected = InternetGetConnectedStateEx(0, "", 254, 0)
End Function

Sub TestandoConexão()
    If IsInternetConnected = True Then
        MsgBox "Continua o código"
    Else
        MsgBox "Código interrompido"
    End If
End Sub
    
asked by anonymous 24.04.2018 / 14:02

0 answers