Avatar of sandramac
 asked on

FTP via excel VBA


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
!:--- FTP commands below here ---
lcd C:\Users\Mac\Files
cd  /public_html
mput "*.htm"
Microsoft ExcelMicrosoft OfficeMicrosoft Applications

Avatar of undefined
Last Comment

8/22/2022 - Mon
Joe Howard

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.
Joe Howard

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

    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&)
            RetVal = InternetCloseHandle(INetConn)
        End If
        RetVal = InternetCloseHandle(INet)
    End If

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

End Sub

Open in new window


Getting two errors on the vba code, line 65 saying duplicate hostFile and then on line 87, file is not defined.
Your help has saved me hundreds of hours of internet surfing.
Joe Howard

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
Joe Howard

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Joe Howard

Is there still a question?

Nope, I got it.   Thanks for all your help.