Fusion des données à partir de plusieurs classeurs dans un classeur de synthèse dans Excel 2010 (traduction automatique)
Important
Cet article est machine traduit, reportez-vous à l' exclusion de responsabilité. Veuillez trouver la version anglaise de cet article ici pour référence.
.jpg "Procédure Office Visual")
Résumé : Apprenez à utiliser VBA pour prendre des données provenant de plusieurs classeurs et les fusionner dans un classeur de synthèse dans Microsoft Excel 2010 ou Microsoft Excel 2007.
Dernière modification : mercredi 12 septembre 2012
S’applique à : Excel 2007 | Excel 2010 | Office 2007 | Office 2010 | VBA
Applies to:Microsoft Excel 2010 | Microsoft Excel 2007
Publication : Janvier 2011
Fournie par : Peter Gruenbaum, SDK Bridge, LLC (éventuellement en anglais)
.jpg "Vue d’ensemble")
Certains utilisateurs stockent des données dans plusieurs feuilles de calcul. Il est souvent utile de fusionner des éléments de données spécifiques dans un classeur. Toutefois, la fusion de données manuellement peut prendre du temps. Vous pouvez utiliser le code VBA pour automatiser cette tâche. Ce Visual How To explique comment faire pour :
Ce Visual How To est basé sur La fusion des données provenant de plusieurs classeurs dans un classeur de synthèse dans Excel (éventuellement en anglais) par Ron de Bruin et Frank Rice. Les exemples de code inclus dans cette Visual procédure sont des versions simplifiées des exemples de code dans cet article. .jpg "Code")
Envisagez le scénario d'une société de service de petite taille qui crée un classeur Excel pour chaque facture qu'ils créent. La société dispose d'un dossier qui contient toutes les factures, et qu'ils veulent synthétiser les données de la facture dans un classeur. Les factures ont une zone de synthèse qui contient des informations dans les cellules A9 à C9. Cette section décrit comment utiliser VBA pour prendre les valeurs des cellules de résumé de chacune des factures et de placer les valeurs dans un nouveau classeur. L'activation de l'onglet Développeur Vous allez utiliser l'onglet Développeur pour accéder à l'éditeur Visual Basic et autres outils de développement. Cependant, Office 2010 n'affiche pas l'onglet Développeur par défaut. Utilisez la procédure suivante pour activer l'onglet Développeur. Pour activer l’onglet Développeur
.gif "Remarque") Remarque
Dans le Microsoft Office System 2007, utilisez le bouton Office pour ouvrir la boîte de dialogue Options. Sous la catégorie populaires, cliquez sur Afficher l’onglet Développeur dans le Ruban.
Après avoir activé l'onglet Développeur , cliquez sur l'onglet Développeur , le bouton de Macro, tapez MergeAllWorkBooks comme nom de macro et puis cliquez sur Créer. Remarque
Dans toutes les versions récentes de Microsoft Excel, vous pouvez utiliser les touches de raccourci Alt + F8 pour ouvrir la Boîte de dialogue Macro .
Fusion de tous les classeurs dans un dossier L'exemple de code suivant crée un nouveau classeur. Ensuite, le code prend chaque classeur Excel dans le dossier C:\Users\Peter\invoices et copie les informations à partir de cellules A9 via C9 dans le nouveau classeur. En outre, le code place le nom de fichier du classeur dans la ligne a. Pour parcourir en boucle tous les fichiers dans le dossier, utilisez la fonction Dir. La première fois que vous appelez Dir, entrez le chemin d'accès des fichiers que vous souhaitez comme argument et récupérez le premier fichier. Ensuite, appelez Dir sans arguments, et vous récupérez le fichier suivant. Lorsqu'il n'y a plus de fichiers, le processus retourne une chaîne vide. ``` VBA Sub MergeAllWorkbooks() Dim SummarySheet As Worksheet Dim FolderPath As String Dim NRow As Long Dim FileName As String Dim WorkBk As Workbook Dim SourceRange As Range Dim DestRange As Range ' Create a new workbook and set a variable to the first sheet. Set SummarySheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1) ' Modify this folder path to point to the files you want to use. FolderPath = "C:\Users\Peter\invoices\" ' NRow keeps track of where to insert new rows in the destination workbook. NRow = 1 ' Call Dir the first time, pointing it to all Excel files in the folder path. FileName = Dir(FolderPath & "*.xl*") ' Loop until Dir returns an empty string. Do While FileName <> "" ' Open a workbook in the folder Set WorkBk = Workbooks.Open(FolderPath & FileName) ' Set the cell in column A to be the file name. SummarySheet.Range("A" & NRow).Value = FileName ' Set the source range to be A9 through C9. ' Modify this range for your workbooks. ' It can span multiple rows. Set SourceRange = WorkBk.Worksheets(1).Range("A9:C9") ' Set the destination range to start at column B and ' be the same size as the source range. Set DestRange = SummarySheet.Range("B" & NRow) Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _ SourceRange.Columns.Count) ' Copy over the values from the source to the destination. DestRange.Value = SourceRange.Value ' Increase NRow so that we know where to copy data next. NRow = NRow + DestRange.Rows.Count ' Close the source workbook without saving changes. WorkBk.Close savechanges:=False ' Use Dir to get the next file name. FileName = Dir() Loop ' Call AutoFit on the destination sheet so that all ' data is readable. SummarySheet.Columns.AutoFit End Sub ```Le nouveau classeur que vous créez avec cet exemple de code contient une ligne pour chaque fichier dans le répertoire. Colonne a contient le nom de fichier et les colonnes b à d contiendra les informations contenues dans les cellules A9 à C9 à partir de la première feuille de chaque classeur. Pour configurer cette macro, remplacez la valeur que FolderPath est définie sur et modifier la plage où SourceRange est définie. Fusion sélectionnée classeurs dans un dossier Maintenant, vous pouvez modifier le code afin qu'un utilisateur peut sélectionner certains fichiers dans ce dossier. Pour ce faire, vous devez présenter une boîte de dialogue Ouvrir un fichier et afficher les fichiers dans votre dossier. Définir le répertoire en cours à votre chemin d'accès du dossier. Définir le répertoire avec les fonctions ChDrive et ChDir. Remarque
À l'aide de ChDrive et ChDir ne fonctionnera pas pour les lecteurs réseau. Pour définir le répertoire en cours pour les lecteurs réseau, appelez le SetCurrentDirectoryA de fonction Windows qui est illustrée dans La fusion des données provenant de plusieurs classeurs dans un classeur de synthèse dans Excel (éventuellement en anglais).
Le code utilise Application.GetOpenFilename pour ouvrir la boîte de dialogue fichier qui possède les filtres configurés pour afficher uniquement les classeurs Excel. GetOpenFileNames retourne un tableau d'objets Variant, qui sont des objets qui peuvent être n'importe quel type. Dans ce cas, GetOpenFilenames retourne un tableau de chaînes, un pour chaque nom de fichier sélectionné. ``` VBA Sub MergeSelectedWorkbooks() Dim SummarySheet As Worksheet Dim FolderPath As String Dim SelectedFiles() As Variant Dim NRow As Long Dim FileName As String Dim NFile As Long Dim WorkBk As Workbook Dim SourceRange As Range Dim DestRange As Range ' Create a new workbook and set a variable to the first sheet. Set SummarySheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1) ' Modify this folder path to point to the files you want to use. FolderPath = "C:\Users\Peter\invoices\" ' Set the current directory to the the folder path. ChDrive FolderPath ChDir FolderPath ' Open the file dialog box and filter on Excel files, allowing multiple files ' to be selected. SelectedFiles = Application.GetOpenFilename( _ filefilter:="Excel Files (*.xl*), *.xl*", MultiSelect:=True) ' NRow keeps track of where to insert new rows in the destination workbook. NRow = 1 ' Loop through the list of returned file names For NFile = LBound(SelectedFiles) To UBound(SelectedFiles) ' Set FileName to be the current workbook file name to open. FileName = SelectedFiles(NFile) ' Open the current workbook. Set WorkBk = Workbooks.Open(FileName) ' Set the cell in column A to be the file name. SummarySheet.Range("A" & NRow).Value = FileName ' Set the source range to be A9 through C9. ' Modify this range for your workbooks. It can span multiple rows. Set SourceRange = WorkBk.Worksheets(1).Range("A9:C9") ' Set the destination range to start at column B and be the same size as the source range. Set DestRange = SummarySheet.Range("B" & NRow) Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _ SourceRange.Columns.Count) ' Copy over the values from the source to the destination. DestRange.Value = SourceRange.Value ' Increase NRow so that we know where to copy data next. NRow = NRow + DestRange.Rows.Count ' Close the source workbook without saving changes. WorkBk.Close savechanges:=False Next NFile ' Call AutoFit on the destination sheet so that all data is readable. SummarySheet.Columns.AutoFit End Sub ```Exécution de la macro s'ouvre une boîte de dialogue fichier. Utilisez les touches de raccourci Ctrl + A pour sélectionner tous les fichiers ou sur la touche Ctrl enfoncée pour sélectionner plusieurs fichiers. Vous pouvez également cliquer avec la touche MAJ enfoncée pour sélectionner une plage de fichiers. Auparavant, vous pouvez modifier cette macro pour vos propres besoins. Pour ce faire, modifiez la valeur que FolderPath est définie sur et modifier la plage où SourceRange est définie. Copie d'une plage qui s'étend vers le bas jusqu'à la dernière ligne Dans les exemples précédents, vous connaissez exactement les lignes à copier. Toutefois, vous souhaiterez copier des cellules d'une ligne de départ jusqu'à la dernière ligne où vous ne savez pas quelle ligne le dernier. L'exemple suivant montre comment modifier le code afin que la macro copie des colonnes a à K, à partir de la ligne 8 vers la dernière ligne. Pour copier toutes les lignes, vous devez d'abord trouver la dernière ligne. Vous pouvez trouver la dernière ligne avec la méthode Find, qui renvoie une plage de cellules qui correspondent à un critère spécifié. Dans ce cas, le code commence au bas de la feuille de calcul et vers le haut, ligne par ligne, le code de recherche, dans une cellule qui n'est pas vide. Remplacez la ligne qui définit la variable SourceRange à l'exemple de code suivant. ``` VBA Dim LastRow As Long LastRow = WorkBk.Worksheets(1).Cells.Find(What:="*", _ After:=WorkBk.Worksheets(1).Cells.Range("A1"), _ SearchDirection:=xlPrevious, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows).Row Set SourceRange = WorkBk.Worksheets(1).Range("A8:K" & LastRow) ```Pour rechercher une cellule qui n'est pas vide, définissez le What à la "*" génériques et l'argument LookIn à xlFormulas. Pour démarrer à partir de bas en haut, définissez l'argument après la première cellule et si l'argument SearchDirection à xlPrevious, ce qui entraîne la recherche habiller vers le bas. Pour effectuer une recherche par lignes, définissez l'argument SearchOrder à xlByRows. Enfin, utilisez la propriété Row pour trouver le numéro de ligne de la dernière ligne. Ensuite, utilisez le numéro de ligne pour définir une plage qui commence à A8 et accède à la colonne k et le dernier numéro de ligne. Dans ce cas, vous savez que la dernière colonne k. Si vous ne connaissez pas la dernière colonne, utilisez la fonction RDB_Last dans La fusion des données provenant de plusieurs classeurs dans un classeur de synthèse dans Excel (éventuellement en anglais), qui peuvent fournir la dernière colonne, la dernière ligne ou la dernière cellule. .jpg "Lecture")
Les exemples de code dans cette Visual procédure sont simplifiées. En revanche, l'article écrit par de Bruin contient des exemples de code VBA plus complexes. Exemples de son code vérifier lorsque les fichiers sont introuvables, lorsque le classeur cible a lignes insuffisants et autres erreurs. En particulier, de Bruin inclut du code pour gérer les erreurs afin que si vous traitez un grand nombre de fichiers et un tombe en panne, le code n'échouera pas. Considérez l'exemple de code suivant ouvre un classeur. ``` VBA On Error Resume Next Set mybook = Workbooks.Open(MyPath & MyFiles(FNum)) On Error GoTo 0 ```On Error Resume Next signifie que si une erreur se produit lorsque vous ouvrez le classeur, le code passe à la ligne suivante. On Error GoTo 0 renvoie à la condition par défaut de gestion des erreurs. Exemples de code de Bruin de fournissent également une meilleure expérience utilisateur en désactivant la mise à jour de l'écran et la désactivation des événements. CalcMode dans l'application est défini sur calcul manuel. Toutefois, le code capture l'état d'origine de la CalcMode et restaure la valeur à la fin de la sous-routine. Filtrage des données Un autre exemple utile dans l'article de Bruin est un exemple qui utilise un filtre. Le filtre vous permet de limiter les cellules sont copiés dans le classeur de synthèse. Une fois que l'application trouve une plage source, le code désactive le filtre automatique de la plage de son parent. Ensuite, le code définit le filtre automatique de plage dans une colonne spécifique pour les cellules qui correspondent à des critères spécifiques. (Dans son cas, les cellules ont la valeur « ron ».) Si la cellule de la colonne correspond aux critères, le code copie la ligne dans le classeur de destination. Fusionner les compléments et les autres Macros fusion Vous peut-être besoin de la capacité de fusion, mais pas ont besoin ou à écrire du code VBA. Dans ce cas, de Bruin a créé un utilitaire permettant de fusionner dans Excel qui est sous la forme d'un complément appelé RDB_Merge (éventuellement en anglais). Sa page web contient des instructions pour le téléchargement, l'installation et à l'aide de la macro complémentaire. Site Web de Bruin de possède également plusieurs Macros vba (éventuellement en anglais) qui couvrent diverses situations de fusion. Ces macros sont les suivants :
|
.jpg "Le voir")
Regardez la vidéo (éventuellement en anglais) | Longueur : 00 : 07 : 52 .jpg "L’explorer")
|
.jpg "Regarder une vidéo")
Notes
Avis de non-responsabilité de traduction automatique: cet article a été traduit par un ordinateur sans intervention humaine. Microsoft propose ces traductions machine pour aider les utilisateurs anglophones non anglophones à profiter du contenu sur les produits, services et technologies Microsoft. Dans la mesure où l'article a été traduite de machine, il peut contenir des erreurs de vocabulaire, de syntaxe ou de grammaire.