Using Microsoft Office for Mac as a Relational Database

By Jim Gordon, co-author of Office 2011 for Mac All-in-One For Dummies.

Part 22 - Make a query table with VBA and add calculated columns

Excel can automatically fill in and update columns adjacent to a query table result set if the adjacent rows contain cell formulas based on data returned from a query. That means if your source database changes and you refresh the query, when the new record set is returned, all the adjacent cell formulas will calculate new values for the corresponding rows in the result set, and the number of rows in the adjacent columns will adjust automatically.

You can make a query from scratch using VBA. Let's put these two features to the test.

1. Open a new, blank workbook.
2. In the VB Editor, in a new module type or paste the following macro:

Sub MakeAQuery()
Dim sqlstring As String
Dim connstring As String
sqlstring = "SELECT Products.ProductName, Products.UnitsInStock, Products.UnitsOnOrder, Products.ReorderLevel, Products.Discontinued FROM Products WHERE (Products.ProductName like '%chestnut%')"
connstring = "ODBC;DSN=ExampleData"
With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)
   .BackgroundQuery = False
   .HasAutoFormat = True
    .FillAdjacentFormulas = True
    .UseListObject = True
End With
With ActiveSheet
    .ListObjects("Table1").TableStyle = "TableStyleMedium10"
End With
End Sub

3, Run the macro.
The result set should look like this screen shot:

Step 3

4. Click into the cell immediately to the right of the field headers. In this example this cell is F1, as shown in the screen shot above.
Type a new field name in the selected cell. In this example the new field name will be TotalAvailable, which will be the sum of UnitsInStock plus UnitsOnOrder. When you press the Return key on your keyboard, notice that the formatting fills down, as shown below.

Step 5

6. Do not click anywhere. You must immediately Press the = key to begin a cell formula. Your formula starts with =VALUE(
Stop typing. Before continuing, know that all of the records in the Products table in ExampleData.xls were stored as text, so our cell formula will treat these values as text.
7. Look at the screen shot below. Build your cell formula arguments by clicking into cells B2 and C2. Notice that Excel builds the formula using the column headers rather than cell references. This is pretty neat. Excel calls this a "calculated column."

Step 7

8. Press return and Excel will automatically fill the formula down. This is very handy if you have thousands or hundreds of thousands of records to work with.

Finished. Step 8.

If data in the data source changes and has been saved, when you refresh your query the adjacent column's calculated formulas will recalculate based on the new data and adjust to the new row count. The formatting of the first record in the query table will copy down. Sorry, the fancy every other row formatting won't be retained unless you do more coding. You can add as many additional columns as you want to your query table this way. Remember, the name of the table increments each time you refresh the query.

To obtain a VBA code example of making a calculated column, click the Record Macro button on the Developer Tab of the Ribbon between steps 3 and 4 above. Click the Stop Recording button after step 8. The macro recorder will record the code you need.

Here's an example of a connection string that was used to connect to a Microsoft SQl Server
ConnectionString = "ODBC;DSN=MyDataSourceName;DATABASE=MyDatabaseName;SERVER="";PORT=5432;UID=MyUserName"

End of tutorial. Click here to return to Part 1.