есть файл t.xls с одним листом tn - таблица с заголовками (7 столбцов) есть другой файл с листом mmm и таблицей из трёх столбцов
нужно изменить данные в первом файле на основе данных второго файла в первом файле 29364 записи во втором 216
делаю так:
только не смейтесь...
Код
Public Declare Function GetTickCount Lib "kernel32" () As Long
Public Sub ADO_upd_norm1() ' update норм
Dim a As New ADODB.Connection, arr(), i&, s$
t = GetTickCount
Application.ScreenUpdating = False
With Sheets("mmm") ' временно
With a
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source='d:\t.xls" & _
"';Extended Properties='Excel 8.0;HDR=Yes;IMEX=3';"
.Open
End With
arr = .[a1].CurrentRegion.Value
For i = 1 To UBound(arr)
s = s & "||" & arr(i, 1) & "|" & arr(i, 2) & "|" & arr(i, 3)
Next
a.Execute "UPDATE [tn$] SET normDel=1 WHERE InStr('" & s & "', '||' & idMat & '|' & idIzd & '|' & normSZ)>0"
a.Close
End With
Debug.Print "ADO_upd_norm1", GetTickCount - t
End Sub
Public Sub ADO_upd_norm2() ' update норм
Dim a As New ADODB.Connection, arr(), i&, s$
t = GetTickCount
Application.ScreenUpdating = False
With Sheets("mmm") ' временно
With a
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source='d:\t.xls" & _
"';Extended Properties='Excel 8.0;HDR=Yes;IMEX=3';"
.Open
End With
arr = .[a1].CurrentRegion.Value
For i = 1 To UBound(arr)
a.Execute "UPDATE [tn$] SET normDel=1 " _
& "WHERE idMat=" & arr(i, 1) & " AND idIzd=" & arr(i, 2) & " AND normSZ='" & arr(i, 3) & "'"
Next
a.Close
End With
Debug.Print "ADO_upd_norm2", GetTickCount - t
End Sub
выполняется всё это из второго файла при закрытом первом на не слишком тормозном ноуте "2 ядра - 3 гига" :) мой Excel 2003 отрабатывает вторую процедуру за 14,5сек., первую - примерно за 4,2сек.
кроме обычных "что делать?" и "кто виноват?" закрадывается сомнение - а нет ли другого варианта для апдейта, чтоб жужжало побыстрее? само собой - через ADO и SQL, а иначе неинтересно (точнее - иначе оно мне не надо)
примеры файлов не прикладываю. вообще не знаю, что с ними делать... :( заархивированный первый весит чуть больше 400 килобайт. а на маленьком объеме и вопроса не будет. но, может быть, профи и без примера подскажут чего?..
(желающим могу выслать файлики на мейл - ничего секретного там нет)
Доброе время суток Александр Первый вариант - слишком много работы со строками и преобразованиями типов, поэтому, на мой взгляд и медленнее. Думаю такой запрос на обновление должен, по идее, быть быстрее, так как не инициализирует каждый раз обновление (естественно, нужно указать путь к файлу с листом mmm и предполагается, что названия полей в обоих таблицах одинаковые).
Код
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"
Run-time error '-2147217865 (80040e37)': Объект 'mmm$' не найден ядром базы данных Microsoft Jet. Проверьте существование объекта и правильность имени и пути.
фрилансер Excel, VBA - контакты в профиле "Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
ADO_upd_norm4 выдает ошибку из сообщения #3 на строке .Execute
пс. anvg, Вы так быстро отвечаете, что я тоже вынужден спешить возможно, если я сейчас позавтракаю, покурю и подумаю, то найду свой ляп...
ппс. сейчас заметил - у меня используется ссылка на Microsoft ActiveX Data Objects 2.8 Library (а не 2.0). попробовал и так и эдак - чего-то пока ничего...
Александр. У вас в Ado_upd_norm4 лишние кавычки ' вокруг имени файла - без них обновление выполнилось. Почему не отработал мой вариант в example не знаю. Попробовал в Excel 2010 на Win7 и на виртуальной машине в Excel 2003 под WinXP - полёт нормальный. P. S. тестовый пример для tn на 65000 строк и mmm на 3250 отработал за 1,8 секунды. Если что, то завтра посмотрю, если вы так и не решите проблему.
пс. ай!.. копировал листы туда-сюда. и случайно удалил лист tn с реальными данными. так что скорость работы теперь только завтра смогу проверить. на тестовых - 0,047 против 0,016 в ADO_upd_norm3, но это, я так понимаю, в пределах шума. хотя по сравнению с лучшей из моих ADO_upd_norm1 (0,265) - всё равно отлично!
большущее спасибо.
фрилансер Excel, VBA - контакты в профиле "Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
Пробелы вряд ли могут повлиять, а вот скобки [] или = ! могут. По крайней мере у меня: в папке с пробелами выполнилось, а в папке с именем =[] выскочило сообщение "Ошибка синтаксиса в инструкции Update".
а почему так сильно увеличивается размер файла? чуть более чем в 2 раза! что интересно - если этот увеличенный файл просто открыть в excel и сохранить - размер приходит "в норму"...
фрилансер Excel, VBA - контакты в профиле "Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
ikki пишет: а почему так сильно увеличивается размер файла? чуть более чем в 2 раза!
Можно посмотреть как "строки" записаны в ячейки. Этом может быть связано с особенностями хранения строк внутри файла .xml
Возможно, сразу после обработки в каждую ячейку записана строка. После того, как вы открываете файл и сохраняете его файл "перестраивается". Строки собираются в sharedStrings.xml а ячейки туда просто ссылаются.
p/s потом увидел что речь идет об .XLS В .XLS информация организована иначе, но механизм может быть похож