一般情况：

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