powered by simpleCommunicator - 2.0.18     © 2024 Programmizd 02
Map
Форумы / Visual Basic [игнор отключен] [закрыт для гостей] / Очень долгий перебор строк в цикле
62 сообщений из 62, показаны все 3 страниц
Очень долгий перебор строк в цикле
    #40077851
Markovich21
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Доброго времени суток, уважаемые форумчане! В макросах не силен, по статейкам в инете написал код. Смысл такой, что в таблицу сначала вставляется необходимое количество строк, затем из буфера обмена (ранее скопированного диапазона в excel) вставляется в диапазон ячеек таблицы word (выбирается таблица №3). Далее перебираются ячейки 3 столбца и при условии, что в ячейке 3 символа, копируется в буфер содержимое ячейки второго столбца, строка очищается, ячейки в строке объединяются и затем в строку вставляется ранее скопированное содержимое ячейки второго столбца, ну и для красоты ячейка затемняется. Проблема в том, что в случае, например, двухста строк код выполняется более менее быстро, а вот когда строк, например, две тысячи, код выполняется настолько долго, что через 15-20 минут, не дождавшись окончания, снимаю задачу диспетчером. Не могу понять, что можно предпринять для исправления.
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
Sub Import()
On Error Resume Next
Application.ScreenUpdating = False
Dim i As Long
    With ActiveDocument
    n = Val(InputBox("Сколько строк в таблице?"))
    If n = 0 Then Exit Sub
    k = n - 1 'корректировка количества строк
    r = n + 1 'корректировка количества строк
        With .Tables(3) 'обращаюсь к третьей таблице
        .Rows(2).Select 'выбираю позицию для добавления строк
        Selection.InsertRowsBelow k 'вставвка заданного количества строк
        .Rows(1).Borders(wdBorderBottom).LineStyle = wdLineStyleDouble 'красявости
        .Rows(r).Borders(wdBorderBottom).LineStyle = wdLineStyleDouble 'красявости
        rs = .Cell(2, 2).Range.Start 'начало дипазона для вставки
        re = .Cell(r, 6).Range.End 'конец дипазона для вставки
        ActiveDocument.Range(rs, re).Paste 'вставка из буфера в заданный диапазон
            For i = 2 To r
                If .Cell(i, 3).Range.Characters.Count = 3 Then 'определение строки для форматирования
                .Cell(i, 2).Range.Copy 'копирую в память содержимое ячейки
                .Rows(i).Range.Delete Unit:=wdCharacter, Count:=1 'очистка строки
                .Cell(i, 1).Merge MergeTo:=.Cell(i, 6) 'объединение ячеек
                .Rows(i).Range.Paste 'вставка ранее запомненного значения
                .Rows(i).Shading.BackgroundPatternColor = wdColorGray15 'красявости
                End If
            Next
        End With
    End With
Application.ScreenUpdating = True
End Sub



Подскажите, пожалуйста, в чем ошибка в коде, существует ли способ ли ускорить его выполнение? Заранее спасибо.
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40077859
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Во-первых, тут очень много копирования в буфер. Это занимает очень много времени. По возможности, нужно избежать копирования в буфер, лучше копировать в переменную (если речь идет о значении, если, конечно, речь идет только о значении, а не о формулах, оформлении и прочем)

Во-вторых, любое взаимодействие с ячейками листа тоже достаточно медленное. Поэтому, лучше минимально задействовать такое взаимодействие в цикле и активно использовать массивы, копируя и вставляя значения разом из массива / в массив.
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40078146
Markovich21
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Shocker.Pro, большое Вам спасибо за отзыв. По первому пункту понятно в каком направлении работать, а на счет второго пункта я не совсем понял, каким образом должна происходить работа с массивом, разом копировать и вставлять, если по выполнению условия в ячейке изменяется строка. Буду очень благодарен, если немного подробней объясните как это должно работать.
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40078150
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Markovich21
каким образом должна происходить работа с массивом, разом копировать и вставлять, если по выполнению условия в ячейке изменяется строка.

Она изменяется, потому что там имеется зависимая формула?
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40078156
vb_sub
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Shocker.Pro,
столкнулся с таким кейсом-нужно обработать таблицу с листа Excel через предварительное загнание её в массив, но для обработки нет необходимости загонять всю таблицу в массив- нужно только 2 столбца из 20 к примеру. Но быстрым методом копирования диапазона в массив
Код: vbnet
1.
   SetTimingRange = Range(.Cells(2, 1), .Cells(rowCount, columnCount)).Value2


