请教如何设置Querytable的TableStyle

EisenWang 2012-05-16 02:06:02

我最近使用Querytable从数据库里提取数据生成一个excel，于是我录制了一个宏，发现里面是这样的——



Sub Macro1()

'

' Macro1 Macro

'



'

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="ODBC;DSN=PROD;", _

        Destination:=Range("$A$1")).QueryTable

        .CommandText = Array( _

        "SELECT * FROM fawpmpr0.dbo.v_F3_Today order by STIN_BEG_DTM")

        .RowNumbers = False

        .FillAdjacentFormulas = False

        .PreserveFormatting = True

        .RefreshOnFileOpen = False

        .BackgroundQuery = True

        .RefreshStyle = xlInsertDeleteCells

        .SavePassword = False

        .SaveData = True

        .AdjustColumnWidth = True

        .RefreshPeriod = 0

        .PreserveColumnInfo = True

        .ListObject.DisplayName = "表_fawpmpr0_v_F3_Today"

        .Refresh BackgroundQuery:=False

    End With

    ActiveSheet.ListObjects("表_fawpmpr0_v_F3_Today").TableStyle = _

        "TableStyleMedium10"

    ActiveSheet.ListObjects("表_fawpmpr0_v_F3_Today").TableStyle = _

        "TableStyleMedium9"

    Range("G7").Select

End Sub

于是我就照样写了一个——



'Save this file as t.vbs

'cscript t.vbs



dim oExcel,oWb,oSheet 

Set oExcel= CreateObject("Excel.Application") 

Set oWb = oExcel.Workbooks.Add

oExcel.DisplayAlerts = false 

oExcel.AlertBeforeOverwriting = false 

CreateQT()



oWb.SaveAs "d:\temp\test.xls",1

oWb.Close False

oExcel.Quit





Sub CreateQT()



'    Dim sConn As String

'    Dim sSql As String

'    Dim oQt As QueryTable

     Dim sConn,sSql,oQt



    sConn = "ODBC;DSN=PROD;"



    sSql = "select @@servername servername,'2012/05/12' Today"

    WScript.Echo sSql

   

   Set oQt = oExcel.Activesheet.QueryTables.Add(sConn, oExcel.Activesheet.Range("A1"))

   oQT.CommandText = Array(sSql)

   oQT.RowNumbers = False

   oQT.FillAdjacentFormulas = False

   oQT.PreserveFormatting = True

   oQT.RefreshOnFileOpen = False

   oQT.BackgroundQuery = True

   oQT.RefreshStyle = xlInsertDeleteCells

   oQT.SavePassword = True

   oQT.SaveData = True

   oQT.AdjustColumnWidth = True

   oQT.RefreshPeriod = 0

   oQT.PreserveColumnInfo = True

   oQT.SourceConnectionFile = ""

   oQT.Name = "Conn"

   oQT.Refresh BackgroundQuery=True

'   oQT.ListObject.TableStyle = "TableStyleMedium9"

'   oExcel.Activesheet.ListObjects("Conn").TableStyle = "TableStyleMedium9"

'   oExcel.Activesheet.ListObjects.Add(xlSrcRange,false,oExcel.Activesheet.Range("A1"),"TableStyleMedium9")   



End Sub