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
Microsoft ExcelMicrosoft OfficeMicrosoft Applications

Avatar of undefined
Last Comment
sandramac

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

sandramac

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

Sorry, delete line 65. in line 87 and 88 change file to strFile.
sandramac

ASKER
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
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
or
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
sandramac

ASKER
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?
sandramac

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