4,008
社区成员
发帖
与我相关
我的任务
分享
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