Помогите плиз правильно написать макрос обновления данных из разных файлов

[Legame]

добрый ночи,

есть задачи, которые я к сожалению не могу решить ввиду недостаточных знаний. если для вас все тривиально, огромная просьба помочь...

необходимо создать макрос:
обновления данных(ЦЕНЫ НА ТОВАР) в исходный файл(на разные листы) из нескольких разных файлов
(здесь я абсолютно не знаю как правильнее - должны ли быть открыты эти файлы при выполнении макроса)

как я это себе представляю:
1. поиск осуществляется по артикулу позиции (которая может быть текстовая или числовая)
2. обновление будет производится построчно (в выбранных или прописанных листах книги)
3. при этом лучше всего было бы обновлять сразу все значения для выбранной позиции (в этом случае - во всех файлах поставщиков)
3.0. возможно, что проще создать несколько макросов обновлений(я бы назвал их по-листовыми или по-диапазонными) - просто не знаю как правильнее делать
по-диапозынными - т.е. в выделенном диапазоне пользователем или он задан определенными границами(строка с, по)
3.1. может быть такая ситуация - что искомая позиция отсутствует в файле от какого-либо поставщика(например ее сейчас нет или больше не завозится)
3.2. файлы поставщиков абсолютно разные(т.е. написать универсальный ПОИСК для всех не получится, к примеру в одном файле требуемые данные находятся в одном столбце, а в остальных иначе)
3.3. не для каждого товара есть артикул - необходимо исключить поиск таких позиций

вложены файлы:
общий.xls - для сбора данных
поставщик1.xls - источник данных1
поставщик2.xls - источник данных2
поставщик3.xls - источник данных3

P.S. возможно условия неккорректны, или я их плохо описал...

если не трудно - помогите, плиз!!!

[EducatedFool]

3.2. файлы поставщиков абсолютно разные(т.е. написать универсальный ПОИСК для всех не получится, к примеру в одном файле требуемые данные находятся в одном столбце, а в остальных иначе)

Это плохо, конечно, что файлы разные...
Всё зависит от того, насколько сильно эти файлы отличаются друг от друга.

Если все файлы будут иметь структуру,аналогичную файлам поставщик1.xls, поставщик2.xls, поставщик3.xls, - то не всё так плохо, поскольку макрос легко найдёт нужные столбцы по их заголовкам (в каждом из этих файлов есть столбцы с заголовками артикул, наименование, цена).
Если же заголовки столбцов могут отсутствовать (или будут другие заголовки) - то возникнут сложности...

3. при этом лучше всего было бы обновлять сразу все значения для выбранной позиции (в этом случае - во всех пяти файлах поставщиков)

Я-то думал, надо просто собирать значения из всех этих файлов в один (общий.xls)... Так требуется вносить изменения в файлы поставщиков, или нет?

возможно, что проще создать несколько макросов обновлений

Думаю, хватит одного макроса.

должны ли быть открыты эти файлы при выполнении макроса

В принципе, не важно, открыты файлы, или нет - макрос их откроет и закроет при необходимости.
Но надо знать, из каких файлов брать данные - либо все эти файлы должны быть в одной папке, и путь к этой папке должен быть известен (макрос переберёт все файлы в этой папке), либо все файлы-источники данных должны быть открыты (макрос переберёт все открытые файлы в поисках данных)


В общем, задача решаемая, но разбираться долго.
Потом потребуются дополнительные разъяснения.

Необходимо прикрепить к сообщению оригинальные файлы (хотя бы 3 - 4 шт.) - чтобы потом 10 раз не переписывать макросы (возможно, формат оригинальных файлов отличается от приведённых Вами образцов)

Считать данные из файлов-источников в принципе не проблема.
А вот как размещать эти данные в файле общий.xls - не совсем понятно...
Особенно, если размещать эти данные требуется на разные листы.

Я правильно понял, что макрос будет помещать данные только в столбцы F, G и H?

может быть такая ситуация - что искомая позиция отсутствует в файле от какого-либо поставщика

В отсутствующими строками томе не совсем понятно - добавлять соответствующие строки в файл общий.xls, или нет?

[Legame]

Добрый день, EducatedFool
огромное спасибо за подробный комментарий!

Если все файлы будут иметь структуру,аналогичную файлам поставщик1.xls, поставщик2.xls, поставщик3.xls, - то не всё так плохо, поскольку макрос легко найдёт нужные столбцы по их заголовкам (в каждом из этих файлов есть столбцы с заголовками артикул, наименование, цена).
Если же заголовки столбцов могут отсутствовать (или будут другие заголовки) - то возникнут сложности...
Заголовки будут в большинстве файлов(если что их можно ручками дописать), либо зная изначально структуру файла можно указать столбец заранее


