А теперь к делу ))) Есть данные из SAP, которые нужно обработать. В Excel данные обрабатываются, НО много различных исключений по сцепкам (около 4 вкладок с исключениями по разным сцепкам - от 100 до 70 000 строк). Шаблон уже пытался по-всякому оптимизировать, уменьшал формулы, менял их местами, считал с помощью макроса, сколько каждая формула занимает времени при пересчете), но данных много (обрабатывается в Excel иногда до 130 000 строк - и это кол-во будет увеличиваться) и шаблон от этого тормозит (много ЕСЛИ и ВПР). Возникла идея перекинуть хотя бы эти таблицы с исключениями в Access и пользоваться только запросами к ним. Куски разных кодов брал с форумов и блогов, получилось так:
Код
Sub кнопка()
Dim sCon As String, FieldName As String, sSql As String
Dim FilePath As String, conn As Object
Set conn = CreateObject("ADODB.Connection"
conn = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=g:\2.accdb;Uid=Admin;Pwd=;"
conn.Open 'подключаемся к БД
For i = 1 To 5
sSql = "SELECT ПОкр FROM Таблица1 WHERE Товар=" & Cells(i, 3).Value & ""
Sheets("Лист2".Cells(i, 6).CopyFromRecordset conn.Execute(sSql)
Next
conn.Close
Set conn = Nothing
End Sub
Естественно, запрос отправляется только от одной ячейки, что меня не устраивает. Пытался зациклить процесс, но тогда считает оочень долго даже 2000 строк. Пытался также помещать данные в массив, но там тоже не лучше ситуация.
Возможно ли как-то сделать запрос массовым для ячеек ОТ и ДО ? Или, возможно, у вас появятся еще мысли, как уменьшить время расчета?
ber$erk,ну например магазин №1 Товар "1000" возится в коробках, а на супермакрет №2 тот же товар возится в поддонах (примерный смысл сцепок). Т.е. к сцепке №1&"1000" подтягиваются "коробки".
Насчет обрабатывать в Access - вот фиг знает, как мне показалось при беглом взгляде на программу, не очень удобно менять формулы (если изменился алгоритм) + вроде есть какая-то заморочка с вставлением данных для оперативной обработки (за 1 день может потребоваться вставить данные раз 30-60 - вставить от 10 строчек до 130 000 - нужно быстро сделать копи-паст).
Цитата
ber$erk пишет: Может можно выгрузить все данные в массив и уже его обрабатывать макросами?
Примерно так делал с 80 000 строчек обрабатываемых данных - 1 массив + 80 000 строчек исключений - 2 массив, сравнивал их, времени что-то много уходит на это (не дождался конца). Т.е. перебирал каждый элемент массива №1 в массиве №2.
вот это мне пока не понятно - как может быть 80 тысяч исключений? Может быть получиться через словарь сделать. в два прохода: 1) загоняем в словарь исключения 2) проходим по данным и исключаем те, которые есть в словаре.
З.Ы.: выкладывали здесь вот такую справочку - до сих пор ею пользуюсь
Спасибо! Сегодня около 5 часов, сидя на работе, пробовал как-то под себя подстроить строку из вашей ссылки:
Код
a.Execute "Update [tn$] As t1 Inner Join [Excel 8.0;Database=d:\path\mmm_sheet_file.xls;HDR=YES].[mmm$] As t2 On ((t1.idMat=t2.idMat) And (t1.idIzd=t2.idIzd) And (t1.normSZ=t2.normSZ)) Set t1.normDel=1"
Я так понимаю, мне тут нужно применять не Update, а простой SELECT FROM ? Т.к. заменять данные не надо, а просто подтягивать. Также не понял, что означает это t1.idMat=t2.idMat и это t1.normDel=1 в этом примере...
Подскажите, применительно к моему коду ниже, как можно его поправить?
Код
sSql = "SELECT ПОкр FROM Таблица1 WHERE Товар=" & Cells(i, 3).Value & ""
Sheets("Лист2".Cells(i, 6).CopyFromRecordset conn.Execute(sSql)
Цитата
Юрий М пишет: modcom, код следует оформлять тегом. Исправляйте.
Сгруппируйте товар по группам (простите за тавтологию) и исключайте по ним - исключений должно, навскидку, поубавиться. Либо исключайте по ключевым словам, которые не встречаются в целевых позициях.
да. я вначале вопрос неточно понял. думал, Вам нужно обновление.
основной смысл альтернативного (не по одной записи) SELECT'а - это избежание накладных расходов на соединение и т.п. устанавливаем соединение таблиц - той, из которой нужно получить данные и нужного нам подмножества ключей. допустим, нам нужно поле field2 из таблицы main для каких-то записей. перечень этих записей находится в таблице tab само собой, таблицы main и tab как-то связаны, например - по полю key
тогда получим:
Код
SELECT main.field2 FROM (main INNER JOIN tab ON main.key = tab.key)
не проверял.
я пока основные сложности вижу в правильном построении запроса вместе с соединениями таблиц из разных источников - Access и Excel (опять же, если я правильно понял Вашу задачу). гуглить надо.
фрилансер Excel, VBA - контакты в профиле "Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
Сидел сегодня еще несколько часов по форумам. Вообщем решил задачу пока так - связал анализируемую таблицу Excel с Access. В Access создал запрос, который формирует отчет из данных Excel. А в Excel создал запрос, подтягивающий уже готовые данные из запроса Access))) К сожалению, не нашел способа, как в запросе Excel сравнить сразу диапазон ячеек со столбцом в Access, поэтому пришлось так крутить...
Думаю, что это возможно (даже для меня было бы удобней), но пока так, с помощью костылей.))) Попробую еще объяснить задание (скорее всего непонятно описал его в начале).
Есть, например, Супермаркет (С) - Товар (Т). 95% товаров возятся кратно коробкам. НО, есть С-Т, которые должны возится кратно штукам или поддонам (неважно). Это и есть исключения, на данный момент которые ВПРятся по сцепке С-Т из листа "Исключения". (прикрепил пример в файле). Т.к. исключений очень много + сцепок для исключений тоже, хочу ускорить это дело запросами+убрать всякие сцепки и ВПРы. Пробовал запрос поячеечно - это очень долго. Можно ли взять весь столбец для запроса, не прибегая к массиву? (надеюсь, понятно выразился))).
Спасибо большое, теперь понятно, что это возможно!!
Переделал немного под свою задачу - иногда неправильно тянет (выделил в примере красным), иногда не с первого раза сам макрос срабатывает (?), также почему-то подтягивает данные "-1" строка...
Буду очень благодарен, если посмотрите (путь для базы данных прописан как диск G)
Извиняюсь за дотошность, b.id это наверное первая колонка id, а a.test - это где? в коде есть про "а" [tt$a1:a6]-где это находится? в access же только одна таблица tbl. Спасибо.
1. Понял, пробую использовать, читаю всякие форумы, но выходит ошибка. Последняя моя версия, как ЭТО должно быть:
Код
sSql = "SELECT b.ПОкр FROM [Excel 12.0;DATABASE=" & ThisWorkbook.FullName & ";HDR=YES;IMEX=1;].[tt$a1:b20] as a,Таблица1 as b" _
& " LEFT OUTER JOIN b ON b.тк=a.тк AND ON b.товар=a.товар"
Подскажите, в чем ошибка?
2. Интересно, почему в вашем запросе:
Код
sSql = "SELECT b.namestr FROM [Excel 8.0;DATABASE=" & ThisWorkbook.FullName & ";HDR=YES;IMEX=1;].[tt$a1:a6] as a, tbl as b" _
& " where b.id=a.test"
в выражении FROM присутствуют 2 таблицы, а не одна?
Вы лучше скажите, что Вы хотите получить в итоге? ---- Если просто подтянуть некие данные из справочника, тогда сформируйте полный набор данных и вставьте его на лист. Если необходимо вставить в эти конкретные ячейки, тогда надо извращаться. Сначала пронумеровать записи в excel затем провести отбор с сортировкой.
Jet сам (исходя из вашего запроса) определят какой столбец индексировать для поиска данных. Естественно он его и сортирует и затем выводит информацию в отсортированном виде.
B.Key пишет: Jet сам (исходя из вашего запроса) определят какой столбец индексировать для поискаданных. Естественно он его и сортирует и затем выводит информацию в отсортированном виде.
Спасибо Вам большое! Всё получилось теперь таким образом:
Код
sSql = "SELECT ПОкр FROM Таблица1 as b" _
& " RIGHT OUTER JOIN [Excel 12.0;DATABASE=" & ThisWorkbook.FullName & ";HDR=YES;IMEX=1;].[tt$b1:c20] as a ON b.тк=a.тк AND b.Товар=a.Товар"
Если данные есть в справочнике - они подтягиваются к соответствующим строкам, если нет, то просто стоит "Пусто".
B.Key, считаю, что вы дали достаточно эксклюзивную информацию, т.к. в гугле несколько часов поисков не дали нормального результата, как присобачить справочник Access в таблицу Excel.
P.S. Кому требуется сделать также, можете обращаться ко мне, помогу и расскажу, что знаю!