Имеется ли ограничение на длину ".CommandText" при создании ListObjects? Если переменные <короткие> - работает, < удлиняешь > - Error 13, Type Mismatch. Пробовал изменять MaxBufferSize - не помогло.
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DRIVER=SQL Server;SERVER=" & s_Server_Name & ";UID=" & s_User_ID & ";Pwd=" & s_Password & ";APP=2007 Microsoft Office system;MaxBufferSize=8192;WSID=V-SRV2003;DATABASE=" & s_Database_Name & "" _
, Destination:=Range("$A$1")).QueryTable
.CommandText = Array( _
"SELECT " & v_SQL & " FROM vAllPrS_5Reg" & v_SQL_HL & "_" & v_Year & v_Plan & " " _
, " WHERE (PERIOD>=" & v_PeriodFrom & " And PERIOD<=" & v_PeriodTo & ") " _
, " " & v_Where_ID & v_Where_StoreB & v_Where_StoreE & " ORDER BY PERIOD")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query_from_CitiTrends_VG"
.Refresh BackgroundQuery:=False
End With
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DRIVER=SQL Server;SERVER=" & s_Server_Name & ";UID=" & s_User_ID & ";Pwd=" & s_Password & ";APP=2007 Microsoft Office system;MaxBufferSize=8192;WSID=V-SRV2003;DATABASE=" & s_Database_Name & "" _
, Destination:=Range("$A$1")).QueryTable
.CommandText = Array( _
"SELECT " & v_SQL & " FROM vAllPrS_5Reg" & v_SQL_HL & "_" & v_Year & v_Plan & " " _
, " WHERE (PERIOD>=" & v_PeriodFrom & " And PERIOD<=" & v_PeriodTo & ") " _
, " " & v_Where_ID & v_Where_StoreB & v_Where_StoreE & " ORDER BY PERIOD")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query_from_CitiTrends_VG"
.Refresh BackgroundQuery:=False
End With
vladimir