Я-то думал, надо просто собирать значения из всех этих файлов в один (общий.xls)... Так требуется вносить изменения в файлы поставщиков, или нет?
Вносить изменения не требуется. Все верно, необходимо только собирать значения и сохранять их в итоговый файл


Но надо знать, из каких файлов брать данные - либо все эти файлы должны быть в одной папке, и путь к этой папке должен быть известен (макрос переберёт все файлы в этой папке), либо все файлы-источники данных должны быть открыты (макрос переберёт все открытые файлы в поисках данных)
все файлы будут в одной папке

В общем, задача решаемая, но разбираться долго.
если не очень сложно, помогите плиз

Необходимо прикрепить к сообщению оригинальные файлы (хотя бы 3 - 4 шт.) - чтобы потом 10 раз не переписывать макросы (возможно, формат оригинальных файлов отличается от приведённых Вами образцов)
да. частично отличается. попробую вечером вложить файлы образцы

Считать данные из файлов-источников в принципе не проблема.
А вот как размещать эти данные в файле общий.xls - не совсем понятно...
Особенно, если размещать эти данные требуется на разные листы.
задача разместить их в строке соответствующего товара поиска на пересечении столбца файла поставщика, в котором ищем значение

Я правильно понял, что макрос будет помещать данные только в столбцы F, G и H?
Да, все верно

В отсутствующими строками томе не совсем понятно - добавлять соответствующие строки в файл общий.xls, или нет?[/QUOTE]
Ой, я здесь не совсем понял о чем идет речь

[Legame]

Доброй ночи,

Необходимо создать макрос... (назовем его Sony для сбора/обновления цен на продукцию Sony)

Задача макроса: установить/обновить данные цен в соответствующих ячейках куммулятивного файла (общий.xls) из 3х разных файлов (поставщик1.xls, поставщик2.xls, поставщик3.xls)

Поиск: огранизовывается по артикулу (из файла обший.xls) на листах заранее заданных пользователем (в нашем случае это "бытовая", "техника") и в диапазоне заранее заданном пользователем (ибо не имеет смысла искать по всем строкам листов; да и по смыслу задачи по строкам ниже ячейки с артикулами будут окрашены красным цветом)

При нахождении совпадения: проставлем цену из файлов поставщиков*.xls в ячейки файла обший.xls на пересечнии строки и соответствующего поставщика (F-H колонки)
поставщик1: дилер B - колонка D
поставщик2: Dialer Price - колонка С
поставщик3: Dialer 2 - колонка H

При отсутствии совпадения: выделяем ячейку файла общий.xls, содержащую искомый артикул красным цветом


дополнительные ситуации или исключения:

1. считаем что все файлы открыты и "лежат" относительно файла обший.xls(корневая папка) Price\Sony
при этом если не сложно подскажите плиз как сделать так, чтобы макрос работал и с закрыми файлами-источниками!?

2. диапазон обновлений цен для макроса Sony:
лист "техника" - строка 9-15 и строка 26-27
лист "техника" - строка 9-15

3. операция сравнения артикулов должна быть "абсолютной"
(к примеру: артикул TVSony_2 содержат все файлы поставщиков, но с небольшим добавлением в конце артикула - TVSony_25. в данном случае принимаем за разные товары)


Огромная просьба, если не сложно - то немного прокомментировать написанный макрос на выполняемые действия... т.к. в последствии я его буду изменять и хотелось бы разобраться самому тоже. заранее спасибо!

P.S. возможно я не все случаи предусмотрел ввиду недостаточных знаний или логики языка программирования. прошу помочь...

[EducatedFool]

Вот что получилось:



1) Откройте файл Общий.xls
2) Нажмите красную кнопочку (необязательно)
3) Нажмите зелёную кнопочку

Основной макрос не нуждается в комментариях:

Sub СборДанныхИзРазныхФайлов()
    ОткрытьПрайсыПоставщиков
    ОчисткаЛиста
    ЗаполнениеСпискаПостащиков
    ПоискЦен
    ЗакрытьПрайсыПоставщиков Листы
End Sub

К остальным макросам комментарии написать не смог, потому что сам с трудом представляю, как они работают

Будут вопросы или предложения по модернизации макроса - обращайтесь.

[Legame]

Сообщение от EducatedFool

Вот что получилось:
Будут вопросы или предложения по модернизации макроса - обращайтесь.

EducatedFool, огромное спасибо за столь быстрое решение задачи...
насчет модернизации - да есть небольшие уточнения и дополнения - если не тяжело попробуйте помочь?


