Enum gdGifDims
    gdHeight = 0
    gdWidth = 1
End Enum
 
Sub UploadPicture()
   
    Dim vFname As Variant
    Dim i As Long
    Dim sTags As String
    Dim lHeight As Long
    Dim lWidth As Long
    
    Const sIMG As String = ""
   
    'get one or more gif files
    vFname = Application.GetOpenFilename("*.gif; *.jpg, *.gif; *.jpg", , , , True)
   
   'If vFname <> "False" Then
        'Make the img tags
        For i = LBound(vFname) To UBound(vFname)
            lHeight = GetGifDim(vFname(i), gdHeight)
            lWidth = GetGifDim(vFname(i), gdWidth)
            
            sTags = sTags & sIMG & sPath & Dir(vFname(i)) & Chr$(34) & _
                " height=""" & lHeight & _
                """ width=""" & lWidth & _
                """" & sIMGEND & vbCrLf
        Next i
    'End If
    'SendViaFtp vFname
    SendViaSCP vFname
    
    'put string in clipboard
    PutInClip sTags
   
End Sub
 
Function GetGifDim(ByVal sFname As String, ByVal eDim As gdGifDims) As Long
   
    Dim btBuffer(10) As Byte 'to get the first 10 bits
    Dim lFnum As Long
   
    lFnum = FreeFile
   
    'open the file and read in the bits
    Open sFname For Binary As lFnum
    Get lFnum, 1, btBuffer
    Close lFnum
   
    If eDim = gdHeight Then
        GetGifDim = btBuffer(8) + (btBuffer(9) * 256)
    Else
        GetGifDim = btBuffer(6) + (btBuffer(7) * 256)
    End If
   
End Function
 
Sub SendViaFtp(vFname As Variant)
   
    'code modified from http://www.bygsoftware.com/Excel/VBA/ftp.htm
   
    Dim i As Long
    Dim lFnumFtp As Long, lFnumBatch As Long
    Dim sFname As String
    Dim sPath As String
    
    Const sSITE As String = "ftp.mysite.com"
    Const sUSER As String = "MyUserName"
    Const sPASS As String = "MyPassword"
    Const sDIR As String = "public_html/ddoe/blogpix/"
           
    sPath = Environ$("TMP") & Application.PathSeparator
    sFname = sPath & Format(Now, "yyyymmddhhmm")
    lFnumFtp = FreeFile
   
    'Create text file with ftp commands
    Open sFname & ".txt" For Output As lFnumFtp
    Print #lFnumFtp, "open " & sSITE 'open the site
    Print #lFnumFtp, sUSER
    Print #lFnumFtp, sPASS
    Print #lFnumFtp, "binary" 'set file transfer mode
    Print #lFnumFtp, "cd " & sDIR
    For i = LBound(vFname) To UBound(vFname)
        Print #lFnumFtp, "send " & Dir(vFname(i)) 'send files
    Next i
    Print #lFnumFtp, "bye" 'close ftp session
   
    Close lFnumFtp  'close text file
   
    lFnumBatch = FreeFile
   
    'open a batch file
    Open sFname & ".bat" For Output As lFnumBatch
    Print #lFnumBatch, "ftp -s:" & sFname & ".txt"
    Print #lFnumBatch, "Echo ""Complete""> " & sFname & ".out"
    Close lFnumBatch
   
    'run the batch file
    Shell sFname & ".bat"
   
    'what until the ftp session is closed
    Do While Dir(sFname & ".out") = ""
        DoEvents
    Loop
   
    Application.Wait Now + TimeValue("0:00:03")
   
    'clean up files used
'    On Error Resume Next
'        Kill sFname & ".txt"
'        Kill sFname & ".bat"
'        Kill sFname & ".out"
'    On Error GoTo 0
   
End Sub
 
Sub PutInClip(sTags As String)
   
    Dim doObject As DataObject
   
    Set doObject = New DataObject
   
    doObject.SetText sTags
    doObject.PutInClipboard
   
End Sub

13 thoughts on “FTP Via VBA”

1. Blogger has a addin that lets you post from word, but does not support pictures. Time permiting, i may try and add that function to it – it maybe a limit from the blogger side, but this code will be a good start!

2. I used to create an FTP script and use Shell() to execute http://ftp.exe similiar to what you have done in your code here, but the major downside I saw to this was that the user name and password had to be stored in plain text in the script file (if only for a few seconds).

   Anyway I didn’t really like the idea of having to generate an FTP script either. It seemed to amateur to me, so I rewrote my FTP code using wininet.dll

   Simply you just call InternetOpen(), InternetConnect(), and then FtpGetFile() or FtpPutFile()

   ‘ 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

3. I agree with Billkamm. I dug around and found:
   http://www.15seconds.com/issue/981203.htm
   then wrote my code like:
   Dim lngINetConn
   Dim lngINet
   Dim blnRC As Boolean
   Dim UserName As String
   Dim Password As String
   Dim serverName As String
   Const ASCII_TRANSFER = 1
   Const BINARY_TRANSFER = 2

   serverName = “myserver.some.company”
   UserName = “anonymous”
   Password = “foo@foo.net”

   blnRC = False
   lngINet = InternetOpen(“MyFTP Control”, 1, vbNullString, vbNullString, 0)
   If lngINet > 0 Then
   lngINetConn = InternetConnect(lngINet, serverName, 0, UserName, Password, 1, 0, 0)
   If lngINetConn > 0 Then

   blnRC = FtpPutFile(lngINetConn, localFile, hostFile, BINARY_TRANSFER, 0)
   InternetCloseHandle lngINetConn
   End If
   InternetCloseHandle lngINet
   End If

   If (blnRC) Then
   MsgBox (“Upload process completed”)
   Else
   MsgBox (“ERROR IN FTP OF FILE!!!!”)
   End If

4. Hi. I’ve been searching and searching and seaching for VBA code (Access 2003) that I can use to do the following:

   transfer FTP files from the mainframe to a local drive adding the extension “.txt” to the local.

   so that I can import into Access 2003 and run all the reports needed.

   Your example is lenghy and I hope I can make since of it. I’m going to try to use it and I PRAY it works! MANY THANKS TO YOU for taking the time to help others like myself!!! I’m sure it was hard work and time consumming!!!

5. Funny you should mention that, Dale; I’ve just been commissioned to automate a procedure that generates some data on the mainframe, saves it to a dataset, then download the data to a PC and join it in an Access query to another table. The mainframe stuff I can do, and I can fumble my way through Access VBA — I’m very much a beginner but getting the hang of it — but I’d almost despaired of the FTP part, fearing I may have to let my users do that part manually.

   If you’re interested in comparing notes and figuring this out together, contact me at rhbridg (at sign) attglobal.net.

6. I am developing a website that track the issue of grants to businesses for going “Green”. I have to upload several images that we use to show Budget-Issued-remaining sums. Your code seemed to be the best solution. However, when I try to compile the project, I get the following error on the “Function GetGifDim(ByVal sFname As String, ByVal eDim As gdGifDims) As Long” function:
   Compile Error: User-defined type not defined
   (Help File reference HV01202536)

   How do I fix this?

   Thanks pws

7. Phil: The first four lines in the above procedure are a custom enumeration. You need to include that enum or the code won’t know what gdGifDims is.

8. Using wininet.dll is fine, but how can you perform a resume with that library? Try it, you can’t as the FtpOpenFile performs two calls instead of the proper one.

9. Wicked, thanks for the code, need for some really old vba app!!!!!

10. Thanks, this is great! But what’s that reference to what “Walkenbach did with MP3s” and extended file properties?

    I need a way to determine the file type of an image without referring to the file extension. I have some images that are GIFs or BMPs but have a JPG extension. Some shopping carts can handle a badly-named image and will display it anyway, but others will show a broken image even though it does exist.

    There are over 50,000 images, way too many to check individually, so if there was some file property I could interrogate that would do the trick.

    Anyone know if there’s some extended file property that could help with this?

11. Pat: I think this is what I was talking about. http://spreadsheetpage.com/index.php/file/mp3_file_lister/

12. Thanks guys this was extremely useful and im sure to use this code.

    Using an ftp client is sometimes a little bit to tedious for me so using your examples up there I can now take a look at putting together something extremely useful for me..

    Thanks again…

13. Hi
    I just found this article explaining how to use wininet.dll. I have always used http://ftp.exe called from at *.bat file.
    It works OK, but I also have the problem with host password. I also have problems using UNC patch.
    But this code WORKS PERFECTLY. Many thanks to Bilkamm and Haines to sharing this code.

    What I though still miss using this code is the response from FTP, espacially if an error occurs. (could be truncation of host dataset, wrong userid/password and so on)

    Using the http://ftp.exe I can pipe the result to a textfile, so I can see how each transfer went.

    Is it possible to get this information from the wininet.dll solution ?

---

Posting code? Use <pre> tags for VBA and <code> tags for inline.