Всем привет. Сегодня столкнулся с неожиданной проблемой. Формирую запрос из VBA на выборку данных. Некоторые данные задаются в условие IN (коды товаров). Это текстовая строка с числами через запятую. Так вот когда эта строка превышает определенную длину (подозреваю что 256 символов), запрос не формируется, vba возвращается ошибку Type mismacht 13. Хотя эту переменную как String не объявлял. Интересно то что в режиме Excel свойства запроса съедают и более длинные строки. Но это уже вручную.
Как победить?
Всем привет. Сегодня столкнулся с неожиданной проблемой. Формирую запрос из VBA на выборку данных. Некоторые данные задаются в условие IN (коды товаров). Это текстовая строка с числами через запятую. Так вот когда эта строка превышает определенную длину (подозреваю что 256 символов), запрос не формируется, vba возвращается ошибку Type mismacht 13. Хотя эту переменную как String не объявлял. Интересно то что в режиме Excel свойства запроса съедают и более длинные строки. Но это уже вручную.
Запрос ниже. Сначала накидываем в переменную значения с листа. затем это подсовывается в сам запрос. На 20-30 значениях работает без проблем. Больше - выдает ошибку.
[vba]
Код
'формируем строку для условия iN из данных в ячейках
lastrow = Cells(Rows.Count, 25).End(xlUp).Row avData = Range("Y2:Y" & lastrow).Value For lc = 1 To UBound(avData, 2) For lr = 1 To UBound(avData, 1) If Len(avData(lr, lc)) Then sRes = sRes & "," & avData(lr, lc) End If Next lr Next lc If Len(sRes) Then sRes = Mid(sRes, Len(",") + 1) End If nnum = ActiveSheet.Range("X1") ndate = ActiveSheet.Range("X1") ActiveSheet.Range("A:U").Clear
Запрос ниже. Сначала накидываем в переменную значения с листа. затем это подсовывается в сам запрос. На 20-30 значениях работает без проблем. Больше - выдает ошибку.
[vba]
Код
'формируем строку для условия iN из данных в ячейках
lastrow = Cells(Rows.Count, 25).End(xlUp).Row avData = Range("Y2:Y" & lastrow).Value For lc = 1 To UBound(avData, 2) For lr = 1 To UBound(avData, 1) If Len(avData(lr, lc)) Then sRes = sRes & "," & avData(lr, lc) End If Next lr Next lc If Len(sRes) Then sRes = Mid(sRes, Len(",") + 1) End If nnum = ActiveSheet.Range("X1") ndate = ActiveSheet.Range("X1") ActiveSheet.Range("A:U").Clear
'формируем строку для условия iN из данных в ячейках
lastrow = Cells(Rows.Count, 25).End(xlUp).Row avData = Range("Y2:Y" & lastrow).Value For lc = 1 To UBound(avData, 2) For lr = 1 To UBound(avData, 1) If Len(avData(lr, lc)) Then sRes = sRes & "," & avData(lr, lc) End If Next lr Next lc If Len(sRes) Then sRes = Mid(sRes, Len(",") + 1) End If nnum = ActiveSheet.Range("X1") ndate = ActiveSheet.Range("X1") ActiveSheet.Range("A:U").Clear
'запрос sCon = "Provider=SQLOLEDB.1;Data Source=D-030\CENSUM;Initial Catalog=CnsReports;User ID=access;Password=access;Persist SecurityInfo=True" Set Cn = CreateObject("ADODB.Connection") Cn.Open sCon CommandText = "Select * FROM [Компенсация скидки] WHERE docdate IN(" & sRes1 & ") AND [Поставщик] = '" & ndate & "' AND korkod IN (" & sRes & ")" Set rs = Cn.Execute(CommandText) With ActiveSheet For n = 0 To rs.Fields.Count - 1 .Cells(1, n + 1) = rs.Fields(n).Name Next .Cells(2, 1).CopyFromRecordset rs Cn.Close Set Cn = Nothing Set rs = Nothing End With
[/vba] Подредактировал ячейку вставки,иначе затрет название столбцов
Попробуйте такой вариант,пройдет или нет[vba]
Код
'формируем строку для условия iN из данных в ячейках
lastrow = Cells(Rows.Count, 25).End(xlUp).Row avData = Range("Y2:Y" & lastrow).Value For lc = 1 To UBound(avData, 2) For lr = 1 To UBound(avData, 1) If Len(avData(lr, lc)) Then sRes = sRes & "," & avData(lr, lc) End If Next lr Next lc If Len(sRes) Then sRes = Mid(sRes, Len(",") + 1) End If nnum = ActiveSheet.Range("X1") ndate = ActiveSheet.Range("X1") ActiveSheet.Range("A:U").Clear
'запрос sCon = "Provider=SQLOLEDB.1;Data Source=D-030\CENSUM;Initial Catalog=CnsReports;User ID=access;Password=access;Persist SecurityInfo=True" Set Cn = CreateObject("ADODB.Connection") Cn.Open sCon CommandText = "Select * FROM [Компенсация скидки] WHERE docdate IN(" & sRes1 & ") AND [Поставщик] = '" & ndate & "' AND korkod IN (" & sRes & ")" Set rs = Cn.Execute(CommandText) With ActiveSheet For n = 0 To rs.Fields.Count - 1 .Cells(1, n + 1) = rs.Fields(n).Name Next .Cells(2, 1).CopyFromRecordset rs Cn.Close Set Cn = Nothing Set rs = Nothing End With
[/vba] Подредактировал ячейку вставки,иначе затрет название столбцовdoober
Сообщение отредактировал doober - Понедельник, 18.04.2016, 20:42
Отлично. Работает. Все возвращает. Почему в умную таблицу не хотел? Проверил получаемую строку через MsgBox - там видно как ее обрезает, видимо и в запросе обрезал, с ошибкой. Видимо какие-то внутренние ограничения Excel? Пишет в String даже если ее не объявляешь?
Отлично. Работает. Все возвращает. Почему в умную таблицу не хотел? Проверил получаемую строку через MsgBox - там видно как ее обрезает, видимо и в запросе обрезал, с ошибкой. Видимо какие-то внутренние ограничения Excel? Пишет в String даже если ее не объявляешь?IgorStorm