• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1508
  • Last Modified:

testing ftp connection within Excel

Hello everybody,

I'm looking to test an ftp connection from within Excel VBA. I am using the following code to open a file from an FTP but if there is no connection, Excel gets stuck:

Workbooks.OpenText Filename:="ftp://" & Range("IP_Address_on_Box") & "/FS_LS.log", Origin:= _
        xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
        , ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:= _
        False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True

I've tried doing it without VBA by going through the menu system (File > Open etc.), but it still gets stuck and I have to close Excel using Task Manager.

Is there a way to test a connection over an ftp in advance of running this code?

Thanks in advance :_
0
Canders_12
Asked:
Canders_12
  • 5
  • 5
1 Solution
 
David L. HansenProgrammer AnalystCommented:
Look at this thread.  It shows the use of the clsFTP class, which has a method called "isConnected".  The code is being used in Access but it is true VBA and so will work in your Excel app.

http://www.accessmonster.com/Uwe/Forum.aspx/databases-ms-access/25795/Code-for-controling-FTP-from-Access-VBA
0
 
David L. HansenProgrammer AnalystCommented:
This may be the easiest.

If My.Computer.Network.Ping(...) '<-----This returns a boolean True/False, then you'll know.
0
 
Canders_12Author Commented:
thanks for you help sl8rz...

I'm sure what you're saying is along the right lines but I keep getting "object required". So far I've tried:

My.Computer.Network.Ping("ftp://192.168.254.26")
and
My.Computer.Network.Ping("192.168.254.26")

Any ideas? Thanks again for you help :)
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
David L. HansenProgrammer AnalystCommented:
Can you ping from a regular command line (ie. DOS)?
0
 
Canders_12Author Commented:
erm... possibly... I don't know to be honest. Can that be done using VBA?
0
 
Canders_12Author Commented:
i've got this working in DOS:

"ping 192.168.2.1 >> c:\output.txt"

and so i tried the following in VBA:

Shell "ping 192.168.2.1 >> c:\output.txt", vbNormalFocus

A DOS window flashes up and disappears but no text file appears on the C-drive...

Any ideas gratefully received :)
0
 
Canders_12Author Commented:
I think the following may just do it:

Set WshShell = CreateObject("WScript.Shell")
ping = WshShell.Run("ping -n 1 " & HostName, 0, True)

Any ideas if this would have any negative effects?
0
 
David L. HansenProgrammer AnalystCommented:
So if you are just at a regular command prompt (C:\   )  good old dos, you can ping?

BTW, if you havn't used it, you can just click START -> RUN -> then type "cmd" and you'll get your command prompt.
0
 
David L. HansenProgrammer AnalystCommented:
I don't think it will have any negative affects however let's make sure you can ping at all.
0
 
Canders_12Author Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now