В случаях, когда требуется выполнить однотипные операции с большим количеством файлов Excel (будь то сбор данных или внесение изменений) проще всего использовать скрипт Excel-VBA. В статье приведены примеры таких скриптов и пошагово объяснёна их работа.
Для того, чтобы перебрать все файлы .xls и .xlsx в папке C:/Folder/ можно использовать следующий код:
sFolder = "C:/Folder/"
sFolder = sFolder & IIf(Right(sFolder, 1) = Application.PathSeparator, "", Application.PathSeparator)
sFiles = Dir(sFolder & "*.xls*")
Do While sFiles <> ""
Workbooks.Open sFolder & sFiles
'what to do in each file
ActiveWorkbook.Close False
'write data
sFiles = Dir
Loop
В первом блоке мы задаём целевую папку и добавляем разделительный символ / к адресу в случае, если мы забыли его указать. Это делается по причине того, что чаще всего мы копируем путь из проводника и там этот символ в конце отсутствует.
sFolder = "C:/Folder/"
sFolder = sFolder & IIf(Right(sFolder, 1) = Application.PathSeparator, "", Application.PathSeparator)
Следующая команда открывает первый файл, находящийся в целевой папке. Для того, чтобы был открыт именно файл Excel мы прописываем в качестве аргумента соединение двух строк: sFolder & ".xls*". Звёздочки в данном случае интерпретируются как любое количество любых символов. Таким образом мы открываем все файлы, оканчивающиеся на ."xls"+ любые другие символы. Если нам было бы нужно перебрать только файлы с определёнными символами в имени, то аргумент мог бы выглядеть так: sFolder &"*Шаблон_имени_файла*"& ".xls*"
sFiles = Dir(sFolder & "*.xls*")
В заключительной части мы перебираем все остальные файлы, попадающие под заданный шаблон:
Do While sFiles <> ""
Workbooks.Open sFolder & sFiles
'what to do in each file
ActiveWorkbook.Close False
'write data
sFiles = Dir
Loop
Очевидно, что перебор осуществляется с помощью цикла While. Цикл работает пока переменная sFiles не будет равна пустой строке "". Предпоследняя строка - повторное использование функции Dir без аргумента присваивает переменной sFiles название пути и имени следующего файла, имя которого соответствует шаблону. В случае, если под заданный шаблон не больше не попадает файлов, переменная функция Dir присваивает переменной sFiles значение "", что приводит к остановке цикла.