[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

testing ftp connection within Excel

Posted on 2011-05-11
10
Medium Priority
?
1,382 Views
Last Modified: 2012-05-11
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
Comment
Question by:Canders_12
  • 5
  • 5
10 Comments
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 35741694
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
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 35741792
This may be the easiest.

If My.Computer.Network.Ping(...) '<-----This returns a boolean True/False, then you'll know.
0
 

Author Comment

by:Canders_12
ID: 35742038
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Expert Comment

by:David L. Hansen
ID: 35742059
Can you ping from a regular command line (ie. DOS)?
0
 

Author Comment

by:Canders_12
ID: 35742076
erm... possibly... I don't know to be honest. Can that be done using VBA?
0
 

Author Comment

by:Canders_12
ID: 35742182
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
 

Accepted Solution

by:
Canders_12 earned 0 total points
ID: 35742405
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
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 35742414
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
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 35742428
I don't think it will have any negative affects however let's make sure you can ping at all.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question