Keeping static rows in a query table matched to the correct row from the database query in Excel

The Problem

If you create a query in excel, which then creates a table object, and then you add columns to the excel query table that are not part of the query. When you refresh the query the static information stored in the extra columns can become mismatched with the information returned in the query.

For Example if i have this table

order_id	product	comment (static column)
1	apple
3	strawberry	goes well with cream

And then refresh the query...

order_id	product	comment (static column)
1	apple
2	banana	goes well with cream
3	strawberry

An extra row appears in the middle, we would then have the table saying that bananas go well with cream (when clearly that is crazy)

Workaround

To get around this problem you can use the before and after refresh events on the QueryTable object in Excel

Things to do

1. Setup QueryTable events for before and after refresh of your query
2. Create a hidden sheet in your workbook where you can keep a copy of the table

Setting up the QueryTable events

This is how to setup the events http://msdn.microsoft.com/en-us/library/office/aa221596(v=office.11).aspx

Process Flow

1. In the beforerefresh event, copy the current table to the hidden sheet
2. Allow the data to refresh from the query
3. Clear out the static columns in the newly refreshed data
4. Loop through the hidden sheet table and match on a unique key to find which rows to populate your static data