Excel VBA: Download files from the Internet

There is no built-in function in Microsoft Excel which allows you to download contents from the Internet on the fly. To accomplish this task we need to use the API for WinInet. The use and explanation of API in VBA is for advanced users which have prior experience from either Visual Basic 6.0 or .NET.

Pitfalls
It is very important that all open Internet connections are closed as soon as the task is completed. WinInet only allows 2 concurrent connections to a given host. If you forget to shut down the connection after use, you will experience timeouts and misleading error messages. Please refer to the following website for more information related to the maximum allowed concurrent web connections:

• Adjust maximum concurrent connections

Howto
The source code below should be pasted in a "Class Module" in Excel. If you are not sure how to open the VBA editor in Excel for your current Microsoft Office version, please refer to the following page:

• Display the developer toolbar or ribbon in Excel

Create new class module:

1. Open the Microsoft Visual Basic for Applications editor in Excel.
2. Select Insert -> Class Module on the main menubar
3. Rename the new class module to "WebClient"

Example
To use the code, you shold create a new instance of the class and any of the public methods:

• DownloadFile - download a specific resource to a local file
• UrlExists - check if a given URL exists

Dim objClient As New WebClient
Call objClient.DownloadFile("http://www.google.com", "c:\test.html")

Dependencies
The function "ReThrowError" is defined here:

• Re-throw Errors in VBA

Source Code

For the full source code, please visit

Excel VBA: Download files from the internet

Blogger: Free Templates for your Blog

Want to have a proffessional looking blog on blogger.com (blogspot.com)? The available templates for blogs on blogger.com are very limited. You can download free templates from the following sites:

• B Templates
• Blogger Templates Free
• Our Blogger Templates
• eblog Templates

Facebook Dev: Free webhosting for your App

If you're a student and want to learn Facebook App development, or just need a free webhost for your app, you can apply for free webhosting from numerous free webhosting providers.

If you are developing your Facebook App in ASP.NET, check out the list of free webhosts that support ASP.NET and Microsoft SQL Server:

• Free webhosting for ASP.NET

Since there are few licenses related to setting up a webserver running Linux, it is much easier to find a free webhost if your Facebook application does not require a Windows server.

1. 0000cost (1.5 GB storage, 15 GB bandwidth)
2. Free domain site (1 GB storage, unlimited bandwidth)
3. Bammz (unlimited storage and bandwidth)
4. HelioHost (500 MB, unlimted bandwidth)
5. Somee (150 MB storage, 5 GB transfer)
6. No Fee Host (100 MB storage)
7. 7 Host (50 MB storage)
8. Brinkster (30 MB storage, 2 TB bandwidth) 
9. 10k Host (unknown storage and bandwidth)
10. Millenium Systems (only for webdesigners)

Please notice that the webhosts running Windows servers and ASP.NET normally also provide support for PHP and MySQL databases.


You can also get free SSL certificates for your webserver (which is required from October 1st for all Facebook Apps):

•  Free SSL Certificates

Facebook Dev: Choosing a SDK for ASP.NET

When first deciding to create a Facebook App in ASP.NET I was struggling to find a up to date Facebook SDK for .NET. Some of the SDKs contains very low level functions, while others don't support the latest features. When selecting SDK you also have to make sure that the "new" signed_request parameter is used by the framework. From October 1st 2011 all Facebook Apps and Facebook Pages must use this parameter to get information about the current user, page id, access token etc.

At the time of writing, it seems like it is only the offical Facebook SDK at CodePlex which is satisfying all the requirements when it comes to Facebook Development in .NET.
Unfortunately, the SDK contains a lot of bugs, and is very hard to debug if you happens to find one.

Personally I was forced to write most of my Facebook code from scratch without using the Facebook SDK due to all the bugs.

You can find the documentation of the Facebook API at the Developer area.

MS SQL: Store Color (ARGB) as integer

The most common way is to store colors in a SQL database as a string, i.e. the HTML color code (ex. #FFFFFF). The colors can also be stored as 32 bit integers. A 32 bit color value (including alpha channel) can be calculated in the following way:

@Alpha * 16777216 + @Red * 65536 + @Green * 256 + @Blue


The full userdefined function is as follows:


-- Description: Generate ARGB Color value
-- =============================================
CREATE FUNCTION [dbo].[sudf_Color_FromArgb]
(
 @intAlpha tinyint,
 @intRed  tinyint,
 @intGreen tinyint,
 @intBlue tinyint
)
RETURNS int
AS
BEGIN

declare @lngColor bigint;
declare @intColor int;

 -- Generate the color
 set @lngColor = (
      (cast(@intAlpha as bigint) * 16777216) +
      (cast(@intRed as bigint) * 65536) +
      (cast(@intGreen as bigint) * 256)  +
      (cast(@intBlue as bigint))
     );

 -- Get the color
 set @intColor = cast(cast(@lngColor as binary(4)) as int);

 -- Return the color
 return @intColor;
END