Получить список файлов Excel в папке с помощью VBA

Мне нужно получить имена всех файлов Excel в папке, а затем внести изменения в каждый файл. Я разобрался с частью «внести изменения». Есть ли способ получить список файлов .xlsx в одной папке, скажем, D: Personal , и сохранить его в массиве строк.

Затем мне нужно перебрать список файлов и запустить макрос для каждого из файлов, которые, как я полагал, я могу сделать, используя:

  Filepath =  "D:  Personal " для каждого i в FileArray Workbooks.Open (Filepath + i) Next  

Я смотрел на это, однако мне не удалось открыть файлы вызывают сохранение имен в формате Variant .

Короче говоря, как я могу использовать VBA для получения списка имен файлов Excel в определенной папке?


Хорошо, это может сработать для вас, функция, которая принимает путь и возвращает массив имен файлов в папке. Вы можете использовать оператор if для получения только файлов Excel при циклическом просмотре массива.

  Файлы списка функций (ByVal sPath As String) Dim vaArray As Variant Dim i As Integer Dim oFile  As Object Dim oFSO As Object Dim oFolder As Object Dim oFiles As Object Set oFSO = CreateObject ("Scripting.FileSystemObject") Set oFolder = oFSO.GetFolder (sPath) Set oFiles = oFolder.Files If oFiles.Count = 0 Then Exit Function ReDim  vaArray (1 To oFiles.Count) i = 1 для каждого oFile в oFiles vaArray (i) = oFile.Name i = i + 1 Next listfiles = vaArrayEnd Function  

Это будет было бы хорошо, если бы мы могли просто получить доступ к файлам в объекте files по номеру индекса, но это, похоже, не работает в VBA по какой-либо причине (ошибка?).


Вы можете использовать встроенную функцию Dir или FileSystemObject.

  • Функция Dir: VBA: Функция Dir

  • FileSystemObject: VBA: FileSystemObject — Коллекция файлов

У каждого из них есть свои собственные сильные и слабые стороны.

Функция Dir

Функция Dir — это встроенный легкий метод для получения списка файлы. Преимущества его использования:

  • Простота использования
  • Хорошая производительность (быстро)
  • Поддержка подстановочных знаков

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

  Public Sub ListFilesDir (ByVal sPath As String, Optional ByVal sFilter As String) Dim sFile As String If Right (sPath, 1)  "" Then sPath = sPath & "" End If If sFilter = "" Then sFilter = "*. *" End If 'вызов с указанием пути "инициализирует" функцию dir и возвращает имя первого файла sFile = Dir (sPath  & sFilter) 'вызовите его снова, пока не останется больше файлов. Выполните до sFile = "" Debug.Print sFile' последующие вызовы без параметра return имя следующего файла sFile = Dir LoopEnd Sub  

Если вы измените любой из файлов внутри цикла, вы получите непредсказуемые результаты.. Лучше прочитать все имена в массиве строк, прежде чем выполнять какие-либо операции с файлами. Вот пример, основанный на предыдущем. Это функция, которая возвращает массив строк:

  Открытая функция GetFilesDir (ByVal sPath As String, _ Optional ByVal sFilter As String) As String () 'динамический массив для имен Dim  aFileNames () As String ReDim aFileNames (0) Dim sFile As String Dim nCounter As Long If Right (sPath, 1)  "" Then sPath = sPath & "" End If If sFilter = "" Then sFilter = "*  . * "End If 'вызов с путем" инициализирует "функцию dir и возвращает первый файл sFile = Dir (sPath & sFilter)' вызывайте его до тех пор, пока не будет возвращено имя файла Do While sFile " "'сохранить имя файла в  массив aFileNames (nCounter) = sFile 'последующие вызовы без параметра return следующий файл sFile = Dir' убедитесь, что ваш массив достаточно велик для другого nCounter = nCounter + 1 Если nCounter> UBound (aFileNames) Then 'сохраните значения и увеличьте разумным  сумма для производительности ReDim Preserve aFileNames (UBound (aFileNames) + 255) End If Loop 'усекает массив до нужного размера Если nCounter  

Объект файловой системы

Объект файловой системы — это библиотека для операций ввода-вывода, которая поддерживает объектную модель для управления файлами. Плюсы этого подхода:

  • Intellisense
  • Надежная объектная модель

Вы можете добавить ссылку на «Объектную модель хоста сценариев Windows» (или «Среду выполнения сценариев Windows») и объявите свои объекты следующим образом:

  Public Sub ListFilesFSO (ByVal sPath As String) Dim oFSO  Как FileSystemObject Dim oFolder As Folder Dim oFile As File Set oFSO = New FileSystemObject Set oFolder = oFSO.GetFolder (sPath) Для каждого oFile в oFolder.Files Debug.Print oFile.Name Next 'oFile Set oFile = Name  = NothingEnd Sub  