можно загнать только всю таблицу целиком в массив.
Получается, чтобы заполнить массив только 2 столбцами, а не всей таблицей, нужно делать цикл по ячейкам, который медленнее, чем Range.Value и вручную их копировать в массив. Соответственно, можно ли несколько Range склеить в один массив без цикла по ячейкам?
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40078165
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
vb_sub
Но быстрым методом копирования диапазона в массив

можно загнать только всю таблицу целиком в массив.

А указать в Range только нужные столбцы идея не приходила? типа
Код: vbnet
1.
SetTimingRange = Range(.Cells(2, 1), .Cells(rowCount, 2)).Value2
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40078168
vb_sub
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Akina,
Range это непрерывный диапазон, нельзя указать крайние столбцы и исключить некоторые промежуточные из них.
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40078182
Markovich21
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Akina, я может что то не понимаю, но у меня word и формулы в строке нет. Изменяется строка i (объединяются ячейки и в них вставляется текстовое значение из ячейки второго столбца), если в ячейке третьего столбца 3 символа
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40078183
Фотография big-duke
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
vb_sub
Akina,
Range это непрерывный диапазон

Это не так.
Код: vbnet
1.
Range("A1,B:C,f:f,16:17,20:20").Select
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40078190
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Markovich21
у меня word
о боже *FACEPALM*!
Я не заметил слона!

Думаю, что все вышестоящие ответы можно вычеркнуть.

Автору - надо ЯВНО указывать среду разработки, но мы, конечно, тоже хороши!
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40078207
Markovich21
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Shocker.Pro, виноват, каюсь, в будущем учту замечание. И все же для word существует решение данной проблемы?
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40078291
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
С вордом всё сложнее, надо возиться и пробовать.

Попробуй для начала убрать работу с буфером внутри цикла. Может это решит проблему до приемлемой скорости.

ЗЫ: Лучше работать с ThisDocument вместо ActiveDocument
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40078409
Markovich21
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Shocker.Pro, спасибо за ответы с наводками. Использовал переменную внутри цикла вместо буфера, скорость выполнения кода действительно возрасла. Теперь хотя бы на двух тысячах строках можно дождаться завершения работы макроса. Код выглядит так:
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
Sub ImportMetallTest()
Application.ScreenUpdating = False
Dim i As Integer
Dim S As String
    With ActiveDocument
    N = Val(InputBox("Сколько строк в таблице?"))
    If N = 0 Then Exit Sub
    k = N - 1
    R = N + 1
        With .Tables(3)
        .Rows(2).Select
        Selection.InsertRowsBelow k
        rs = .Cell(2, 2).Range.Start
        re = .Cell(R, 6).Range.End
        ActiveDocument.Range(rs, re).Paste
           For i = 2 To R
               If .Cell(i, 3).Range.Characters.Count = 3 Then
               S = .Cell(i, 2).Range
               .Rows(i).Range.Delete Unit:=wdCharacter, Count:=1
               .Cell(i, 1).Merge MergeTo:=.Cell(i, 6)
               .Rows(i).Range = S
               End If
           Next
       End With
    End With
Application.ScreenUpdating = True
End Sub


Но возникла проблема, что после вставленной переменной отрабатывается Enter и ячейка становится как бы двустроковой. Можно этого как то избежать?
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40078415
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Для начала отформатируем, чтобы хоть читать код можно было
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
Sub ImportMetallTest()

Application.ScreenUpdating = False
Dim i As Integer
Dim S As String
With ActiveDocument
    N = Val(InputBox("Сколько строк в таблице?"))
    If N = 0 Then Exit Sub
    k = N - 1
    R = N + 1
    With .Tables(3)
        .Rows(2).Select
        Selection.InsertRowsBelow k
        rs = .Cell(2, 2).Range.Start
        re = .Cell(R, 6).Range.End
        ActiveDocument.Range(rs, re).Paste
        For i = 2 To R
            If .Cell(i, 3).Range.Characters.Count = 3 Then
                S = .Cell(i, 2).Range
                .Rows(i).Range.Delete Unit:=wdCharacter, Count:=1
                .Cell(i, 1).Merge MergeTo:=.Cell(i, 6)
                .Rows(i).Range = S
            End If
        Next
    End With
End With
Application.ScreenUpdating = True

