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

Skip Clipboard during VBA.. operation

Recently i came across an error in Excel 2007 as

"Picture is too large and will be truncated."

Upon looking into the issue, i found that leaving stuff on the clipboard, used during the copy and paste operations within VBA was the culprit. This error occurred when the file was about to be closed by the user.
                                After digging in to many portals and unsuccessful attempts to resolve the error by clearing the clipboard via VBA, I found one way to fix the code by redesigning my VBA codes to skip the use of copy and paste and stick with the range based value transfers. Which later i realised is more robust and better way of doing things.

The simplest of the solutions to completed the copy and paste task is

DestinationRange.Value = SourceRange.Value
'with the format code later.

And the solution for the elimination of formulas and paste as values could be gained by

SelectionRange.Formula = SelectionRange.Value

As the office clipboard is now provided in task panes section its quite unclear as of now which solutions can clear the clipboard in VBA.

And to add upon the article

Application.CutCopyMode = False 

doesnt work to clear the office clipboard.