Если вам не нужен intellisense, вы можете сделать это, не задавая ссылку:

  Public Sub ListFilesFSO  (ByVal sPath As String) Dim oFSO As Object Dim oFolder As Object Dim oFile As Object Set oFSO = CreateObject ("Scripting.FileSystemObject") Установить oFolder = oFSO.GetFolder (sPath) Для каждого oFolder в oFolder.Files Debug.Prr.  Name Next 'oFile Set oFile = Ничего не установлено oFolder = Ничего не установлено oFSO = Not  hingEnd Sub  


Вы можете использовать встроенную функцию Dir или FileSystemObject.

  • Функция Dir: VBA: Функция Dir

  • FileSystemObject: VBA: FileSystemObject — Файлы Коллекция

У каждого из них есть свои сильные и слабые стороны.

Функция Dir

Функция Dir — это встроенный легкий метод для получения списка файлов. Преимущества его использования:

  • Простота использования
  • Хорошая производительность (быстро)
  • Поддержка подстановочных знаков

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

  Public Sub ListFilesDir (ByVal sPath As String, Optional ByVal sFilter As String) Dim sFile As String If Right (sPath, 1)  "" Then sPath = sPath & "" End If If sFilter = "" Then sFilter = "*. *" End If 'вызов с указанием пути "инициализирует" функцию dir и возвращает имя первого файла sFile = Dir (sPath  & sFilter) 'вызовите его снова, пока не останется больше файлов. Выполните до sFile = "" Debug.Print sFile' последующие вызовы без параметра return имя следующего файла sFile = Dir LoopEnd Sub  

Если вы измените любой из файлов внутри цикла, вы получите непредсказуемые результаты. Лучше прочитать все имена в массиве строк, прежде чем выполнять какие-либо операции с файлами. Вот пример, основанный на предыдущем. Это функция, которая возвращает массив строк:

  Открытая функция GetFilesDir (ByVal sPath As String, _ Optional ByVal sFilter As String) As String () 'динамический массив для имен Dim  aFileNames () As String ReDim aFileNames (0) Dim sFile As String Dim nCounter As Long If Right (sPath, 1)  "" Then sPath = sPath & "" End If If sFilter = "" Then sFilter = "*  . * "End If 'вызов с путем" инициализирует "функцию dir и возвращает первый файл sFile = Dir (sPath & sFilter)' вызывайте его до тех пор, пока не будет возвращено имя файла Do While sFile " "'сохранить имя файла в  массив aFileNames (nCounter) = sFile 'последующие вызовы без параметра return следующий файл sFile = Dir' убедитесь, что ваш массив достаточно велик для другого nCounter = nCounter + 1 Если nCounter> UBound (aFileNames) Then 'сохраните значения и увеличьте разумным  сумма для производительности ReDim Preserve aFileNames (UBound (aFileNames) + 255) End If Loop 'усекает массив до нужного размера Если nCounter  

Объект файловой системы

Объект файловой системы — это библиотека для операций ввода-вывода, которая поддерживает объектную модель для управления файлами.. Плюсы этого подхода:

  • Intellisense
  • Надежная объектная модель

Вы можете добавить ссылку на «Объектную модель хоста сценариев Windows» (или «Среду выполнения сценариев Windows») и объявите свои объекты следующим образом:

  Public Sub ListFilesFSO (ByVal sPath As String) Dim oFSO  Как FileSystemObject Dim oFolder As Folder Dim oFile As File Set oFSO = New FileSystemObject Set oFolder = oFSO.GetFolder (sPath) Для каждого oFile в oFolder.Files Debug.Print oFile.Name Next 'oFile Set oFile = Name  = NothingEnd Sub  

Если вам не нужен intellisense, вы можете сделать это, не задавая ссылку:

  Public Sub ListFilesFSO  (ByVal sPath As String) Dim oFSO As Object Dim oFolder As Object Dim oFile As Object Set oFSO = CreateObject ("Scripting.FileSystemObject") Установить oFolder = oFSO.GetFolder (sPath) Для каждого oFolder в oFolder.Files Debug.Prr.  Name Next 'oFile Set oFile = Ничего не установлено oFolder = Ничего не установлено oFSO = Not  hingEnd Sub  

  Dim iIndex as IntegerDim ws As Excel.WorksheetDim wb  Как WorkbookDim strPath Как StringDim strFile As StringstrPath = "D:  Personal " strFile = Dir (strPath & "* .xlsx") Do While strFile  "" Установить wb = Workbooks.Open (Filename: = strPath & strFile) For  iIndex = 1 В wb.Worksheets.count Установите ws = wb.Worksheets (iIndex) 'Сделайте что-нибудь здесь.  Next iIndex strFile = Dir 'Это перемещает значение strFile в следующий файл. Цикл  


  Dim iIndex as IntegerDim ws As Excel.WorksheetDim wb As WorkbookDim strPath As StringDim strFile As StringstrPath = "D:  Personal " strFile = Dir (strPath & "* .xlsx") Do While strFile   "" Установить wb = Workbooks.Open (Filename: = strPath & strFile) For iIndex = 1 To wb.Worksheets.count Set ws = wb.Worksheets (iIndex) 'Сделайте что-нибудь здесь.  Next iIndex strFile = Dir 'Это перемещает значение strFile в следующий файл. Цикл  

