FTP Via VBA

One of my new year’s resolutions was to write a procedure to ftp picture files to this blog to simplify the procedure. It took two days, but I finally got it done. I don’t know if it’s well done, but it works. I got a little help along the way, specifically:

Forestasia has code to determine the image size. Another resolution is to put height and width arguments in my img tags. I tried about 17 other things before finding this page, such as looking at the extended file properties like Walkenbach did with MP3s. I’m glad I found it, though, because I learned something about the GIF file format. Nice use of Get.

Speaking of that, I read the GIF89a file specification to see what else I could learn. I had a little trouble applying the spec to the bits (which I printed to a worksheet for inspection). For one, it was clear from Forestasia’s code that the dimensions were stored in two bits; one with size mod 256 and the other with the number of full 256’s, but I couldn’t find that in the spec. I was going to make an all white GIF and an all black GIF and compare the bits, but I lost interest.

For the FTP stuff, I went to bygsoftware. I read about ftp subcommands in Windows help, but all my attempts to change bygsoftware’s code failed. It ended up looking pretty much like they have it.

Finally, since I can never remember how to stuff text into the clipboard, I went back (as always) to Chip’s Clipboard page. Forms 2.0. Oh yeah, now I remember.

The code is a bit long, but there’s some good stuff in there.

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

What kind of dink would I be if I didn’t actually have a picture on this post? Here’s the first part of the 12,365 bits of GIF file on my computer. The bits actually start at 1 (if you’re using the Get Statement), but the array they were stored in was zero based.

Excel range listing first few bits of GIF format

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

  8. 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?

  9. 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…

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

Leave a Reply

Your email address will not be published.