End Sub
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40078416
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Попробуй копировать в переменную не .Range а .Range.Text
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40078468
Markovich21
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Shocker.Pro, таким вот образом не помогло:
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
        For i = 2 To R
            If .Cell(i, 3).Range.Characters.Count = 3 Then
                S = .Cell(i, 2).Range.Text
                .Rows(i).Range.Delete Unit:=wdCharacter, Count:=1
                .Cell(i, 1).Merge MergeTo:=.Cell(i, 6)
                .Rows(i).Range = S
            End If
        Next


Все-равно откуда то присутствует символ "конец абзаца"
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40078486
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Ну значит надо его грохнуть прям в переменной
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40078535
istrebitel
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
vb_sub
Range это непрерывный диапазон, нельзя указать крайние столбцы и исключить некоторые промежуточные из них.

Можно использовать несколько Range, по одному на каждую необходимую колонку.
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40078813
Markovich21
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Shocker.Pro, несколько другим способом пытаюсь решить проблему скорости выполнения цикла. Как Вы и советовали, отказался от копирования в буфер внутри цикла. Получился код:
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
Sub ImportTest1()

Application.ScreenUpdating = False
Dim i As Integer
Dim S As String
With ActiveDocument
    N = Val(InputBox("Сколько строк в таблице?"))
    If N = 0 Then Exit Sub
    k = N - 1
    R = N + 1
    With .Tables(3)
        .Rows(2).Select
        Selection.InsertRowsBelow k
        rs = .Cell(2, 2).Range.Start
        re = .Cell(R, 6).Range.End
        ActiveDocument.Range(rs, re).Paste
        For i = 2 To R
            If .Cell(i, 3).Range.Characters.Count = 3 Then
                .Cell(i, 1).Merge MergeTo:=.Cell(i, 6)
                .Rows(i).Range = Left(.Rows(i).Range, Len(.Rows(i).Range) - 13)
            End If
        Next
    End With
End With
Application.ScreenUpdating = True

End Sub


В нем в строке, найденной по условию, сначала объединяются ячейки; в объединенной ячейке остается содержимое второй ячейки с множеством скрытых символов, которые обрезаются; в итоге как плюс - не сбивается форматирование. Скорость выполнения кода заметно выше, но все-равно когда строк две тысячи выполняется он очень долго. Не могу разобраться как работать с ThisDocument. Если с ним работать тоже должен быть прирост скорости? Есть еще варианты над чем поработать? Спасибо.
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40078817
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
можно еще ускорить, избавившись от двойного обращения к .Rows(i).Range

чтобы ускорять еще - скорее всего уже нужно менять подход в принципе, а может это уже будет невозможно. Без файла образца сложно что-то сказать
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40078892
Markovich21
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Shocker.Pro, двойное обращение к .Rows(i).Range исключил. Вставил таймер, цикл по обработке двух тысяч строк выполняется 85 сек.
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
Sub ImportTest2()

Application.ScreenUpdating = False
Dim i As Integer
Dim S As String
With ActiveDocument
'    N = Val(InputBox("Сколько строк в таблице?"))
    N = 2000
'    If N = 0 Then Exit Sub
    k = N - 1
    R = N + 1
    With .Tables(3)
        .Rows(2).Select
        Selection.InsertRowsBelow k
        rs = .Cell(2, 2).Range.Start
        re = .Cell(R, 6).Range.End
        ActiveDocument.Range(rs, re).Paste
 tm = Timer
        For i = 2 To R
            If .Cell(i, 3).Range.Characters.Count = 3 Then
                .Cell(i, 1).Merge MergeTo:=.Cell(i, 6)
                S = .Rows(i).Range
                S = Left(S, Len(S) - 13)
                .Rows(i).Range = S
            End If
       Next
    End With
End With
Application.ScreenUpdating = True

MsgBox Timer - tm, 64

End Sub


Прилагаю файлы для примера. В таблице excel нажимаю кнопку "копировать в буфер", в word выполняю код. Т.к. два файла приложить не могу, поэтому выложил в архиве. Предполагаю, что можно сократить время, если передавать таблицу из excel в word напрямую, но как это сделать пока не знаю, да и все-таки основное время уходит на обработку строк в цикле, вот его бы ускорить
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40079841
Markovich21
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Shocker.Pro

чтобы ускорять еще - скорее всего уже нужно менять подход в принципе, а может это уже будет невозможно.

