This project has moved. For the latest updates, please go here.

Excel: Apply a TableStyle to a QueryTable

May 21, 2014 at 5:03 PM
Edited May 21, 2014 at 5:07 PM
I am trying to get a data from a database into Excel and apply a table style to it in the most quickest time; whilst still using NetOffice for the interoperability it offers. I assume that using Excel's inbuilt query functions will be far faster so I am using that (please correct me if I am wrong on this)

I can use a query table:
var sheet = (_excel.ActiveSheet as Excel.Worksheet);
var rng = sheet.Range("A1");

var qt = sheet.QueryTables.Add("ODBC;...", rng, "SELECT * FROM myTable");
qt.Refresh();
and this will import the data correctly (e.g. dates actually display as dates etc...). But when I try and access the ListObject to apply a TableStyle I get an exception.

Now if I add a list object like:
var sheet = (_excel.ActiveSheet as Excel.Worksheet);

var qt = sheet.ListObjects.Add(
    Excel.Enums.XlListObjectSourceType.xlSrcQuery,
    "ODBC;...",
    null,
    Excel.Enums.XlYesNoGuess.xlNo,
    rng,
    "TableStyleMedium9").QueryTable;

qt.CommandType = Excel.Enums.XlCmdType.xlCmdSql;
qt.CommandText = "SELECT * FROM myTable";
qt.Refresh();
The the dates in the query display as decimal numbers and not dates...

I could just format the columns afterwards, but the problem is that I won't actually know the query that is being run as the user types this at runtime, so I would prefer to get Excel to do this.

So essentially, what I want, is to use the first bit of code and apply a TableStyle to it.

Can anyone help?
May 22, 2014 at 9:25 AM
So I can see that when I create a list object that the NumberFormat of the whole range is set to "General"....however, on the QueryTable (which has the correct number formatting), it is null which means that the range has different formats according to the documentation.

So my question is, how do I get the ListObject to take the NumberFormat from query like QueryTable does.
May 22, 2014 at 5:05 PM
I've just realised I can record a macro to record what happens when I use the GUI:
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "ODBC;..." _
        , Destination:=Range("$A$1")).QueryTable
        .CommandText = Array("SELECT * FROM myTable")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_myTable"
        .Refresh BackgroundQuery:=False
    End With
However, I can't seem to replicate it via NetOffice and get the same result?
            var sheet = (_excel.ActiveSheet as Excel.Worksheet);
            var rng = sheet.Range("$A$1");

            var lo = sheet.ListObjects.Add(
                0,
                "ODBC;...",
                Missing.Value,
                Excel.Enums.XlYesNoGuess.xlNo,
                rng);

            var qt = lo.QueryTable;
            qt.CommandText = new[] { "SELECT TOP 1 * FROM myTable" };
            qt.RowNumbers = false;
            qt.FillAdjacentFormulas = false;
            qt.PreserveFormatting = true;
            qt.RefreshOnFileOpen = false;
            qt.BackgroundQuery = true;
            qt.RefreshStyle = Excel.Enums.XlCellInsertionMode.xlInsertDeleteCells;
            qt.SavePassword = false;
            qt.SaveData = true;
            qt.AdjustColumnWidth = true;
            qt.RefreshPeriod = 0;
            qt.PreserveColumnInfo = true;
            qt.ListObject.DisplayName = "Table_myTable";
            qt.Refresh();