Solved

FTP through Excel 2007 halts on random systems

Posted on 2011-03-25
6
187 Views
Last Modified: 2012-08-13
I have about 200 users that run a specific macro that has an FTP call. The usernames and passwords are supplied and it works for 90 percent of the people.
***The credentials are the same for everyone***
The other 10 percent have Excel freeze during the FTP call. They can reach the FTP through FTP clients or browsers, but the Macro in the spreadsheet will not work.
They are all running Windows 7 with Excel 2007, and there is no difference in physical hardware.
Any help or ideas will be greatly appreciated.
Thanks
PR
0
Comment
Question by:PointRollIT
6 Comments
 
LVL 16

Expert Comment

by:AlexPace
ID: 35219903
Is your FTP client capable of producing a log file?  Maybe it will show the problem.
0
 
LVL 8

Expert Comment

by:dlongan
ID: 35220115
How about how the security setup in excel's 'trust center'
0
 

Author Comment

by:PointRollIT
ID: 35259120
Thanks for the fast response...

Log files only show that the FTP call is stopped. There are no error messages or any clues.

The Trust Center was the first thing we looked into. Unfortunately we cannot add an FTP site to the Trust Center, but all local resources have been added.

Any other ideas?
0
 
LVL 16

Accepted Solution

by:
AlexPace earned 500 total points
ID: 35260556
I have an idea that will cost money but will work If all of your users are on the same internal network and they all use the same username / password on the remote FTP server ...

Set up a network share and allow everyone to write to it.  Then set up a "hot send" service using Robo-FTP.  This will monitor the folder and automatically upload any files that appear.  Then modify your Excel macro to copy the file to be sent to this network share instead of using FTP from the individual machine.  Then you only have to worry about maintaing the FTP connection from one server.  Also you get the advantages of protocol-level logging if you need to troubleshoot and automatic retries if there is a transmission error.  Here is a sample Robo-FTP command script for implementing a "hot send" folder:
LOG "hot_send_script.log"
TRACELOG "hot_send_trace.log"
WORKINGDIR "c:\data\source\folder"

:find_file 
GETNEXTFILE "*" /timeout=0 
IFERROR= $ERROR_WAIT_TIMED_OUT GOTO find_file
 
:upload 
FTPLOGON "ftp.new.com" /user=anonymous /pw=itchy 
IFERROR!= $ERROR_SUCCESS GOTO upload_error 
SENDFILE %nextfile 
IFERROR!= $ERROR_SUCCESS GOTO upload_error 
FTPLOGOFF 
DELETE %nextfile 
GOTO find_file 

:upload_error 
FTPLOGOFF 
GETNEXTFILE %nextfile  
IFERROR= $ERROR_SUCCESS GOTO upload  ; if file exists try to upload 
GOTO find_file     ; file no longer exists so find another file

Open in new window


If some of your users are mobile and might be trying to use this macro remotely whileout a VPN into your network you could add a little test in the macro to see if it has access to the shared hot send folder and if that folder is unreachable fall back to your existing FTP method.
0
 
LVL 24

Expert Comment

by:broomee9
ID: 35821698
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

863 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now