一般情况:
cnn = "OLEDB;Provider=IBMDA400;Data Source=TFB4001;User ID=;Password=;"
Sql = "select * from NON"
Set sh = ThisWorkbook.Worksheets("sheet1")
sh.QueryTables.Add(cnn, Range("A1"), Sql).Refresh
保证中文无乱码:
With ActiveSheet.QueryTables.Add(Array("ODBC;Driver={SQL Server};Server=192.168.1.250;UID=rptuser;PWD=rptuser;DATABASE=SUNDB;AutoTran", "slate=No"), Range("A1"))
.CommandText = "SELECT "
.Name = "Query from HKGCPSRV3"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh
End With