Вопросы:
1. как я понял обновление данных происходит только на активном листе? (в принципе это даже удобнее нежели следить за разными листами общей книги)

2. переменные
Public Путь As String, sh As Worksheet, Листы As Collection, ash As Worksheet
sh - тип рабочий лист? используется для ... ?
sh - тип рабочий лист? используется для ... ?
Листы - тип коллекция ? используется для получения 1-ых листов в файлах поставщиков?

3. Поиск значения в файлах поставщиков
НайтиАртикулНаЛисте = sh.UsedRange.Find(art, , , xlWhole, , , False).EntireRow.Cells(100).End(xlT oLeft).Value
по сути дела мы выбираем самую крайнюю правую колонку из прайсов поставщиков?
что делать если в каком-либо из прайсов могут еще содержаться данные? (хотя можно просто удалить лишние столбцы )

Дополнения:
1. Set Листы = ОткрыватьВсеФайлы(Путь)
скорее всего нет необходимости открывать все файлы из данной папки, достаточно только несколько прописанных заранее...
возможно ли переопределение на три определенных файла названных одноименно "поставщик1.xls", "поставщик2.xls", "поставщик3.xls"?
т.к. в дальнейшем предполагается изменение имен файлов

+известно, что данные будут сохраняться в определенных столбцах(это изначально задается). как узнать какой файл из открытых будет сохранять значение в первом столбце, какой во втором, и какой в третьем?

2. Выделение ячейки цветом
При занесении значения из файлов поставщиков в ячейку:
в случае занесения значения - окрашиваем ячейку в белый цвет
в случае отсутствия данных для занесения - ставим значение 0 и окрашиваем ячейку в красный цвет
при этом тогда не потребуется выполнять первоначальную очистку...

3. поиск значений происходил по определенным(заданным)
строкам:
в случае данных на листе "техника" поиск осуществляется только в строках 9-15 и 26-27.
т.е. возможно ли заранее указать поиск по нескольким определенным диапазонам? (диапазонов на листе может быть несколько)

+также может возникнуть ситуация когда в поле арткула может появиться надпись не относящаяся к артикулу - например объединение группы товаров (при создании структуры)

вложил еще раз файл для компоновки общий.xls

[EducatedFool]

Пока попробую ответить на часть вопросов, потом буду переделывать макрос.

как я понял обновление данных происходит только на активном листе?

Да. Можно сделать обновление сразу на всех (или определённых Вами) листах. Я подумал, что для одного листа будет удобнее.
Кнопку макроса можно вывести на панель инструментов, и после переключения на другой лист её нажимать.

2. переменные

Оно Вам надо? Работает, да и ладно
Макрос написать требуется полчаса, а объяснить, как он работает - час.
Так что выбирайте - или я пишу Вам макросы, или я объясняю как они работают

sh - тип рабочий лист? Да
используется для ... ? для всего подряд используется во всех макросах

ash - тип рабочий лист? Да
используется для ... ? хранит ссылку на лист, с которого запускали макрос (в файле "общий"). При открытии файлов активными становятся другие листы, а нам требуется часто обращаться именно к исходному листу

Листы - тип коллекция ? используется для получения 1-ых листов в файлах поставщиков?
Абсолютно верно.

3. Поиск значения в файлах поставщиков
по сути дела мы выбираем самую крайнюю правую колонку из прайсов поставщиков?

Вы угадали

что делать если в каком-либо из прайсов могут еще содержаться данные?

А что делать, если цены не справа, а слева? Или снизу?
Или их вообще там нет? А если они зашифрованы?

Всего не предусмотришь...
В тех трёх файлах данный метод работал.
Будут файлы с другой структурой - будем думать, как это обойти.

[Legame]

Сообщение от EducatedFool

Пока попробую ответить на часть вопросов, потом буду переделывать макрос.


Да. Можно сделать обновление сразу на всех (или определённых Вами) листах. Я подумал, что для одного листа будет удобнее.
Кнопку макроса можно вывести на панель инструментов, и после переключения на другой лист её нажимать.

с этим полностью согласен. вариант обновления на активном листе устраивает!!!

Оно Вам надо? Работает, да и ладно
Макрос написать требуется полчаса, а объяснить, как он работает - час.
Так что выбирайте - или я пишу Вам макросы, или я объясняю как они работают

EducatedFool, Я естественно за выбор - помощь в написании макросов!!!
все-таки хотелось самому понять, но мне немного тяжело, поэтому и задаю глупые и непонятные мне вопросы


Всего не предусмотришь...
В тех трёх файлах данный метод работал.
Будут файлы с другой структурой - будем думать, как это обойти.