Shocker.Pro, в чем может заключаться подход с другой стороны, имеется ввиду отказаться от цикла перебора ячеек?
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40079849
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Я поэкспериментировал с этим кодом. Даже если просто убрать все внутренности цикла, тормозит сама проверка
Код: vbnet
1.
.Cell(i, 3).Range.Characters.Count = 3


Попытки обращения к ячейке другими способами результата особо не дали.

Я не знаю, какие у тебя возможности есть при манипуляции экселем, кроме копипасты
По большому счету, нужно заранее вычислить номера строк для объединения ячеек
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40079872
Markovich21
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Shocker.Pro, большое спасибо Вам за помощь и быстрые ответы. Может я делаю это криво, но в экселе, я специально ввожу в ячейку 3 символа, чтобы определить строку в которой нужно объединить ячейки, т.е. эти три символа в ячейке третьего столбца работают как маркер. Может есть какие то другие способы, более быстрые, по которым определить строку, в которой объединить ячейки? А про определение номеров строк для объединения в самом экселе... у меня в голове витала такая идея. Но она для меня какая то фантастическая. В самом экселе определить номера строк для определения сложностей нет (например, через те же три символа в ячейке), но фактически передать в ворд номера строк для объединения не представляю как реализовать. Количество строк и номера строк могут быть абсолютно разные. В экселе нужно выделить ячейку (диапазон) для номеров строк или как то через переменные эти номера передавать?
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40079876
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Markovich21
я специально ввожу в ячейку 3 символа
это программно происходит или руками?
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40079888
Markovich21
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Shocker.Pro, програмно, пытаюсь автоматизировать как можно больше... добавляю два пробела спереди и сзади к значениям (для центирования), а когда значение пусто, то в ворде присутствует третий скрытый символ
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40079891
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
А сам эксель тут для чего вообще? Может опишешь весь процесс, потому что раз не получилось локально оптимизировать цикл, надо расширять горизонты.
К примеру, можно управлять экселем из ворда, можно вордом из экселя, можно вордом забирать данные минуя буфер обмена, из БД сразу, или, к примеру, забирать с листа экселя как из БД через ADODB.
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40079923
Markovich21
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Shocker.Pro, в эксел вводятся исходные данные, происходит их обработка с помощью формул и макросов, полученные данные выстраиваются в итоговом виде, далее в экселе нажимаю кнопку, которая необходимый диапазон ячеек копирует в буфер, запускаю готовую форму (заполнены прочие данные) в ворде, в нем запускаю данный код, заполняется и форматируется (объединяются ячейки где необходимо) таблица и т.о. формируется готовый отчет. Основная работа происходит в экселе, в ворд отправляются готовые данные и проставляется номер страниц.
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40079994
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Хорошо, если данные готовятся в экселе с помощью макросов, почему бы не объединить ячейки в экселе до копирования?
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40080008
Markovich21
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Shocker.Pro, не совсем удобно объединять ячейки в экселе, т.к. объединение будет макросом, не будет отмены действия, а содержимое ячеек с формулами динамическое и все может изменяться на любом этапе. Либо макросом после объединения ячеек и копирования в буфер в нем же сразу и возвращать исходный вид ячеек.
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40080020
Markovich21
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Shocker.Pro, попробовал вручную скопировать в ворд объединенные в экселе ячейки. Не работает, ворд объединенные ячейки воспринимает как одну ячейку и соответственно в выделенный диапазон для вставки просто дублирует в каждую ячейку содержимое буфера.
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40080032
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
А у меня нормально прошло именно на твоих файлах
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40080041
Markovich21
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Shocker.Pro, извини, а как ты делал? Я в экселе ячейку Ctrl+C, в ворде выделяю в строке ячейки и выполняю Ctrl+V; и во всех выделенных в строке ячейках продублировано содержимое исходной ячейки эксел.
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40080043
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
я объединяю ячейки в экселе и нажимаю кнопку.

