HTTP File Download using VBA (Password Protected Site)

Recently I embarked on a project involving the file download from a password protected site (http location), though with some troubles to get the Internet Transfer Controls setup on my PC. I found the following solution amazingly brilliant to complete the task.

The following solution uses the Internet Transfer Control object to navigate to the http location using the authentication protocols and details and obtains the files in byte format which is then placed at the location of your choice using the file operation in VBA.

The code is illustrated as follows:

Sub HTTPDownloadFile(ByVal URL As String, ByVal LocalFileName As String)
    Dim HTTP As Inet
    Dim Contents() As Byte

    Set HTTP = New Inet
    With HTTP
        .Protocol = icHTTP
        .URL = URL
        .UserName = "**UserName**"
        .Password = "**Password**"
        Contents() = .OpenURL(.URL, icByteArray)
    End With
    Set HTTP = Nothing

    Open LocalFileName For Binary Access Write As #1
    Put #1, , Contents()
    Close #1
End Sub

Note: To install the Internet Transfer Control for the Vista x64 , Just copy and paste the "msinet.ocx" file into the "C:\Windows\SysWow64" folder and run the "regsvr32" which should successfully install the Internet transfer control on your system.


References:
Office One: http://officeone.mvps.org/vba/http_download_file.html

SubVersion : VBA Code (Export VBA to text files..)

I would like to wish all my blog readers a very happy and a prosperous New Year. I am posting this post after a while, as recently in the past month I was on my new career move in a new role.
Recently upon joining, I had the opportunity to design the office application setup on board the department. Among which one of the key issue came by was to maintain the VBA code in subversion and use subversion capabilities of code compare to its fullest which requires code to be in clear text file. And as XL save as everything in binary it was little tough to code compare the binaries.. !!

So the solution was to design a tool to export all the VBA code modules in text format which could be easily then imported back into excel.

The attached tool takes in the path of the folder from where the source files of excel whose code needs to be extracted, along with the path navigating the destination location for the code exported to be saved. And clicking on the “Extract Code..” iterates all the excel files present in the source folder location and correspondingly creates folder in the destination location with the exported set of code modules within the relevant corresponding folders in relation to the source file names.

Hope this tool helps you to, utilize the full potential of Sub versioning VBA…



Download solution