Link to home
Start Free TrialLog in
Avatar of sandramac
sandramac

asked on

FTP via excel VBA

Hello

Looking for some help.  I have a macro that save several worksheets as an html file in my C:/user/Mac/Files directory.  Is there a way I can add another set of code to FTP all the .html files in C:/user/Mac/Files directory to me website server.  Here is the script file that I have been using to do it manually:

open *******.net
name@********.net
password
!:--- FTP commands below here ---
lcd C:\Users\Mac\Files
cd  /public_html
binary
mput "*.htm"
disconnect
bye
Avatar of Joe Howard
Joe Howard
Flag of United States of America image

The easiest way is to shell (run) the bat file from vba, for example:

Dim strBatchName as String

strBatchName = "C:\YourFile.bat"
Shell strBatchName 

Open in new window


If you're interested in using only vba, it is possible using api calls. If intrested I will post an example.
Here is the Vba method:

Option Explicit


'Written: June 11, 2008
'Author:  Leith Ross

'Open the Internet object
Private 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

'Connect to the network
Private 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

'Get a file using FTP
Private Declare Function FtpGetFile _
                          Lib "wininet.dll" _
                              Alias "FtpGetFileA" _
                              (ByVal hFtpSession As Long, _
                               ByVal lpszRemoteFile As String, _
                               ByVal lpszNewFile As String, _
                               ByVal fFailIfExists As Boolean, _
                               ByVal dwFlagsAndAttributes As Long, _
                               ByVal dwFlags As Long, _
                               ByVal dwContext As Long) As Boolean

'Send a file using FTP
Private 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

'Close the Internet object
Private Declare Function InternetCloseHandle _
                          Lib "wininet.dll" _
                              (ByVal hInet As Long) As Integer

Sub UploadFTP()

'When uploading a file, make sure you have permisson to create a file on the server.
'The size limit for a uploading a file is 4GB.

    Dim hostFile As String
    Dim INet As Long
    Dim INetConn As Long
    Dim hostFile As String
    Dim Password As String
    Dim RetVal As Long
    Dim ServerName As String
    Dim Success As Long
    Dim UserName As String
    Dim StrFile As String

    Const ASCII_TRANSFER = 1
    Const BINARY_TRANSFER = 2

    ServerName = "myserver.some.company"
    UserName = "anonymous"
    Password = "MyEmail@somewhere.net"
    hostFile = "\\My Test File.txt"

    RetVal = False
    INet = InternetOpen("MyFTP Control", 1&, vbNullString, vbNullString, 0&)
    If INet > 0 Then
        INetConn = InternetConnect(INet, ServerName, 0&, UserName, Password, 1&, 0&, 0&)
        If INetConn > 0 Then
            StrFile = Dir("C:\Users\Mac\Files\*.html")
            While (file <> "")
                file = Dir
                Success = FtpPutFile(INetConn, StrFile, hostFile, BINARY_TRANSFER, 0&)
            Wend
            RetVal = InternetCloseHandle(INetConn)
        End If
        RetVal = InternetCloseHandle(INet)
    End If

    If Success <> 0 Then
        MsgBox ("Upload process completed")
    Else
        MsgBox "FTP File Error!"
    End If

End Sub

Open in new window

Avatar of sandramac
sandramac

ASKER

Getting two errors on the vba code, line 65 saying duplicate hostFile and then on line 87, file is not defined.
Sorry, delete line 65. in line 87 and 88 change file to strFile.
thanks, okay  I corrected the errors, but now it goes straight to the FTP File Error.  What doeshostFile = "\\My Test File.txt" suppose to equal is this the directory i am pushing the file
ASKER CERTIFIED SOLUTION
Avatar of Joe Howard
Joe Howard
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks the second one works.  I was also looking at your first suggest of calling up the bat file.  I tried that one, it pulls up the cmd window but them immediately closes it.
Is there still a question?
Nope, I got it.   Thanks for all your help.