How to get the number of columns in an Excel-Query?

[Former Member]

hi,

I would like to implement a formatted header in all bw-querys according to the corporate design by using a template workbook. By now nearly everything works perfect...but I have some issues with the header of the querys.

The header is coloured in blue with white text and should span across all columns in excel that contain values. Currently the header is coloured (fix) from column A to E - this looks good as long as there are max. five columns.

If there are more than five columns, the header does not match to the size of the query-results.

Is it possible to colourize the header (columns) according to the number of columns in the result table?

Thanks in advance,

Chris

[Former Member]

This can be done easily with VB by using the property "CurrentRegion" to retrieve the entire results area, as shown in the following example:

     Set RptRng = ActiveCell.CurrentRegion

After that, you can find the number of columns with the property "RptRng.Columns.Count", for example.

Hope this helps...

Bob