none
Add query table on formated cells manually and by VBA RRS feed

  • Question

  • I have a "template" sheet with formatting. My VBA will copy the template sheet and add a query table. 

    I and users will do formatting on the template sheet, for example Column B is yellow, width is 10, etc. The new copied sheet should preserve all formatting.

    Here comes the question.

    First, I set Column C to be yellow, then I add a query table to A10. The yellow background is preserved after the table is inserted.

    Then I record a marco of this action. The recorded marco is 

        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="ODBC;DSN=NBA;", _
            destination:=Range("$A$10")).queryTable
            .CommandText = Array("SELECT * FROM `nba`.`game`")
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            '.SourceConnectionFile = "D:\Documents\我的数据源\nba game.odc"
            '.ListObject.DisplayName = "表_nba_game"
            .Refresh BackgroundQuery:=False
        End With

    But if I run the macro instead of doing manually, the background loses, as Figure 2.

    How do I modify the code so that the formatting can be preserved after the table is inserted? 

    Thursday, July 3, 2014 12:33 AM