Help With FTPPutFile

[mnunan]

Hi

I have a button on a form which runs a query, exports the data to csv and then opens that file in excel for checking.

This is my code which works great:

Private Sub RunStatements_Click()
DoCmd.TransferText acExportDelim, , "StatementReport", "G:\jobs.csv", True
Call Shell("""C:\Program Files (x86)\Microsoft Office\Office12\excel.exe"" ""G:\jobs.csv""", vbNormalFocus)
End Sub

What i would like to do is after the file is exported to csv, rather than open the file I would like it uploaded to a server, after some searching it seems that FTPPutFile would be the way to do this but I cannot find anything which is simple enough for me to understand how to execute this.

If anyone is able to modify my code to make this work I would be most grateful.

Thanks

 

[pr2-eugin]

Hello mnunan, Welcome to AWF.

The Internet Data Transfer Library, might help you get started !

 

[mnunan]

Hi pr2 and thanks for the welcome.

That link was very helpful thank you, I wonder if you are able to see where I am going wrong (bit of a novice with VBA!)

I have the following code which while it doesn't throw any error, it doesn't do anything. Now I think it is because it isn't actually being executed on the button click after the file creation but when I try to incorporate it into the first sub I get an error saying end sub missing. I have removed the ftp server info in the code below.

Private Sub RunStatements_Click()
DoCmd.TransferText acExportDelim, , "Online Statement Report", "G:\jobs.csv", True
End Sub

'********** Code Start ****************
'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish

Sub TestFTPUpload()
On Error GoTo ErrHandler
Dim objFTP As InetTransferLib.FTP
Const conTARGET = "ftp://ftp.mysite.com"

Set objFTP = New InetTransferLib.FTP
With objFTP
.FtpURL = conTARGET
.SourceFile = "G:\jobs.csv"
.DestinationFile = "/jobs/jobs.csv"
.AutoCreateRemoteDir = False
If Not .IsConnected Then .DialDefaultNumber
.ConnectToFTPHost "username", "password"
.UploadFileToFTPServer
End With
ExitHere:
On Error Resume Next
Set objFTP = Nothing
Call SysCmd(acSysCmdRemoveMeter)
Exit Sub
ErrHandler:
MsgBox Err.Number & vbCrLf & Err.Description, _
vbCritical + vbOKOnly, Err.Source
Resume ExitHere
End Sub
'********** Code End ****************

Thanks for any help

 

[pr2-eugin]

You need the Library provided there to be included in your code.. Not just the FTP Upload method..

Please use CODE tags when posting VBA Code.. End Sub missing looks like a mistake on your end of accidentally deleting an End Sub statement.

 

[mnunan]

Excuse my ignorance, I imported the addin as described in the link you provided, does the code not reference those imported classes?

If you could provide some step by step that would be great

Thanks

 

[mnunan]

Sorry not to worry, I found the solution on another forum and it is due to a slight error in the instructions for referencing the classes on Dev Ashish's site.

Fixed now and working great!

Thanks for all the help

 

[pr2-eugin]

Glad you have it working.. Good luck !