Check port is up from VBA (Excel)

[danfleetwood]

Hello, I am trying to create a simple procedure for a non-profit to be able to FTP a workbook to a central FTP server to store and back up a workbook off site and so remote support can get to it.

What I need is a way to check the remote port is up on the FTP server and if not report back so they know the service is down.

Can anyone help please? I have tried to search for an answer for a fair bit of time and don’t seem to be getting anywhere.

Much appreciated,
Dan

[Apps]

Sub TestPing()
    Dim strComputer As String
    strComputer = "<TargetComputerAddress>"
    If Not SystemOnline(strComputer) Then
        MsgBox "This computer is currently unreachable: " & strComputer, vbOKOnly, "Computer Status"
        '....your logic
    Else
        '...your logic
        MsgBox "This computer is Online!", vbOKOnly, "Computer Status"
    End If
End Sub 

'TestPing
'Determine if system is online
Function SystemOnline(ByVal ComputerName As String)
    Dim oShell, oExec As Variant
    Dim strText, strCmd As String
    strText = ""
    strCmd = "ping -n 3 -w 1000 " & ComputerName
    Set oShell = CreateObject("WScript.Shell")
    Set oExec = oShell.Exec(strCmd)
    Do While Not oExec.StdOut.AtEndOfStream
        strText = oExec.StdOut.ReadLine()
        If InStr(strText, "Reply") > 0 Then
            SystemOnline = True
            Exit Do
        End If
    Loop
End Function

originally from here :
http://forums.devshed.com/visual-bas...ba-328706.html

Tested and working on my system and worked ok if it's any good to you?

[danfleetwood]

Thank you Apps! However what about the checking if the port is up? Is this possible?

[Apps]

Thank you Apps! However what about the checking if the port is up? Is this possible?

Oops! Port numbers don't work with Ping.

Had a quick look and telnet or this option may give you some ideas/place to start ?
http://forum.codecall.net/visual-bas...t-scanner.html

Unfortunately I can't test it as I am at work with a load of ridiculous security settings on my system...

[danfleetwood]

Hi Apps, Thank you for a point in the right direction but do you have any idea how I tell the script:

"Can you connect to port 21? If so please do this script, If not give me an error"?

Sorry if I am missing the obvious

Cheers,
Dan

[Apps]

yeah as it was originally built to scan a number of ports, it may be easier to gut it to just what you need. Try :

Private Sub UserForm_Initialize()
    Call TestPortOpen
End Sub

Sub TestPortOpen()
    If IsPortOpen("127.0.0.1", "80") = True Then
        '...do stuff
    Else
        '...do some other stuff
    End If
End Sub

Function IsPortOpen(MyIP As String, MyPort As String) As Boolean
    IsPortOpen = False
    On Error Resume Next
    Winsock1.Close
    Winsock1.RemoteHost = MyIP
    Winsock1.RemotePort = MyPort
    Winsock1.Connect
End Function

Remember that this still uses a userform, not sure if it can be done without as it uses the control reference for Winsock. Also I apologise if it doesn't work as is, as I have coded it blind as like I said I can't test it at work.