Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Не формируется SQL запрос из VBA - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Не формируется SQL запрос из VBA (Макросы/Sub)
Не формируется SQL запрос из VBA
IgorStorm Дата: Понедельник, 18.04.2016, 18:54 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 29
Репутация: 1 ±
Замечаний: 0% ±

Excel 2007
Всем привет. Сегодня столкнулся с неожиданной проблемой. Формирую запрос из VBA на выборку данных. Некоторые данные задаются в условие IN (коды товаров). Это текстовая строка с числами через запятую. Так вот когда эта строка превышает определенную длину (подозреваю что 256 символов), запрос не формируется, vba возвращается ошибку Type mismacht 13. Хотя эту переменную как String не объявлял.
Интересно то что в режиме Excel свойства запроса съедают и более длинные строки. Но это уже вручную.

Как победить?
 
Ответить
СообщениеВсем привет. Сегодня столкнулся с неожиданной проблемой. Формирую запрос из VBA на выборку данных. Некоторые данные задаются в условие IN (коды товаров). Это текстовая строка с числами через запятую. Так вот когда эта строка превышает определенную длину (подозреваю что 256 символов), запрос не формируется, vba возвращается ошибку Type mismacht 13. Хотя эту переменную как String не объявлял.
Интересно то что в режиме Excel свойства запроса съедают и более длинные строки. Но это уже вручную.

Как победить?
Автор - IgorStorm
Дата добавления - 18.04.2016 в 18:54
doober Дата: Понедельник, 18.04.2016, 19:17 | Сообщение № 2
Группа: Друзья
Ранг: Ветеран
Сообщений: 947
Репутация: 323 ±
Замечаний: 0% ±

Excel 2010
Здравствуйте.
До 4 килл пройдет запрос без проблем.
Надо код VBA смотреть и как в дебагере выглядит сам текст запроса.

 
Ответить
СообщениеЗдравствуйте.
До 4 килл пройдет запрос без проблем.
Надо код VBA смотреть и как в дебагере выглядит сам текст запроса.
Автор - doober
Дата добавления - 18.04.2016 в 19:17
IgorStorm Дата: Понедельник, 18.04.2016, 19:51 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 29
Репутация: 1 ±
Замечаний: 0% ±

Excel 2007
Запрос ниже. Сначала накидываем в переменную значения с листа. затем это подсовывается в сам запрос. На 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

'запрос
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=SQLOLEDB.1;Data Source=D-030\CENSUM;Initial Catalog=CnsReports;User ID=access;Password=access" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array( _
        "Select *" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "FROM [Компенсация скидки]" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "WHERE docdate  IN(" & sRes1 & ") AND [Поставщик] = '" & ndate & "' AND korkod IN (" & sRes & ")")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Таблица_CnsReports_1"
        .Refresh BackgroundQuery:=False
    End With
[/vba]
 
Ответить
СообщениеЗапрос ниже. Сначала накидываем в переменную значения с листа. затем это подсовывается в сам запрос. На 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

'запрос
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=SQLOLEDB.1;Data Source=D-030\CENSUM;Initial Catalog=CnsReports;User ID=access;Password=access" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array( _
        "Select *" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "FROM [Компенсация скидки]" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "WHERE docdate  IN(" & sRes1 & ") AND [Поставщик] = '" & ndate & "' AND korkod IN (" & sRes & ")")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Таблица_CnsReports_1"
        .Refresh BackgroundQuery:=False
    End With
[/vba]
Автор - IgorStorm
Дата добавления - 18.04.2016 в 19:51
doober Дата: Понедельник, 18.04.2016, 20:30 | Сообщение № 4
Группа: Друзья
Ранг: Ветеран
Сообщений: 947
Репутация: 323 ±
Замечаний: 0% ±

Excel 2010
Попробуйте такой вариант,пройдет или нет[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 - Понедельник, 18.04.2016, 20:42
 
Ответить
СообщениеПопробуйте такой вариант,пройдет или нет[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
Дата добавления - 18.04.2016 в 20:30
IgorStorm Дата: Понедельник, 18.04.2016, 21:06 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 29
Репутация: 1 ±
Замечаний: 0% ±

Excel 2007
Отлично. Работает. Все возвращает. Почему в умную таблицу не хотел? Проверил получаемую строку через MsgBox - там видно как ее обрезает, видимо и в запросе обрезал, с ошибкой. Видимо какие-то внутренние ограничения Excel? Пишет в String даже если ее не объявляешь?
 
Ответить
СообщениеОтлично. Работает. Все возвращает. Почему в умную таблицу не хотел? Проверил получаемую строку через MsgBox - там видно как ее обрезает, видимо и в запросе обрезал, с ошибкой. Видимо какие-то внутренние ограничения Excel? Пишет в String даже если ее не объявляешь?
Автор - IgorStorm
Дата добавления - 18.04.2016 в 21:06
Gustav Дата: Понедельник, 18.04.2016, 22:16 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2744
Репутация: 1137 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Цитата IgorStorm, 18.04.2016 в 21:06, в сообщении № 5
Почему в умную таблицу не хотел?

Попробуйте в своем первоначальном варианте просто текстовую строку, без функции Array:
т.е. вместо
[vba]
Код
.CommandText = Array("Select...")
[/vba]
просто
[vba]
Код
.CommandText = "Select..."
[/vba]

ЛИБО с функцией Array, но поделив исходную строку на несколько частей длиной не более 255 символов каждая:
[vba]
Код
.CommandText = Array("Select...", " FROM ...", " WHERE ...", " AND ...")
[/vba]

И косметическое замечание: для составной строки Chr(13) & Chr(10) существует компактная константа vbCrLf, делающая то же самое.

МОИ: Ник, Tip box: 41001663842605
 
Ответить
Сообщение
Цитата IgorStorm, 18.04.2016 в 21:06, в сообщении № 5
Почему в умную таблицу не хотел?

Попробуйте в своем первоначальном варианте просто текстовую строку, без функции Array:
т.е. вместо
[vba]
Код
.CommandText = Array("Select...")
[/vba]
просто
[vba]
Код
.CommandText = "Select..."
[/vba]

ЛИБО с функцией Array, но поделив исходную строку на несколько частей длиной не более 255 символов каждая:
[vba]
Код
.CommandText = Array("Select...", " FROM ...", " WHERE ...", " AND ...")
[/vba]

И косметическое замечание: для составной строки Chr(13) & Chr(10) существует компактная константа vbCrLf, делающая то же самое.
Автор - Gustav
Дата добавления - 18.04.2016 в 22:16
IgorStorm Дата: Вторник, 19.04.2016, 21:29 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 29
Репутация: 1 ±
Замечаний: 0% ±

Excel 2007
Спасибо огромное. Дело точно было в Array. Убрал и все выгрузилось по старой схеме - в умную таблицу.
 
Ответить
СообщениеСпасибо огромное. Дело точно было в Array. Убрал и все выгрузилось по старой схеме - в умную таблицу.
Автор - IgorStorm
Дата добавления - 19.04.2016 в 21:29
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Не формируется SQL запрос из VBA (Макросы/Sub)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!