I have written an Excel 2003 VBA macro to save an Excel sheet as a CSV file locally. I then want the macro to upload that CSV file to an FTP site using a hardcoded id & password.
I created a macro called mcrTestUploadControl. This macro calls the custom function UploadFile (pasted below). I copied this function from a website: http://officeone.mvps.org/vba/ftp_upload_file.html
The function uses an old ActiveX control, msinet.ocx, that is called the “Microsoft Internet Transfer Control“. I have this old control on my home computer, but not on my work computer (Windows XP with Excel 2003).
I can compile the macro and function on my home computer, but the function does not upload the file anyway.
Can anyone help me with an Excel 2003 macro to upload the CSV file via FTP? In Excel 2003, VBA just does not recognize the Inet control.
Function UploadFile(ByVal HostName As String, _
ByVal UserName As String, _
ByVal Password As String, _
ByVal LocalFileName As String, _
ByVal RemoteFileName As String) As Boolean
Dim FTP As Inet
Set FTP = New Inet
.Protocol = icFTP
.RemoteHost = HostName
.UserName = UserName
.Password = Password
.Execute .URL, "Put " + LocalFileName + " " + RemoteFileName
Do While .StillExecuting
UploadFile = (.ResponseCode = 0)
Set FTP = Nothing