Code zum Durchlaufen aller Excel-Dateien in einem angegebenen Ordner und zum Abrufen von Daten aus bestimmten Zellen
Ich habe ungefähr 50 Excel-Arbeitsmappen, aus denen ich Daten abrufen muss. Ich muss Daten aus bestimmten Zellen und bestimmten Arbeitsblättern übernehmen und in ein Dataset (vorzugsweise in eine andere Excel-Arbeitsmappe) kompilieren.
Ich suche nach VBA, damit ich die Ergebnisse in die Arbeitsmappe kompilieren kann, die ich zum Ausführen des Codes verwende.
Also, eine der xls- oder xlsx-Dateien, aus denen ich die Daten ziehen muss, Arbeitsblatt ("DataSource"), ich muss die Zelle (D4) auswerten, und wenn sie nicht null ist, dann ziehen Sie die Daten aus der Zelle (F4) ab und setzen sie in eine neue Zeile in den kompilierten Datensatz. Durchlaufen Sie alle Excel-Dateien in diesem Ordner wie oben erwähnt.
Und wenn möglich, möchte ich, dass das erste Datenfeld in der ersten Spalte den Namen der Datei enthält, aus der die Daten im resultierenden Dataset abgerufen werden.
Kann mir jemand dabei helfen? Ich suche nach VBA, weil ich damit mehr vertraut bin, aber auch an VBScript interessiert bin (da ich versuche, mich damit auseinanderzusetzen und die Unterschiede zu lernen).
Beginnen Sie zuerst mit dieser Google-Abfrage und klicken Sie auf den ersten Link, der auftaucht. Dieser führt Sie zu einem Artikel , der zeigt, wie Sie eine Gruppe von Excel-Dateien in einem Ordner durchlaufen.
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
Um den Namen der Arbeitsmappe zu erhalten, müssen Sie den Code unter "DO YOUR CODE HERE" so anpassen, dass er wbResults.Name enthält. Wenn es sich um den gewünschten Dateinamen handelt, verwenden Sie wbResults.FullName, der den Namen der Arbeitsmappe einschließlich ihres Pfads auf der Festplatte als Zeichenfolge zurückgibt.
Eine Suche nach einer VBScript-Variation für dasselbe liefert eine Reihe nützlicher Ergebnisse, einschließlich dieses Skripts:
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
Ich würde es in VBScript oder sogar VB.NET oder Powershell tun, wenn Sie sich dazu neigen.
Mit VB.NET können Sie über den OLEDB-Provider auf Excel-Arbeitsblätter wie auf Datenbanken zugreifen. Der Code zum Auswählen eines Wertebereichs könnte folgendermaßen aussehen:
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
Sobald Sie die Daten erhalten haben, können Sie sie näher erläutern. Fügen Sie das Ergebnis mit derselben API in eine andere Excel-Tabelle ein.
Das Abrufen der Dateiliste ist in .NET mit einem Aufruf an System.IO.Directory.GetFiles() ; Geben Sie einfach den Platzhalter "* .xls" an. Sobald Sie die Liste haben, verwenden Sie einfach eine for-Schleife, um die Liste zu durchlaufen, öffnen Sie jede Datei der Reihe nach, und führen Sie die Abfrage nach der Datei that usw. aus.
Wenn Sie VBScript verwenden, wird die Liste der Excel-Dateien vorzugsweise mit dem Scripting.FileSystemObject , insbesondere der GetFolder-Methode , aufgerufen. Es funktioniert grundsätzlich genauso, aber die Syntax unterscheidet sich geringfügig.
Wenn es sich um VBScript oder VB.NET handelt, wird es wahrscheinlich außerhalb von Excel selbst ausgeführt. Sie würden es per Doppelklick oder aus einer Batchdatei oder ähnlichem ausführen. Der Vorteil der Verwendung von VB.NET besteht darin, dass Sie ein grafisches Formular für die Interaktion erstellen können. Es könnte eine Statusleiste angezeigt werden, die die Anzahl der durchgeführten Dateien aufzeichnet, Statusaktualisierungen und dergleichen.
Wenn Sie nacheinander auf so viele Excel-Dateien zugreifen, können Sie im Allgemeinen mit ADODB eine bessere Leistung als mit dem Automatisierungsobjekt von Excel erzielen.
Dies könnte mit dem folgenden Code erfolgen
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
Ich bin damit einverstanden, dass der Zugriff auf das Excel-Objekt nicht der schnellste ist, und wenn die Arbeitsmappen und Arbeitsblätter, von denen Sie versuchen, Daten abzurufen, alle konsistent sind (dh dieselben Spaltennamen haben usw.) oder zumindest die Spaltennamen, die Sie verwenden suchen) es wäre besser, ODBC zu verwenden. Dies hat einige Probleme. Wenn Sie sie nicht umgehen können oder aufgrund des Inhalts tatsächlich etwas komplexeres tun müssen, führt dies möglicherweise zu keiner Umgehung. Wenn dies der Fall ist, würde ich vorschlagen, ein Excel-Objekt zu erstellen und die Dateien dann nach Bedarf zu öffnen und zu schließen, um die Effizienz zu erhöhen.