В ворде ничего не выделяю, просто вставляю
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40080053
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Есть еще такая штука как "слияние данных"
Я с ней сам не работал
По идее как раз и предназначена для твоих целей, но не знаю, удовлетворит ли в нужной мере
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40080070
Markovich21
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Shocker.Pro, я понял, данные из буфера вставляются не в таблицу, а вставляется сама таблица целиком. Вариант интересный, но, так понимаю, нужно будет задавать позицию курсора для вставки и форматировать вставленную таблицу (задавать ширину столбцов и т.п.). Интересным еще представляется вариант с передачей из эксела в ворд номеров строк для объединения ячеек. Ведь тогда будут общие переменные для ворда и эксела и можно сделать, чтобы по нажатию кнопки в экселе таблица вставлялась в ворд без дополнительного запуска макроса в ворде. Но как это сделать пока не знаю, нужно изучать вопрос
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40080076
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Markovich21
Вариант интересный, но, так понимаю, нужно будет задавать позицию курсора для вставки и форматировать вставленную таблицу (задавать ширину столбцов и т.п.).
это будет работать гораздо быстрее перебора

Markovich21
по нажатию кнопки в экселе таблица вставлялась в ворд без дополнительного запуска макроса в ворде.
Ищи по форуму CreateObject("Word.Application"), нароешь миллион примеров

Смысл в том, что Эксель создает объект Word-а и может им управлять в такой же мере, как ты это делаешь с помощью макроса самого Ворда.
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40080097
Markovich21
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Shocker.Pro, еще раз большое спасибо. Правильно понимаю, что второй вариант с управлением вордом из эксела более оптимальный? Если да, буду его изучать.
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40080101
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Понятие "оптимальный" зависит от того, как будет развиваться твой проект и сколько времени ты можешь этому посвятить.
Если нужно быстро доделать и забыть - то первый вариант. Второй вариант более гибкий, более перспективный (кстати, с тем же успехом можно управлять экселем из ворда), но потребует времени и экпериментов.
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40080184
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
я в отпуск, заглядывать-то буду, но времени вдумчиво отвечать и экспериментировать, не будет, так что очень рассчитываю, что коллеги помогут в этом топике
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40080233
Markovich21
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Shocker.Pro, еще раз спасибо, буду по-возможности разбираться со вторым вариантом, он мне нравится. Хорошего качественного и душевного отдыха!
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40081771
Фотография Antonariy
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Shocker.Pro
Я поэкспериментировал с этим кодом. Даже если просто убрать все внутренности цикла, тормозит сама проверка
Код: vbnet
1.
.Cell(i, 3).Range.Characters.Count = 3


Попытки обращения к ячейке другими способами результата особо не дали.

Я не знаю, какие у тебя возможности есть при манипуляции экселем, кроме копипасты
По большому счету, нужно заранее вычислить номера строк для объединения ячеек

Индексированные свойства тормозят. Потому что при обращении по индексу где-то внутри происходит перебор. Чтобы не тормозило, нужно использовать for each.

Кроме того Cells это Range и есть, в Cells.Range смысла нет.
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40083202
Markovich21
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Доброго времени суток, уважаемые форумчане! Последовав рекомендациям, подошел с другой стороны к решению вопроса копирования таблицы из excel в word с форматированием определенных строк по условию. Выгрузка в word осуществляется по нажатию кнопки "заполнить таблицу word" в экселе, строки для форматирования определяются в экселе и передаются в ворд. Т.о. таблица величиной 2000 строк передается в ворд значительно быстрее, за 37сек (в предыдущем варианте в ворде код выполнялся 87сек). Т.к. в макросах не силен совсем, прошу посмотреть представленный ниже код на предмет корректности написания и может что то в нем можно оптимизировать и ускорить. И еще, ответьте, пожалуйста, на вопрос можно ли таблицу передавать в ворд в виде переменной (предполагаю, что с помощью переменной передача в ворд будет происходить еще быстрее)? Сам попытался выгрузить с помощью переменной, ничего не получилось. В качестве примера прикладываю архив с двумя файлами ("источник" эксел и "приемник" ворд). Заранее спасибо.

Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
Sub ToWordTbl()

tm = Timer

    With Application
        .ScreenUpdating = False
    End With
On Error Resume Next

