執行階段錯誤13，型態不符合與sql語法指點

joey0415

小弟最近在寫sql語法，最後要代入excel vba中, 前面的一段code可以用，不過改成另一最sql的語句卻發生錯誤，不過這一段語句在sql server中是可以執行的，在vba執行中出現的是「執行階段錯誤13，型態不符合」，請高手指點一下，另一個問題是當語句很長時怎麼換行呢？

1. Sub 股票合併測試()
   
2. '
   
3. 
   
4.     Sheets("Sheet4").Select
   
5.     Range("A1").Select
   
6.     With ActiveSheet.QueryTables.Add(Connection:= _
   
7.         "ODBC;DRIVER=SQL Server;SERVER=.;UID=sa;PWD=wxyz1234;APP=Microsoft Office 2003;WSID=CS5111;DATABASE=stock" _
   
8.         , Destination:=Range("A1"))
   
9.         .CommandText = Array("select a.sdate,a.stockid,a.buy,a.sell,b.sdate,b.stockid,b.eq0,b.eq1 from dbo.CompTradeMonth a inner join dbo.StockStore b  on  a.stockid=b.stockid  where    a.sdate='2010-03-31 00:00:00.000' and b.sdate='2010-03-31 00:00:00.000' and a.stockid>1000")
   
10.         .Name = "來自 "
    
11.         .FieldNames = True
    
12.         .RowNumbers = False
    
13.         .FillAdjacentFormulas = False
    
14.         .PreserveFormatting = True
    
15.         .RefreshOnFileOpen = False
    
16.         .BackgroundQuery = True
    
17.         .RefreshStyle = xlInsertDeleteCells
    
18.         .SavePassword = True
    
19.         .SaveData = True
    
20.         .AdjustColumnWidth = True
    
21.         .RefreshPeriod = 0
    
22.         .PreserveColumnInfo = True
    
23.         .Refresh BackgroundQuery:=False
    
24.     End With
    
25. 
    
26. End Sub

複製代碼

1. Sub 股票合併測試()
   
2. '
   
3. 
   
4.     Sheets("Sheet4").Select
   
5.     Range("A1").Select
   
6.     With ActiveSheet.QueryTables.Add(Connection:= _
   
7.         "ODBC;DRIVER=SQL Server;SERVER=.;UID=sa;PWD=wxyz1234;APP=Microsoft Office 2003;WSID=CS5111;DATABASE=stock" _
   
8.         , Destination:=Range("A1"))
   
9.         .CommandText = Array("select a.sdate,a.stockid,a.eq0,a.eq1,a.eq5,a.eq10,a.eq15,a.eq20,a.eq30,a.eq50,a.eq100,a.eq200,a.eq400,a.eq600,a.eq800,a.eq1000,a.eq,b.sdate,b.stockid,b.Buy,b.Sell,b.UD,b.EQ from dbo.StockStore a inner join dbo.CompTradeMonth b  on  a.stockid=b.stockid  where    a.sdate='2010-03-31 00:00:00.000' and b.sdate='2010-03-31 00:00:00.000' and a.stockid>1100")
   
10.         .Name = "來自 "
    
11.         .FieldNames = True
    
12.         .RowNumbers = False
    
13.         .FillAdjacentFormulas = False
    
14.         .PreserveFormatting = True
    
15.         .RefreshOnFileOpen = False
    
16.         .BackgroundQuery = True
    
17.         .RefreshStyle = xlInsertDeleteCells
    
18.         .SavePassword = True
    
19.         .SaveData = True
    
20.         .AdjustColumnWidth = True
    
21.         .RefreshPeriod = 0
    
22.         .PreserveColumnInfo = True
    
23.         .Refresh BackgroundQuery:=False
    
24.     End With
    
25. 
    
26. End Sub

複製代碼

joey0415

請問.CommandText = Array("select a.*,b.*,c.*,d.* from dbo.StockStore a left join dbo.StockMonth b on a.stockid=b.stockid left join dbo.CompTradeMonth c on a.stockid=c.stockid left join dbo.CreditExchangeMonth d on a.stockid=d.stockid   where   a.stockid>1100 and a.sdate='2010-03-31 00:00:00.000' and b.sdate='2010-03-31 00:00:00.000' and c.sdate='2010-03-31 00:00:00.000' and d.sdate='2010-03-31 00:00:00.000'")
陣列中的值是不是要相同型態的，不然怎會「執行階段錯誤13」，請問怎麼改成相同型態呢？

請高手指導一下？

謝謝