Code pour passer en boucle sur tous les fichiers excel dans un dossier spécifié, et de l'extraction de données à partir des cellules spécifiques

J'ai environ 50 classeurs Excel que j'ai besoin d'extraire des données à partir. J'ai besoin de prendre des données à partir des cellules spécifiques, spécifiques des feuilles de calcul et de réunir en un seul ensemble de données (de préférence dans un autre classeur excel).

Je suis à la recherche pour certains VBA afin que je puisse compiler les résultats dans le classeur, je l'utilise pour exécuter le code.

L'un des xls ou xlsx fichiers dont j'ai besoin pour extraire les données à partir de, feuille de calcul("DataSource"), j'ai besoin d'évaluer la cellule(D4), et si elle n'est pas null, alors extraire des données à partir de la cellule(F4), et de mettre sur une nouvelle ligne dans l'ensemble des données compilées. Une boucle sur tous les fichiers Excel dans ce dossier comme mentionné ci-dessus.

Et si possible, j'aimerais que le premier champ de données dans la première colonne le nom du fichier de données est tirée dans le dataset.

Quelqu'un peut-il m'aider? Je suis à la recherche d'VBA parce que je suis plus familier avec qui, mais également intéressés en VBScript (que je suis en train d'entrer dans les et apprendre les différences).

On dirait que vous êtes la plupart de la façon dont il la planification. Vous avez des questions particulières sur la syntaxe ou méthodes?
ouais...en fait juste la syntaxe utilisée pour accomplir cette. Comment faire une boucle sur tous les fichiers excel (xls ou xlsx) les fichiers dans un dossier spécifié?
aussi comment aurais-je spécifier nouvelle ligne de données qui est tiré de 'le prochain fichier excel?
Quelle version d'Excel il a besoin de travailler? Par exemple l'Application.FileSearch approche ne fonctionnera pas en 2007/10
Deux mille sept

OriginalL'auteur Justin | 2011-05-01

D'abord commencer par cette requête google et cliquez sur le premier lien qui s'ouvre, qui vous emmène à la un article montrant comment parcourir un groupe de fichiers Excel dans un dossier.

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

Pour obtenir le nom du classeur, vous aurez envie d'adapter le code à la "FAITES de VOTRE CODE ICI" afin d'inclure les wbResults.Name. Si c'est le nom de fichier que vous voulez, utilisez wbResults.FullName, qui renvoie le nom du classeur, y compris son chemin sur le disque comme une chaîne de caractères.

Une recherche pour un VBScript variation sur la même chose donne un certain nombre de résultats qui sont utiles, y compris ce 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

En aparté, pour obtenir la valeur d'une cellule dans VBScript, CellValue = objXL.Cells(X, Y).Value où X et Y sont en ligne et en colonne les valeurs respectivement.
merci beaucoup pour l'aide! très apprécié
Cette réponse n'est plus valide pour les versions d'Excel après 2003; apparemment, l'Application.FileSearch, a été déprécié à partir d'Excel à partir de 2007.
j'ai vu qu'il détecte ~$<filename>.xls ce qui peut être fait pour les filtrer?
merci, le code en bas a fonctionné sans problème

OriginalL'auteur Phil.Wheeler

4

Je le ferais en VBScript ou même, VB.NET ou Powershell si vous vous sentez si incliné.

À l'aide de VB.NET vous pouvez accéder à des feuilles de calcul Excel comme si elles étaient des bases de données, via le fournisseur OLEDB. Le code pour sélectionner une plage de valeurs pourrait ressembler à ceci :
```
 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
```
Une fois que vous obtenez les données que vous pouvez élaborer sur elle, puis insérer le résultat dans une autre feuille de calcul Excel, à l'aide de la même API.

Obtenir la liste des fichiers est simple .NET avec un appel à System.IO.Répertoire.GetFiles(); il suffit de spécifier le "*.xls" générique. Une fois que vous avez la liste, il suffit d'utiliser une boucle for pour parcourir, de l'ouverture de chaque fichier, puis de faire la requête sur que fichier, et ainsi de suite.

Si vous utilisez VBScript, alors le moyen privilégié pour obtenir la liste des fichiers Excel est d'utiliser le Script.FileSystemObject, plus précisément le Méthode GetFolder. Il fonctionne essentiellement de la même façon, mais la syntaxe est légèrement différente.

Si c'est VBScript ou VB.NET il sera probablement courir à l'extérieur d'Excel lui-même. Vous auriez l'exécuter en double-cliquant sur ou à partir d'un fichier de commandes ou quelque chose comme ça. L'avantage de l'utilisation de VB.NET est que vous pourriez mettre en place une forme de graphiques pour l'interaction, il pourrait afficher une barre de progression, combien de fichiers que vous avez traversé, mises à jour de statut, ce genre de chose.

merci beaucoup pour l'aide!

OriginalL'auteur Cheeso
1

Chaque fois que vous accéder à de nombreux fichiers Excel dans la succession, vous pouvez généralement obtenir une meilleure performance à l'aide de ADODB plutôt que d'Excel objet automation.

OriginalL'auteur Nilpo
0

Je suis d'accord avec l'utilisation que l'accès à l'objet Excel n'est pas le moyen le plus rapide et si les classeurs et les feuilles que vous essayez de récupérer des données à partir sont toutes compatibles (j'.e ont les mêmes noms de colonnes, etc... ou au moins les noms de colonne que vous cherchez), il serait préférable d'utiliser ODBC. Ce ne sont quelques questions et si vous ne pouvez pas contourner ou besoin de faire quelque chose de plus complexe, fondée sur le contenu alors il y a peut-être aucun moyen de contourner cela. Si c'est le cas, alors je suggère la création d'un objet Excel, puis l'ouverture et la fermeture des fichiers que nécessaire pour essayer d'augmenter l'efficacité.

OriginalL'auteur Christopher J. Scharer

Il pourrait être fait avec le code suivant

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

OriginalL'auteur Rakesh kumar

Vous devez vous connecter pour publier un commentaire.