Добрый день!
Уважаемые специалисты, долго сидел в читателях и пытался по кусочкам собрать требуемый мне код.
Что-то получалось, но основная идея никак.
Задача: получить данные из сметы (присылает подрядчик) и распределить затраты по заданным правилам для внесения в программу учета основных средств.
Проблемы:
1. смета имеет дурацкий формат и заполняется разными подрядчиками по разному.
2. в смете не используемые строки скрываются макросом, но иногда скрываются и нужные строки.
3. наименование затрат в смете часто отличаются от образца (разные исполнители)
4. в смете присутствуют объединенные ячейки
Однажды у меня получилось собрать код, который более-менее работал, но только один раз за сеанс, т.е. второй раз он уже не подгружал данные. ВИдимо плохо отрабатывала очистка области, куда данные вставлялись. Если эту область очищать PLEXом со всеми галочками, то код отрабатывал. Я грешу на проблему №4. Данные я парсил связкой =Индекс(Поискпоз()), т.к. ПОИСКПОЗ() позволяет искать с "*" и "?" (проблема №3).
вот этот код (сильно не смейтесь, т.к. код не мой, а "франкинштейн"):
Код |
---|
Public Choise As Integer
-----------------------------------------------------------------------------------------------------------
Function GetFilePath() As String
Dim fd As FileDialog ' объявляем переменную
Set fd = Application.FileDialog(msoFileDialogOpen) ' создаем диалог
With fd
.Filters.Clear ' удаляем все стандартные фильтры диалога
.Filters.Add "Файлы Excel", "*.xls*" ' создаем нулевой фильтр
'.Filters.Add "Все MDB ", "*.MDB" ' создаем второй фильтр в данном случае он может быть не нужен
.FilterIndex = 0 ' ставим маркер на 0 фильтр
.AllowMultiSelect = False ' запрещаем мультиселект
.ButtonName = "Выбрать" ' называем кнопку выбора как хотим
End With
If fd.Show = -1 Then
GetFilePath = fd.SelectedItems.Item(1) ' присваиваем функции путь и имя
Else
Exit Function ' если нажата клавиша cancel (кстати тоже выдает ошибку)
End If
Set fd = Nothing ' чистим память
End Function
---------------------------------------------------------------------------------------------------------------
Sub DoItMy()
Dim ActiveWB, NewWB As Workbook
Set ActiveWB = ActiveWorkbook.ActiveSheet
openxlsb = GetFilePath
Application.ScreenUpdating = False
ActiveWB.Cells.ClearContents ' очищаем лист в текущей книге
Choise = 1
Set NewWB = Workbooks.Open(openxlsb, ReadOnly:=True) ' открываем новую книгу
UserForm1.Show ' форма выдает список листов в книге с выбором
Sheets(Choise).Select ' выбираем лист
'Cells.Select ' выделяем диапазон
'Range("A7:N395").Select ' выделяем диапазон
ActiveSheet.Unprotect Password:="1212" 'снимаем защиту листа
Range("A7:N395").Select ' выделяем диапазон
Selection.EntireRow.Hidden = False
Selection.Rows.AutoFit
Dim i As Long
For i = 28 To 376
If Cells(i, 6).Value = 0 Or Cells(i, 6).Value = Empty _
Then
Rows(i).Hidden = True
Else
Rows(i).Hidden = False
End If
Next i
Selection.SpecialCells(xlCellTypeVisible).Select 'выделяем только видимые ячейки
'Selection.UnMerge - закоментил т.к. выдает ошибку
Selection.Copy Destination:=ActiveWB.Range("A1") ' копируем диапазон в нашу книгу
NewWB.Close False 'закрываем новую книгу без сохранения
Application.ScreenUpdating = True
End Sub
|
простите за простыню.
По ходу пьесы я немного изменил для себя задачу и реализовал полуавтоматическую работу (до лучших времен, т.к. мозгов реализовать начальные планы не хватает).
Пользователю предлагается:
1. на первом этапе отметить наличие в смете определенных видов работ в укрупненном виде (например: отметить "фундамент" чекбоксом, если в смете присутствуют работы и материалы связанные с фундаментными работами ФБС, отсыпка, рытьё и т.п.)
2. далее из общего списка всех возможных позиций формируется список "статей затрат" на основе этапа 1 формулами и фильтрами
3. на 3-м этапе (самый сложный для пользователя и для меня) пользователю предлагается заполнить суммы в отфильтрованном списке статей затрат.
Вот этот третий этап я и хочу автоматизировать. Для этого мне надо (тут два варианта):
1-й вариант.
- с листа "Затраты" нажав кнопку "Смета" указать файл сметы и лист в ней и присвоить переменной "FullPath" (либо разбить на 3 "Путь", "Файл", "Лист"), как вариант открыть её и указать дипазон с данными на листе - переменная "Смета".
- написать пользовательскую ф-цию, которая могла заменить связку ИНДЕКС+ПОСИКПОЗ. эта ф-ция (назовем её "ИнПо") берет текст (первый аргумент) для поиска из столбца А на листе "Затраты" (который я заполню "ключами" для поиска, используя "*" и "?" и смогу корректировать не лезя в код) и ищет соответствие в столбце В (второй аргумент) в диапазоне "Смета", возвращает данные из столбца С (третий аргумент).
2-й вариант (продолжение темы с загрузкой данных в книгу с листом "Затраты"):
- также с листа "Затраты" кнопкой "Смета" указываем путь, лист (возможно диапазон, что бы не искать его кодированием)
- проделываем над этим диапазоном ряд действий (см. код): снимаем защиту, отображаем скрытые ячейки, отменяем объединение, устанавливаем всем строкам нормальную высоту (некоторые подрядчики так скрываю ячейки), преобразовываем всё в значения (бывают ссылки на другие листы и т.д.), скрываем/удаляем строки по условию (нет затрат в столбце с работами И в столбце с материалами), выбираем оставшееся и копируем
- создаем в рабочей книге новый лист и вставляем в него данные (закрываем книгу со сметой БЕЗ изменений)
- далее работа пользовательской ф-ции (описал в первом варианте)
- после проверки пользователем (а может и програмно) если сумма в смете совпадает с суммой всех "статей затрат", то лист с данными можно смело удалять, а если не совпадает, то предложить пользователю самостоятельно найти ошибку и исправить её руками, после чего лист тоже можно удалять.
Мне кажется, что я обнаглел с таким вопросом лезть на форум, но сил и знаний не хватает для реализации хотелки. Честное слово: я очень старался несколько месяцев. Старался подробнее расписать ТЗ, т.к. сам терпеть не могу "сделай мне кнопку, что бы красиво".
В паблик образцы файлов боюсь выкладывать, хотя можно их подчистить и кинуть хотябы в личку тому, кто возьмется мне помочь.
Кстати: я не прошу выдать мне готовое решение. Мне необходим толчок в
нужном направлении (я не могу получить данные из другой книги, а
разобрать я их смогу и сам хотябы формулами) и совет: какой вариант будет оптимальнее 1-й или 2-й.
А то решите, что я тут на халяву надеюсь.