Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Posts
    2

    Unhappy How to save the Current Excel file to FTP server (must use VBA Coding)

    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

  2. #2
    Join Date
    Mar 2004
    Location
    Fort Worth, Texas, USA
    Posts
    68
    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
    Last edited by actuary; 04-21-04 at 18:52.

  3. #3
    Join Date
    Apr 2004
    Posts
    2

    Question Thank you for your suggest, but still not work!

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •