Код для прокрутки всех файлов excel в указанной папке и вытаскивание данных из определенных ячеек

Ответ 1

Сначала начните с этот запрос google и щелкните по первой ссылке, которая приведет вас к статья, показывающая, как выполнять итерацию через группу файлов Excel в папке.

Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next
    Set wbCodeBook = ThisWorkbook
        With Application.FileSearch
            .NewSearch
            'Change path to suit
            .LookIn = "C:\MyDocuments\TestResults"
            .FileType = msoFileTypeExcelWorkbooks
            'Optional filter with wildcard
            '.Filename = "Book*.xls"
                If .Execute > 0 Then 'Workbooks in folder
                    For lCount = 1 To .FoundFiles.Count 'Loop through all
                        'Open Workbook x and Set a Workbook variable to it
                        Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)

                        'DO YOUR CODE HERE

                        wbResults.Close SaveChanges:=False
                    Next lCount
                End If
        End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

Чтобы получить название рабочей книги, вам нужно будет адаптировать код в "DO YOUR CODE ЗДЕСЬ", чтобы включить wbResults.Name. Если это имя файла, которое вы хотите, используйте wbResults.FullName, который возвращает имя рабочей книги, включая ее путь на диске, в виде строки.

Поиск вариации VBScript на том же самом дает ряд полезных результатов, включая этот script:

strPath = "C:\PATH_TO_YOUR_FOLDER"

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.DisplayAlerts = False

Set objFso = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFso.GetFolder (strPath)

For Each objFile In objFolder.Files

If objFso.GetExtensionName (objFile.Path) = "xls" Then
   Set objWorkbook = objExcel.Workbooks.Open(objFile.Path)
   ' Include your code to work with the Excel object here
   objWorkbook.Close True 'Save changes
End If

Next

objExcel.Quit

Ответ 2

Я бы сделал это в VBScript или даже, VB.NET или Powershell, если вы так наклонены.

Используя VB.NET, вы можете получить доступ к электронным таблицам Excel, как к базам данных, через поставщика OLEDB. Код для выбора диапазона значений может выглядеть так:

 Try
        Dim MyConnection As System.Data.OleDb.OleDbConnection
        Dim DtSet As System.Data.DataSet
        Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
        MyConnection = New System.Data.OleDb.OleDbConnection _
        ("provider=Microsoft.Jet.OLEDB.4.0;"  _
        " Data Source='testfile.xls'; " _
         "Extended Properties=Excel 8.0;")
        MyCommand = New System.Data.OleDb.OleDbDataAdapter _
            ("select * from [Sheet1$]", MyConnection)
        MyCommand.TableMappings.Add("Table", "TestTable")
        DtSet = New System.Data.DataSet
        MyCommand.Fill(DtSet)
        MyConnection.Close()
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try

После того, как вы получите данные, которые вы можете проработать на нем, вставьте результат в другую таблицу Excel, используя тот же API.

Получение списка файлов легко в .NET с вызовом System.IO.Directory.GetFiles(); просто укажите "*.xls". После того, как у вас есть список, просто используйте цикл for, чтобы перебирать его, открывая каждый файл по очереди, затем выполняя запрос в этом файле и так далее.

Если вы используете VBScript, предпочтительный способ получить список файлов Excel - использовать Scripting.FileSystemObject, в частности метод GetFolder. Он работает в основном таким же образом, но синтаксис немного отличается.

Если он VBScript или VB.NET, он, вероятно, будет работать за пределами самого Excel. Вы запустили его двойным щелчком или из командного файла или что-то в этом роде. Преимущество использования VB.NET заключается в том, что вы можете создать графическую форму взаимодействия - она ​​может отображать индикатор выполнения, отслеживать количество файлов, которые вы прошли, обновления статуса и т.д.

Ответ 3

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

Ответ 4

Я согласен с тем, что доступ к объекту Excel не самый быстрый, и если книги и листы, которые вы пытаетесь извлечь данные, согласованы (т.е. имеют одинаковые имена столбцов и т.д.) или, по крайней мере, имена столбцов, которые вы ищете) было бы лучше использовать ODBC. У этого есть некоторые проблемы, и если вы не можете обойти их или на самом деле сделать что-то более сложное на основе содержимого, тогда не может быть никакого способа обойти его. Если это случай, я бы предложил создать один объект Excel, а затем открыть и закрыть файлы по мере необходимости, чтобы попытаться повысить эффективность.

Ответ 5

Это можно сделать с помощью следующего кода

Sub LoopThroughFiles()

Dim StrFile As String
StrFile = Dir("V:\XX\XXX\*.xlsx")
 Do While Len(StrFile) > 0
    Debug.Print StrFile
       Set wbResults = Workbooks.Open("V:\XX\XXX\" & StrFile)   

                    'DO YOUR CODE HERE


       wbResults.Close SaveChanges:=True
    StrFile = Dir
 Loop
End Sub