просьба помочь со следующим кусочком задачи: (почему-то уверен с вероятностью 99% , что это последняя часть общей работы)
Дополнения:
1. Set Листы = ОткрыватьВсеФайлы(Путь)
скорее всего нет необходимости открывать все файлы из данной папки, достаточно только несколько прописанных заранее...
*** возможно ли переопределение на три определенных файла названных "Postav_1.xls", "Postavshik2.xls", "Post-3.xls"?
т.к. в дальнейшем предполагается изменение имен файлов
*** Или как вариант: я заранее знаю какие имена файлов будут и данные из этих файлов размещаю в соответствующих столбцах

+как узнать какой файл из открытых будет сохранять значение в первом столбце, какой во втором, и какой в третьем?

2. Выделение ячейки цветом
При занесении значения из файлов поставщиков в ячейку:
в случае занесения значения - окрашиваем ячейку в белый цвет
в случае отсутствия данных для занесения - ставим значение 0 и окрашиваем ячейку в красный цвет
при этом тогда не потребуется выполнять первоначальную очистку...

3. поиск значений происходил по определенным(заданным)
строкам:
в случае данных на листе "техника" поиск осуществляется только в строках 9-15 и 26-27.
т.е. возможно ли заранее указать поиск по нескольким определенным диапазонам? (диапазонов на листе может быть несколько)

+также может возникнуть ситуация когда в поле арткула может появиться надпись не относящаяся к артикулу - например объединение группы товаров (при создании структуры)

вложил еще раз файлы...

EducatedFool, помогите плиз если для вас это не столь сложно

[EducatedFool]

также может возникнуть ситуация когда в поле арткула может появиться надпись не относящаяся к артикулу - например объединение группы товаров (при создании структуры)

Этот случай не рассматривал, так как Вы не указали признаки наличия объединения в ячейке артикула (как макросу отличить, один там артикул или несколько)
Да, скорее всего, это и не потребуется - всё равно для этого объединённого артикула не найдется цен в прайсе.

в случае данных на листе "техника" поиск осуществляется только в строках 9-15 и 26-27.
т.е. возможно ли заранее указать поиск по нескольким определенным диапазонам? (диапазонов на листе может быть несколько)

Опять же, не указано, как определять обрабатываемые строки...
Сделал так:

Const ПризнакОбрабатываемойЯчейки = "sony"    
' обрабатываются только строки, содержащие этот текст во 2-м столбце

Const ОбрабатыватьВсеСтроки = False  
' если false - обрабатываются только строки, содержащие ПризнакОбрабатываемойЯчейки
' если ОбрабатыватьВсеСтроки = true - обрабатываются все строки (не учитывается ПризнакОбрабатываемойЯчейки)

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

возможно ли переопределение на три определенных файла названных "Postav_1.xls", "Postavshik2.xls", "Post-3.xls"?
т.к. в дальнейшем предполагается изменение имен файлов

Возможно всё
Не устраивает универсальный макрос (который обрабатывает неограниченное количество файлов с прайсами) - пользуйтесь ограниченным вариантом.
Имена файлов и порядок их обработки можно изменить здесь:

Const ДобавочныйПуть = "Prices\Sony\"
Const Прайс1 = "Postav_1.xls", Прайс2 = "Postavshik2.xls", Прайс3 = "Post-3.xls"

Поиск файла производится так: берётся путь файла "общий", и к нему присоединяется сначала ДобавочныйПуть, а потом имя файла Прайс1

Вы можете манипулировать этими константами как угодно.
Например, так:

Const ДобавочныйПуть = "Prices\"
Const Прайс1 = "Sony\Postav_1.xls"
Const Прайс2 = "Sony2\Postavshik2.xls"
Const Прайс3 = "Sony3\Post-3.xls"

или так:

Const ДобавочныйПуть = ""
Const Прайс1 = "Prices1\Sony1\Postav_1.xls"
Const Прайс2 = "Prices2\Sony2\Postavshik2.xls"
Const Прайс3 = "Prices3\Sony3\Post-3.xls"

Со цветами ячеек и условиями отбора строк для проверки разберётесь самостоятельно (там есть комментарии).

Проверяйте:

[hromovea]

Уважаемые программисты, а как можно модифицировать данный прайс и сделать так, чтобы он искал значения не только по одному артикулу ( или прочему условию), а по трем. допустим столбик 1 ( размер упаковки - 2Л, 1,5Л 0,6Л) столбик 2 (название напитка-молоко, сок ) столбик 3 ( кол-во бутылок в упаковке - 12,24, 20).

спасибо от чайника! :-)