Open a website via VBA

Sometimes you may want to obtain data from an online source and import it into a spreadsheet.  This is easy to do, using the following statement:

Workbooks.Open Filename:="URL"

This statement opens the website in a spreadsheet, meaning that you can then use the same VBA commands as on any spreadsheet.  For example, if you want to obtain a number from the website (for example a stockquote), you can reference the cell containing the number required.  In order to find out which cell you must reference in the code, you must first open the website in Excel (using the command above) and find the correct cell in the spreadsheet.  Once you have the correct cell reference, you can then automatically open the website in a spreadsheet, store the number in a variable, and then close the website/spreadsheet.  Close the website with this statement:

Windows("Website Title").Close savechanges:=False

Note that to determine the Website Title, you must again use the method of opening the website in a spreadsheet; then look at the "file name" (displayed in the Excel header bar at the very top of the window).

For Loops

Like Do While and Do Until loops, For loops are used to repeat a block of code until (or while) a condition is met.  The syntax is:

For variable = Start To End
    Code to be repeated
Next

A For loop can also be used with the Step statement to alter the increment used.  For example, the code below increments the variable x from 0 to 10 in steps of 2, i.e. x = 0, 2, 4, 6, 8, 10.  Without the Step statement, the code would set x to 0, 1, 2, 3, ... 10.

For x = 0 To 10 Step 2
    Code to be repeated
Next

Note that the Step statement can also be used to run through a For loop "backwards":

For x = 10 To 0 Step -2

    Code to be repeated
Next

The Exit For statement can be used to exit a For loop before the end condition is met.  For example:

For x = 0 To 10 Step 2

    If intNum = 25 Then Exit For

Next