Если вам нужно только имя файла без расширения

  Dim fileNamesCol As New CollectionDim MyFile As Variant 'Строки и примитивные типы данных не допускаются с помощью collectionfilePath = "c:   file directory "+"  "MyFile = Dir $ (filePath &" * .xlsx ") Сделать, пока MyFile " "fileNamesCol.Add (Replace (MyFile," .xlsx "," ")) MyFile = Dir $ Loop   

Для вывода на лист Excel

  Dim myWs As Worksheet: Set myWs = Sheets ("SheetNameToDisplayTo") Dim ic As Integer  : ic = 1Для каждого MyFile В fileNamesCol myWs.Range ("A" & ic) .Value = fileNamesCol (ic) ic = ic + 1Next MyFile  

В основном на основе подробно описанной техники здесь: https://wordmvp. com/FAQs/MacrosVBA/ReadFilesIntoArray.htm


Если вам нужно только имя файла без расширения

   Dim fileNamesCol As New CollectionDim MyFile As Variant 'Строки и примитивные типы данных не допускаются с помощью collectionfilePath = "c:  file directory" + "" MyFile = Dir $ (filePath & "* .xlsx")  Выполнить Пока MyFile  "" fileNamesCol.Add (Replace (MyFile, ".xlsx", "")) MyFile = Dir $ Loop  

Для вывода на лист Excel

  Dim myWs As Worksheet: Set myWs = Sheets ("SheetNameToDisplayTo") Dim ic As Integer: ic = 1 для каждого MyFile в fileNamesCol myWs.Range ("A" & ic) .Value =  fileNamesCol (ic) ic = ic + 1Next MyFile  

В основном на основе метода, описанного здесь: https://wordmvp.com/FAQs/MacrosVBA/ReadFilesIntoArray.htm


Что касается одобренного ответа, мне он понравился, за исключением того, что если итоговые «файлы списка «массив используется в формуле массива {CSE}, значения списка выводятся в горизонтальной строке. Чтобы они отображались в вертикальном столбце, я просто сделал массив двухмерным, как показано ниже:

  ReDim vaArray (1 To oFiles.Count, 0) i = 1For Each oFile  В oFiles vaArray (i, 0) = oFile.Name i = i + 1Next  


Что касается ответа, за который проголосовали, мне он понравился за исключением того, что если результирующий массив «listfiles» используется в формуле массива {CSE}, все значения списка выводятся в горизонтальной строке. Чтобы они отображались в вертикальном столбце, я просто сделал массив двухмерным, как показано ниже:

  ReDim vaArray (1 To oFiles.Count, 0) i = 1For Each oFile  В oFiles vaArray (i, 0) = oFile.Name i = i + 1Next  

   Sub test () Dim FSO As Object Set FSO = CreateObject ("Scripting.FileSystemObject") Установить папку1 = FSO.GetFolder (FromPath) .Files FolderPath_1 = "D:  Arun  Macro Files  UK Marco  External  Инструмент продаж для Au  Example Files  "Рабочие книги. Добавить набор Movenamelist = ActiveWorkbook для каждого файла в папке1 Movenamelist. Активировать диапазон (" A100000 "). Конец (xlUp) .Offset (1, 0) .Value = fil ActiveCell.Offset (  1, 0). Выберите NextEnd Sub  


  Подтест (  ) Dim FSO As Object Set FSO = CreateObject ("Scripting.FileSystemObject") Установить папку1 = FSO.GetFolder (FromPath) .Files FolderPath_1 = "D:  Arun  Macro Files  UK Marco  Exte  rnal Инструмент продаж для Au  Example Files  "Рабочие книги. Добавить Set Movenamelist = ActiveWorkbook для каждого файла в folder1 Movenamelist.Activate Range (" A100000 "). End (xlUp) .Offset (1, 0) .Value = fil ActiveCell.Offset  (1, 0). Выберите NextEnd Sub  

Оцените статью
techsly.ru
Добавить комментарий