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. :banghead:

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-basic-programming-52/ping-from-vba-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-basic-tutorials/928-visual-basic-6-0-tutorial-making-port-scanner.html

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

[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.