Добрый день, уважаемые форумчане) Возникает досадная ситуация при использовании Power Query. При запуске макроса на vba есть строка, которая обновляет Query.
Код
Err.Clear
Application.DisplayAlerts = False
On Error Resume Next
With Workbooks("forum.xlsm").Connections("my_query").OLEDBConnection
.BackgroundQuery = False 'or true, up to you
.Refresh
End With
If Err.Number <> 0 Then GoTo nextx
Debug.Print "обновлено"
nextx:
Раз в энное количество запусков макроса вылетает "Непредвиденная ошибка", связанная с PQ. On Error Resume Next, application.displayallert = false - не помогают убрать возникновение "Непредвиденной ошибки"
Свою задачу могу решить и без PQ, но хочется разобраться в чем дело??? Что бы с уверенностью полагаться на PQ.
Подробные сведения об ошибке приложил. Пользуюсь Excel 2016 64bit
Aleksei_Zhigulin, думаю эту тему стоит поднять попозже, в другой ветке) но на небольших файлах PQ отрабатывает в макросе хорошо. Но вот сделал 600 000 строк, до 10 колонок. и PQ до 10 видоизменений. И вот тут при запуске обновления таблицы PQ макросом, сбоит через раз. и ошибка была не в макросе.
Stics, спасибо, но Power Query не хочу использовать. При работе с большим количеством данных и запуском через макрос, Power Query через раз дает ошибку. При мануальном запуске все всегда отлично. У меня Excel 2016 64bit.
Добрый день, уважаемые форумчане) Прошу Вашей помощи в формировании массива на основании двух других массивов. Интересно задачку решить именно через массивы.
На компьютере должна быть папка "Ортодонтия" на диске D. "D:\Ортодонтия\"
Код
Sub test()
Application.ScreenUpdating = False
If [a1] = "" Or [c1] = "" Then
MsgBox "Cначала укажите путь в ячейку а1 и название книги в ячейку с1"
Exit Sub
End If
my_cheking = Array("\", "/", ":", "*", "?", "<", ">", "|")
For i = 1 To UBound(my_cheking)
If InStr([a1] & [c1], my_cheking(i)) <> 0 Then
MsgBox "Сначала удалите символы \/:*?<>| в ячейках а1, с1"
Exit Sub
End If
Next i
path_to_save = "D:\Ортодонтия\" & [a1]
Debug.Print path_to_save
name_to_save = [c1]
Debug.Print name_to_save
On Error GoTo er
If Dir(path_to_save, vbDirectory) = "" Then MkDir (path_to_save)
ActiveSheet.Copy
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=path_to_save & "\" & name_to_save, FileFormat:=51
Application.DisplayAlerts = False
Workbooks(name_to_save & ".xlsx").Close True
Application.ScreenUpdating = True
MsgBox "Этот лист успешно сохранен в отдельную книгу!"
Exit Sub
er: MsgBox "Проверьте наличие основных подкаталогов в файловой системе!"
End Sub
Nordheim, судя по Вашим сверхскоростным макросам, уровень Ваш с каждой секундой все ближе к профи:) Если у Вас вдруг остался код, где также суммируются остатки, скиньке его тоже, пожалуйста) Большое спасибо за ссылку. Перешел что бы добавить в закладки и засел на добрый час) Очень интересный ресурс.
Уважаемый Nordheim, 6,5 секунд) фантастика!!)) эх, есть чего у Вас поучиться) с удовольствием купил бы Вашу книгу с вашими лайфхаками работы vba excel:) массивы и словари - такой великолепный инструмент) ПРЕБЛАГОДАРЕН ВАМ, Nordheim!!!!
Цитата
Nordheim написал: Но немного не понятно почему берется первое количество , а не сумма. Или в приоритете то количество которое стоит в таблице выше?
в идеале что бы подставлялась сумма остатков, которая соответствует общему значению "Код" и "Код_главного". Но вот если, к примеру, нужно будет подставлять текстовые строки, тогда их значения нужно объединять и разделять "; ". Такая доработка почему-то кажется очень сложной... Но это было бы конечно же идеально и очень правильно!)
Думаю, что следует еще попробовать массивы записать в словари и уже проводить проверку между словарями, и при совпадении производить запись найденного значения в новый массивв нужную строку и только в самом конце выгружать массив. Это должно существенно ускорить код. Но т.к. только начал разбираться в массивах, мои попытки оптимизации кода пока не привели к желаемому результату(Быстрому выполнению макроса).
Sub Макрос1()
Application.ScreenUpdating = False
t = Timer
'диапазон где искать и что найти
a = Workbooks("пример11.xlsm").Worksheets("1").Range(Workbooks("пример11.xlsm").Worksheets("1").Range("a4"), Workbooks("пример11.xlsm").Worksheets("1").Range("a" & Rows.Count).End(xlUp)).Resize(, 4).Value
'диапазон, что искать(диапазон условий)
my_array = Workbooks("пример11.xlsm").Worksheets("2").Range(Workbooks("пример11.xlsm").Worksheets("2").Range("a1"), Workbooks("пример11.xlsm").Worksheets("2").Range("a" & Rows.Count).End(xlUp)).Resize(, 2).Value
For j = 1 To UBound(my_array)
b = my_array(j, 1) 'условие, что искать 1. указываем по каким столбцам диапазона искать
c = my_array(j, 2) 'условие, что искать 2. указываем по каким столбцам диапазона искать
For i = 1 To UBound(a)
If CStr(a(i, 1)) = CStr(b) And CStr(a(i, 3)) = CStr(c) Then 'в диапазоне, где искать указываем по каким столбцам диапазона
Workbooks("пример11.xlsm").Worksheets(2).Cells(j, 3).Value = a(i, 4) 'куда вставлять найденное = что вставлять
Exit For
End If
Next i
Next j
Application.ScreenUpdating = True
Debug.Print Timer - t
End Sub
на небольшом файле отрабатывает хорошо, быстро. Файл побольше отрабатывает СЛИШКОМ ДОЛГО, к сожалению((( А скриптом хотелось выиграть по времени, по сравнению с формулами( =индекс(поискпоз)) ).
Nordheim, прикольно, спасибо за оптимизацию! теперь одновременно два массива не висят в памяти. работать тоже стало быстрее на 19%. 1,7 против 2,1 сек.(643000 строк * 5 столбцов и 1400 критериев )
Nordheim, какой у Вас характер спортивный))) Результат просто великолепный 643000 строк * 5 столбцов и 1400 критериев отработало за 2 секунды!!!! СПАСИБО!!!!!!!!!!!!!!
Nordheim, протестировал код. на небольшом массиве данных фильтрует быстро и хорошо. С задачей 643000 * 5 - код никак не справляется. Обрабатывает слишком медленно и в результате ничего не находит.
Казанский, хотелось, что бы было поменьше выгрузок на лист. Есть один массив, есть второй. Что нужно отфильтровалось и выгрузили готовенькое. Да и всем пользователям vba, думаю, будет круто иметь такой удобный инструмент. Еще бы расширить функцию, что бы можно было фильтровать значения больше, мешьне, между...
Конечно, если что, будем выгружать на лист и фильтровать.