Dim WrdApp As Word.Application
Dim WrdDoc As Word.Document
Dim WrdTbl As Word.Table
Dim DocPath As String
Dim n As Integer, k As Integer, r As Integer, s As Integer, t As Integer
    With Worksheets("Лист1")
        n = .Range("A10")
        k = n - 1
        r = n + 1
        s = .Range("J10") + 9
        .Cells(11, 1).Resize(n, 5).Copy
    End With
    
    DocPath = ThisWorkbook.Path & "\"
    
    Set WrdApp = GetObject(, "Word.Application")
        If WrdApp Is Nothing Then Set WrdApp = CreateObject("Word.Application")
    Set WrdDoc = WrdApp.Documents.Open(DocPath & "Приемник.docx")
        If WrdDoc Is Nothing Then Set WrdDoc = WrdApp.Documents.Open(DocPath & "Приемник.docx")
    
    WrdApp.Visible = False
    
        If Dir(DocPath & "Приемник.docx") = "" Then
            MsgBox "Файл ""Приемник.docx"" отсутствует"
            WrdApp.Visible = True
            Exit Sub
        End If
      
    Set WrdTbl = WrdDoc.Tables(3)
    With WrdTbl

        If .Rows.Count > 2 Then
            MsgBox "Ошибка шаблона ""Приемник""."
            Application.CutCopyMode = False
            WrdApp.Visible = True
            Exit Sub
        End If
        
        .Rows(2).Select
            
        WrdApp.Selection.InsertRowsBelow k
        
        WrdDoc.Range(.Cell(2, 2).Range.Start, .Cell(r, 6).Range.End).PasteAndFormat (16)

        .Rows(1).Borders(wdBorderBottom).LineStyle = wdLineStyleDouble
        .Rows(r).Borders(wdBorderBottom).LineStyle = wdLineStyleDouble
               
        For i = 10 To s
            t = ThisWorkbook.Worksheets("Лист1").Cells(i + 1, 10) + 1
            
            .Cell(t, 2).Range.Copy
            WrdDoc.Range(.Cell(t, 1).Range.Start, .Cell(t, 6).Range.End).Delete
            .Cell(t, 1).Merge MergeTo:=WrdApp.ActiveDocument.Tables(3).Cell(t, 6)
            .Cell(t, 1).Range.PasteAndFormat (16)
        Next

    End With

    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With

WrdApp.Visible = True

MsgBox Timer - tm, 64

End Sub
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40083203
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Markovich21
Код: vbnet
1.
On Error Resume Next

это очень плохая идея
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40083207
Markovich21
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Shocker.Pro,
авторэто очень плохая идея
что в этом плохого? после того как закомментировал эту строку вывалилась ошибка в:
Код: vbnet
1.
Set WrdApp = GetObject(, "Word.Application")


значит это место нужно переработать, а смысл доработки есть или все-таки оставить
Код: vbnet
1.
On Error Resume Next

?
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40083210
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
перед этой строкой On Error Resume Next
Markovich21
вывалилась ошибка в:
Код: vbnet
1.
Set WrdApp = GetObject(, "Word.Application")

после On Error Goto 0


Markovich21
что в этом плохого?
в том, что ты запретил все ошибки во всем коде, прога будет пытаться работать до последнего, пользователь не узнает, что, например, не все нужные строки скопировались, в худшем случае прога не дойдет до конца, скрытый ворд будет висеть в процессах и т.д. и т.п. Да и с отладкой ты замучаешься.
Ошибки должны обрабатываться корректно, анализироваться. А ты взял и заглушил все ошибки, типа "я не вижу проблемы, значит её и не существует"
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40083211
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Markovich21
Код: vbnet
1.
           Exit Sub

а кто будет ScreenUpdating на место возвращать? Пользователь сильно удивится
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40083212
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Shocker.Pro
Markovich21
Код: vbnet
1.
           Exit Sub


а кто будет ScreenUpdating на место возвращать? Пользователь сильно удивится
кстати, а зачем ты ScreenUpdating выключаешь на экселе? Ты же вроде ничего не меняешь в экселевском файле?
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40083214
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Markovich21
что то в нем можно оптимизировать и ускорить.
ты сейчас в цикле насилуешь буфер обмена, это не быстрая операция

Возможно, быстрее будет скопировать в ворд таблицу целиком, а потом объединить только нужные ячейки, которые вычислены заранее, как я и предлагал ранее
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40083249
Markovich21
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Shocker.Pro, про
Код: vbnet
1.
ScreenUpdating

и
Код: vbnet
1.
On Error Resume Next

понял, спасибо за замечание. А вот с авторВозможно, быстрее будет скопировать в ворд таблицу целиком, а потом объединить только нужные ячейки, которые вычислены заранее, как я и предлагал ранее я не понял, что имеется ввиду. В коде работает вроде бы так: в экселе таблица копируется в буфер, затем в ворде вставляется таблица целиком, а затем объединяются строки по списку из экселя (номера строк берутся из небольшого цикла)
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40083250
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
я вижу копипасту внутри цикла
Markovich21
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
        For i = 10 To s
            t = ThisWorkbook.Worksheets("Лист1").Cells(i + 1, 10) + 1
            
            .Cell(t, 2).Range.Copy
            WrdDoc.Range(.Cell(t, 1).Range.Start, .Cell(t, 6).Range.End).Delete
            .Cell(t, 1).Merge MergeTo:=WrdApp.ActiveDocument.Tables(3).Cell(t, 6)
            .Cell(t, 1).Range.PasteAndFormat (16)
        Next

...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40083257
Markovich21
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Shocker.Pro, тут работает таким образом, что таблица в ворд уже полностью вставлена, а t номер строки (берущийся из экселя), в которой нужно объединить ячейки. Сначала какое то общее наименование (оно находится во втором столбце), которое должно быть в объединенной строке копируется в буфер, ячейки строки очищаются, объединяются и наименование вставляется обратно в уже объединенную ячейку. У меня родился такой вот алгоритм работы, другой как то в голове не складывается. Можно как то по другому лучше сделать?
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40084316
Markovich21
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Shocker.Pro, Скажите, пожалуйста, вставить диапазон ячеек в таблицу ворд, не используя буфер обмена, а через переменную теоретически возможно? у меня при попытках что то подобное сделать выпадают либо ошибки, либо ничего не вставляется, хотя по одной ячейке вставка в ячейку таблицы ворд через переменную происходит без проблем. Предполагаю, что выделение диапазона ячеек в ворде таким образом:
Код: vbnet
1.
WrdDoc.Range(.Cell(2, 2).Range.Start, .Cell(r, 6).Range.End)

не подходит для вставки переменной.
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40084611
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Вставить текст можно через переменную или прямо напрямую, присвоив значение свойства Text. Но форматирование не сохранится
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40084613
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Но вообще я этот код тестировал на прошлой неделе, там львиная часть времени уходит не на цикл, а на вставку приличного размера данных из буфера обмена
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40087830
Markovich21
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Shocker.Pro, спасибо за помощь. по Вашему совету избавился от копирования в цикле, использовав переменную и массив. Никак не получается вставлять данные минуя буфер обмена (через переменную), значение свойства Text. применять пробовал. Подскажите, пожалуйста, имеет ли смысл пытаться вставлять данные через переменную, будет ли на этом прирост скорости? На данный момент рабочий код выглядит так:
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
Sub ToWordTbl()

tm = Timer

Dim WrdApp As Word.Application
Dim WrdDoc As Word.Document
Dim WrdTbl As Word.Table
Dim DocPath As String, d As String
Dim n As Integer, s As Integer
Dim str() As Variant
        
        n = Worksheets("Лист1").Range("A10")
        If n = 0 Then
            MsgBox "Таблица пустая"
        Exit Sub
        End If
    
    With Worksheets("Лист1")
        s = .Range("J10") + 12
        .Cells(11, 1).Resize(n, 5).Copy
        str = .Range(Cells(11, 10), Cells(s, 10)).Value
    End With
    
    DocPath = ThisWorkbook.Path & "\"
    
    On Error Resume Next
    Set WrdApp = GetObject(, "Word.Application")
        If WrdApp Is Nothing Then Set WrdApp = CreateObject("Word.Application")
    Set WrdDoc = WrdApp.Documents.Open(DocPath & "Приемник.docx")
        If WrdDoc Is Nothing Then Set WrdDoc = WrdApp.Documents.Open(DocPath & "Приемник.docx")
    On Error GoTo 0

    WrdApp.Visible = False
    
        If Dir(DocPath & "Приемник.docx") = "" Then
            MsgBox "Файл ""Приемник.docx"" отсутствует"
            Application.CutCopyMode = False
            WrdApp.Visible = True
            Exit Sub
        End If
      
    Set WrdTbl = WrdDoc.Tables(3)
    With WrdTbl

        If .Rows.Count > 2 Then
            MsgBox "Ошибка шаблона ""Приемник""."
            Application.CutCopyMode = False
            WrdApp.Visible = True
            Exit Sub
        End If
        
        .Rows(2).Select
            On Error Resume Next
            WrdApp.Selection.InsertRowsBelow n - 1
            On Error GoTo 0
        
        WrdDoc.Range(.Cell(2, 2).Range.Start, .Cell(n + 1, 6).Range.End).PasteAndFormat (16)

        .Rows(1).Borders(wdBorderBottom).LineStyle = wdLineStyleDouble
        .Rows(n + 1).Borders(wdBorderBottom).LineStyle = wdLineStyleDouble
               
        For i = 1 To UBound(str) - 2
            d = .Cell(str(i, 1) + 1, 2).Range
            .Cell(str(i, 1) + 1, 1).Merge MergeTo:=WrdApp.ActiveDocument.Tables(3).Cell(str(i, 1) + 1, 6)
            d = Left(d, Len(d) - 2)
            .Cell(str(i, 1) + 1, 1).Range = d
        Next

    End With

    With Application
        .CutCopyMode = False
    End With

    WrdApp.Visible = True

MsgBox Timer - tm, 64


End Sub
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40087838
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Через переменную можно перенести текст, но не полностью набор ячеек и строк со всем форматированием и т.п.
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40087857
Markovich21
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Shocker.Pro,
создаю переменную:
Код: vbnet
1.
Dim tbl As Variant


вместо копирования в буфер строка:
Код: vbnet
1.
tbl = .Cells(11, 1).Resize(n, 5).Text


и в строке ошибка:
Код: vbnet
1.
WrdDoc.Range(.Cell(2, 2).Range.Start, .Cell(n + 1, 6).Range.End) = tbl



Получается сделал я примерно так как работало бы в экселе. С вордом так не работает, как Вы говорите, передается только текст. Еще помимо вставки из буфера значимую часть времени занимает объединение ячеек, что тоже, наверное, не ускоришь.
Выходит, что в плане оптимизации и увеличения скорости больше особо ничего не выжмешь и можно оставить код как есть?
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40087879
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Markovich21
Получается сделал я примерно так как работало бы в экселе. С вордом так не работает, как Вы говорите, передается только текст.
Дело не в экселе или ворде, а в том, что их ячейки - это объекты совершенно разного типа.


Markovich21
Выходит, что в плане оптимизации и увеличения скорости больше особо ничего не выжмешь и можно оставить код как есть?
Во-первых было предложено:
Antonariy
Индексированные свойства тормозят. Потому что при обращении по индексу где-то внутри происходит перебор. Чтобы не тормозило, нужно использовать for each.
я сам не экспериментировал. Возможно, можно ускорить, попытавшись избавиться от обращениям к ячейкам по индексам.

Можно покурить "слияние", оно вроде как предназначено для того, чтобы перекидывать данные в такой ситуации. Сам не работал с ним.

Можно попытаться работать со стороны Ворда (то есть чтобы код был не в экселе, а в ворде, затянуть данные из экселевского файла через ADODB, обработать их в памяти и вывести в таблицу)

Можно сформировать html-файл и открыть его в ворде. Я делал так универсальные отчеты, которые можно было открывать в браузере, ворде и экселе, при этом они выглядели более-менее одинаково. Там, правда, очень странная интерпретация html, например в тэге в атрибуте класса может быть только один класс, остальные игнорируются, поэтому для документа приходилось создавать персональную таблицу классов, но в целом удавалось объединять и раскрашивать ячейки таблицы и осуществлять кое-какое форматирование

Так что есть куда копать, всё зависит от времени и желания
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40087928
Markovich21
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Shocker.Pro, спасибо, за наметки. Запуск макроса со стороны ворда отпадает, под мой случай не подходит, а вот другие варианты буду изучать
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40087947
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
В конце-концов можно напрямую формировать docx-файл, так как это просто zip-архив с кучкой xml-внутри. Но без какой-нибудь OpenXML-библиотеки, которая упростит формирование файлов - это адский труд. Да и с библиотекой не сахар, в общем-то. Зато это точно будет самый быстрый вариант ))
...
Рейтинг: 0 / 0
Очень долгий перебор строк в цикле
    #40088149
Markovich21
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Shocker.Pro, спасибо, но последний вариант это уже выше моих видений о возможностях vba, сложно представляю о чем идет речь.
...
Рейтинг: 0 / 0
62 сообщений из 62, показаны все 3 страниц
Форумы / Visual Basic [игнор отключен] [закрыт для гостей] / Очень долгий перебор строк в цикле
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (1): Анонимы (1)
Читали форум (1): Анонимы (1)
Пользователи онлайн (8): Анонимы (5), Yandex Bot, CerebroSQL, Bing Bot 1 мин.
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]