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

'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

Const sPATH As String = "C:\Users\dick\"
Const sSITE As String = "ftp.mysite.com"
Const sUSER As String = "My User Name"
Const sPASS As String = "My Password"
Const sDIR As String = "www\blogpix\"

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 ?

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax