Dear Expert,
How to save the Current Excel file to FTP server (must use VBA Coding)

I have tried many ways but it seem cannot work through.....

Tried #1 ....
ActiveWorkbook.SaveAs "ftp://usernameassword@61.38.xxx.xxx/Reports/" &
FileName
Error Message come out


Tried #2 ....
Open FilePath & "ftp.bat" For Output As #1
Print #1, "open " & "61.38.xxx.xxx"
Print #1, "username"
Print #1, "password"
Print #1, "put"
Print #1, """" & FileNameSaved & """"
Print #1, "/Reports/" & FileName
Print #1, "close"
Print #1, "quit"
Close #1

RetVal = Shell("ftp.exe -s:""" & FilePath & "ftp.bat""", vbHide)
Error Message come out too, and do not know when is finish upload
process


Tried #3 ....
I used "wininet.dll" via API

Dim hConnection As Long, hOpen As Long, sOrgPath As String
'// open an internet connection
hOpen = InternetOpen("Upload Excel To Ftp", _
&H0, _
vbNullString, _
vbNullString, _
0)
'// connect to the FTP server
hConnection = InternetConnect(hOpen, _
"61.38.xxx.xxx", _
21, _
"username", _
"password", _
1, _
0, _
0)
'// set the current directory to 'root/reports'
Ret = FtpSetCurrentDirectory(hConnection, "Reports")
'// upload the file
Ret = FtpPutFile(hConnection, FileNameSaved, FileName, &H0, 0)

'// close the FTP connection
InternetCloseHandle hConnection
'// close the internet connection
InternetCloseHandle hOpen

Error on the major instruction, i.e. "Ret = FtpPutFile(hConnection,
FileNameSaved, FileName, &H0, 0)"


Cannot Try #4 ....
I heard, can use an ActiveX control (Inet.ocx), but I cannot find it
on the VBA project reference, even the userform control add also cannot find
this component.

Appreciate on advise

Patrick
patrickhk@go.com

You're past my pay-grade, but did you DECLARE the functions you wish to call?

Public Declare Function InternetConnect Lib "wininet.dll" Alias _
"InternetConnectA" (ByVal InternetSession As Long, _
ByVal sServerName As String, ByVal nServerPort As Integer, _
ByVal sUsername As String, ByVal sPassword As String, _
ByVal lService As Long, ByVal lFlags As Long, _
ByVal lContext As Long) As Long


et. al.

I'll be impressed if you literally save the current (open) file to FTP. Maybe that's what your FilenameSaved variable is--a saved copy of the current file.

Also, I remember an MSDN article on this. Search msdn.microsoft.com for Teaching Fido to Phetch

To actuary and Experts,
Thank you very much for actuary advise, but it's seem not working.

I NEED HELP ASAP .....

Well, I am doing a program in Excel 2003, with the MS Win 2003 FTP Server. This file can share to all users after they download from the FTP, user will change the value in the file and save it back to the FTP server using a different name to recognize (User also need to keep a copy on their desktop too).

<Logic for user>
1. GET FILE FROM FTP SERVER ON IE AFTER VALID LOGIN (USER CHOOSE VERSION)
2. DOWNLOAD AND OPEN THE FILE.
3. UPDATE NEW VALUE(S).
4. CLICK "SAVE AS" FUNCTION, CHOOSE USER's DESKTOP LOCATION.
5. PROMPT TO ASK "UPDATE FTP SERVER (YES / NO ?)"
6. <PROGRAM DO> SAVE THE FILE TO USER CHOOSED LOCATION AND SAVE THE FILE TO FTP (IF, YES).

For Normal "Save", the program will do normal.


For users to use easy, I add some coding in the "Saveas" event, so that when user saveas file, it will auto save the current file to destination and auto save a copy to the FTP server.

Followings are all my code ...


My DECLARE functions .........

' Initializes an application's use of the Win32 Internet functions
Public Declare Function InternetOpen Lib "wininet.dll" Alias "InternetOpenA" _
(ByVal sAgent As String, ByVal lAccessType As Long, ByVal sProxyName As String, _
ByVal sProxyBypass As String, ByVal lFlags As Long) As Long

' Opens a HTTP session for a given site.
Public Declare Function InternetConnect Lib "wininet.dll" Alias "InternetConnectA" _
(ByVal hInternetSession As Long, ByVal sServerName As String, ByVal nServerPort As Integer, _
ByVal sUsername As String, ByVal sPassword As String, ByVal lService As Long, _
ByVal lFlags As Long, ByVal lContext As Long) As Long

'Set FTP Current Directory
Public Declare Function FtpSetCurrentDirectory Lib "wininet.dll" Alias "FtpSetCurrentDirectoryA" _
(ByVal hFtpSession As Long, ByVal lpszDirectory As String) As Boolean

'FTP PUT FILE
Public Declare Function FtpPutFile Lib "wininet.dll" Alias "FtpPutFileA" _
(ByVal hFtpSession As Long, ByVal lpszLocalFile As String, _
ByVal lpszRemoteFile As String, _
ByVal dwFlags As Long, ByVal dwContext As Long) As Boolean

Public Declare Function InternetCloseHandle Lib "wininet.dll" ( _
ByVal hInet As Long) As Integer


My Program in SAVEAS EVENT .........

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI And Not ToSaveOrNotToSave Then

FileNameSaved = Application.GetSaveAsFilename(ActiveWorkbook.Name, _
fileFilter:="Microsoft Excel Workbook (*.xls), *.xls")

Cancel = True

If FileNameSaved <> "" Then SaveNewFileName

End If
End Sub


Sub SaveNewFileName()

ToSaveOrNotToSave = True


ActiveWorkbook.SaveAs FileNameSaved


If MsgBox("Do you want to Update FTP Server", vbYesNo) = vbYes Then

FileName = Right(FileNameSaved, Len(FileNameSaved) - InStrRev(FileNameSaved, "\"))
FilePath = Left(FileNameSaved, Len(FileNameSaved) - Len(FileName))

Dim hConnection As Long, hOpen As Long
'// open an internet connection
hOpen = InternetOpen("Upload Excel To Ftp", _
&H0, _
vbNullString, _
vbNullString, _
0)

' <THE "hOpen" return "13369348">

'// connect to the FTP server
hConnection = InternetConnect(hOpen, _
"61.38.xxx.xxx", _
21, _
"username", _
"password", _
1, _
0, _
0)

' <THE "hConnection" return "13369352">

'// upload the file
Ret = FtpPutFile(hConnection, FileNameSaved, FileName, &H0, 0)

'<THE "Ret" return "False", I think this sentence did wrong!>

'// close the FTP connection
InternetCloseHandle hConnection
'// close the internet connection
InternetCloseHandle hOpen


End If

ToSaveOrNotToSave = False

End Sub


Experts, Please HELP me ASAP .....

Appreciate on advise


Patrick
patrickhk@go.com