Solved: Macro to FTP

[island17]

Hello All



I need some help using FTP from Word. I know that I can add a FTP site to the "save as" list in Word, and save my username and password as well. Is there away to do this with a macro. Our users just want to click a button, and not perform too many steps. Any ideas.



Thanks

Russ:help

[xCav8r]

I did a job for a few clients that required me to get data files via FTP. There may have been other ways to accomplish that, but I used VBA to spit out a .bat file that I used to grab the data files via the DOS FTP. I'm not sure I understand what you want to do exactly, but if this sounds like it would be helpful, I would be happy to share.

[island17]

Sorry if I wasn’t clear enough, let me give it another try. Word provides the capability to log into a FTP site. It will allow you to send anonymously or provide a user name and password. What I want to do is to automate this so the user doesn’t have to fill anything out. I need to create a macro that will save the current document to the FTP site, and provide the address, username, and password. Any ideas?



Thanks

Russ

[xCav8r]

I had no idea about that. I did things the hard way. I'm glad you asked this question. If I get a chance later today, I'll look at changing the way I was getting files via FTP. Maybe the code will be useful to you, or maybe someone else will come along to help before I get around to it. ;)

[MOS MASTER]

Sorry if I wasn?t clear enough, let me give it another try. Word provides the capability to log into a FTP site. It will allow you to send anonymously or provide a user name and password. What I want to do is to automate this so the user doesn?t have to fill anything out. I need to create a macro that will save the current document to the FTP site, and provide the address, username, and password. Any ideas?

Hi Russ, :yes

I don't have an FTP server so I can't test things for you.

But I can probably find the right VBA objects for you if they are provided within VBA.

Could you please tell me the exact steps you do in Word to logon to the FTP Server and Upload the files. (Name chosen menu items!)

If I have that information I can go in the Object Explorer and look for the right Objects.

Later...:yes

[island17]

Here are the steps I used to set up Word to look at the FTP site. I just copied the instructions from Word’s Help. It works quite simply.



Here are the steps to set up the FTP site in Word.



1 Click Open .

2 In the Look in box, click Add/Modify FTP Locations.

3 In the Name of FTP site box, type the FTP site name; for example, type ftp://ftp.microsoft.com/

4 If you want to log on to an FTP site that allows anonymous log on, click Anonymous.



If you want to log on to an FTP site that you have user privileges for, click User, and then type your password.




Click Add.

Open a document at an FTP site



Before you can open a document at an FTP site, you must add the FTP site to the list of Internet sites. For information about how to add an FTP site to the list of Internet sites, click .



1 Click Open .

2 In the Look in box, click Internet Locations (FTP).

3 In the list of FTP sites, double-click the site you want, and then double-click the location at the site that contains the document you want to open.

4 Double-click the document you want to open.



Tips



? To quickly open an FTP site or a document at an FTP site, type the FTP address in the File name box, and then press ENTER. For example, type ftp://ftp.microsoft.com/ to open the Microsoft FTP site. If the FTP Logon dialog box appears (if this is the first time you've opened this FTP site during this session of Word), click Anonymous if you want to log on to an FTP site that allows anonymous log on. If you want to log on to an FTP site that you have user privileges for, click User, and then type your password.



? To open a document on a Gopher server, type the address in the File name box, and then press ENTER.



For example, type gopher://gopher.someones.homepage

Here are instructions to open a document from a FTP site. Even though I want to send the document, the difference is that I would use "Save As" instead of "Open"

[MOS MASTER]

Okay...see I'll see what I can come up whit from within Word VBA...:whistle:

[island17]

Thanks xCav8r. I used your .bat file technique and it works great.
This is how it works.
The macro writes a text file out to a directory on the server. Then the macro creates a file with the FTP commands, and a .bat file to execute the FTP commands file. The bat file will also create an out file to indicate the the process is complete.
The last thing the macro does is to go back and delete these file when it is finished.

[MOS MASTER]

Hi Russ, :yes

Sorry I totally forgot about this thread! :banghead:

Glad to see it all worked out for yah.
Would you be so kind to share your code with us so you can helpout someone seeking the same knowledge in the future? (Or perhaps make an example and create a kb entry?)

Thank you. :whistle:

[xCav8r]

I sent the code to him, which was something that I had grabbed a long time ago from some Excel site. I have since modified it quite a bit, but because it was borrowed, I'm not sure it would be appropriate for the KB, not knowing what the guidelines are for things like that.

[MOS MASTER]

Hi, :yes

I'm not sure as well because I've not seen it yet and don't know if its easy to use!

It doesn't have to be a kb but it could be. I'm sure you'd help someone in the future if you post the code and the howto use it?

Thanx. :whistle:

[island17]

Here is some code I found on the web that is similar to cXav8r's code. I wound up using a highbred of the two for my project.


Sub PublishFile()
Dim strDirectoryList As String
Dim lStr_Dir As String
Dim lInt_FreeFile01 As Integer
Dim lInt_FreeFile02 As Integer
On Error GoTo Err_Handler
lStr_Dir = ThisWorkbook.Path
lInt_FreeFile01 = FreeFile
lInt_FreeFile02 = FreeFile
'' ANW 07-Feb-2003 :
strDirectoryList = lStr_Dir & "\Directory"
'' Delete completion file
If Dir(strDirectoryList & ".out") <> "" Then Kill (strDirectoryList & ".out")
'' Create text file with FTP commands
Open strDirectoryList & ".txt" For Output As #lInt_FreeFile01
Print #lInt_FreeFile01, "open yoursite.com"
Print #lInt_FreeFile01, "account_name"
Print #lInt_FreeFile01, "account_password"
Print #lInt_FreeFile01, "cd source/uploads"
Print #lInt_FreeFile01, "binary"
Print #lInt_FreeFile01, "send " & ThisWorkbook.Path & "\Picture.gif targetdir/Picture.gif"
'' To receive a file, replace the above line with this one
''Print #lInt_FreeFile01, "recv \Picture.gif " & ThisWorkbook.Path & "\Picture.gif"

Print #lInt_FreeFile01, "bye"
Close #lInt_FreeFile01
'' Create Batch program
Open strDirectoryList & ".bat" For Output As #lInt_FreeFile02
Print #lInt_FreeFile02, "ftp -s:" & strDirectoryList & ".txt"
Print #lInt_FreeFile02, "Echo ""Complete"" > " & strDirectoryList & ".out"
Close #lInt_FreeFile02
'' Invoke Directory List generator
Shell (strDirectoryList & ".bat"), vbHide '', vbMinimizedNoFocus
'Wait for completion
Do While Dir(strDirectoryList & ".out") = ""
DoEvents
Loop
Application.Wait (Now + TimeValue("0:00:03"))
'' Clean up files
If Dir(strDirectoryList & ".bat") <> "" Then Kill (strDirectoryList & ".bat")
If Dir(strDirectoryList & ".out") <> "" Then Kill (strDirectoryList & ".out")
If Dir(strDirectoryList & ".txt") <> "" Then Kill (strDirectoryList & ".txt")
bye:
Exit Sub
Err_Handler:
MsgBox "Error : " & Err.Number & vbCrLf & "Description : " & Err.Description, vbCritical
Resume bye
End Sub

[MOS MASTER]

Thanks for sharing. :yes

[xCav8r]

Yes, that looks familiar. I think that's exactly what I adopted.