Solved

FTP through Excel 2007 halts on random systems

Posted on 2011-03-25
6
185 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
Comment Utility
Is your FTP client capable of producing a log file?  Maybe it will show the problem.
0
 
LVL 8

Expert Comment

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

Author Comment

by:PointRollIT
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

772 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

11 Experts available now in Live!

Get 1:1 Help Now