[XL - 2016] copy/paste depuis plusieurs fichiers vers un fichier dest

Le 07/05/2018 à 21:44

Bonjour a tous,

je travail sur un projet d'automatisation et j'aurais besoin d'aide sur la creation d'une macro VBA. Je souhaite tout d'abord m'excuser pour mon francais et ma ponctuation. Travaillant a l'etranger j'ai un clavier Qwerty (donc pas d'accent ou de correction orthographique francaise).

Voici mon workspace:

=> template.xlsx

=> fichier1.csv

=> fichier2.csv

=> fichier3.csv

=> fichier4.csv

je souhaite copier certaines colonnes des fichiers1,2,3 et 4 et les coller dans le fichier template. les 4 fichiers csv sont de la meme forme (meme colonne avec des valeurs differentes). il faut les coller a la suite les unes des autres dans le fichier template. sachant que je ne sais pas le nombre de lignes dans chaque fichier csv (parfois 100 lignes parfois 500). dans mon code je souhaite egalement utiliser des chemins relatifs. les fichiers1,2,3 et 4 seront toujours dans le meme dossier que le fichier template.

Le fichier template contient un certain nombres de feuille. je souhaite copier les resultats dans une feuille precise.

le VBA etant inconnu pour moi j'ai un peu de mal.

je cherche a faire un truc de la sorte:

Sub Copy_Template

Dim template As Workbook
Dim Fichier1 As String
Dim Fichier2 As String
Dim Fichier3 As String
Dim Fichier4 As String

Fichier1 = "chemin relatif du fichier1"
Fichier2 = "chemin relatif du fichier2"
Fichier3 = "chemin relatif du fichier3"
Fichier4 = "chemin relatif du fichier4"

Set template = ThisWorkbook
Set Wb1 = Workbooks.Open(Fichier1)
Set Wb2 = Workbooks.Open(Fichier2)
Set Wb3 = Workbooks.Open(Fichier3)
Set Wb4 = Workbooks.Open(Fichier4)

/*copier toutes les valeurs de la colonneA du fichier1(ignorer les cellules vides)*/
Range("A3:A" & [A5000].End(xlUp).Row).SpecialCells(xlCellTypeConstants, 23).Select
Selection.Copy

template.Sheets("sheetIWant").Activate

Range("A10000").End(xlUp).Offset(0).Select 'recherche la première cellule vide
ActiveSheet.Paste 'copie les données

Sachant que dans le fichier template ou je colle tout les resultats dans un tableau, ce tableau est un tableau pivot. qui est donc relier a dautres feuilles pour la creation automatique de charts and stats.

Ont peut trouver en piece jointe un exemple de fichier CSV et du fichier template.

comme ont peut le voir les colonnes ne sont pas les memes entre les fichiers CSV et le fichier template. De plus je ne copie que certaines colonnes pas toutes.

voici le lien entre les colonnes:

Fichier1: Col A - Col A :Template (ex: je souhaite copier le colonne A du fichier1 vers la colonneA du fichier template)

Fichier1: Col B - Col B :Template

Fichier1: Col C - Col F :Template

Fichier1: Col E - Col G :Template

Fichier1: Col G - Col J :Template

Fichier1: Col K - Col H :Template

Fichier1: Col M - Col K :Template

Je souhaite donc avoir une macro dans le fichier template qui va ouvrir les autres fichiers (1,2,3 et 4) avec un chemin relatif, faire les copy/paste, fermer les fichiers.

Merci d'avance pour votre aide!

Bien Cordialement,

14file1.zip (2.12 Ko)
15template.xlsx (172.33 Ko)
Le 09/05/2018 à 11:18

Bonjour,

ci-joint exemple de code :

Sub copie()                                            
    Dim copie(): copie = Array(Array("A:B", "A"), Array("C", "F"), Array("E", "G"), Array("G", "J"), Array("K", "H"), Array("M", "K"))
    Dim fso As Object: Set fso = CreateObject("Scripting.FileSystemObject")
    Dim file As Object
    Dim KW As ListObject: Set KW = Sheet3.ListObjects(1)    'KW table on Sheet 3                                            
    Dim i As Integer, j As Integer
    Dim wb As Workbook

    For Each file In fso.GetFolder(ThisWorkbook.Path).Files                                            
        If fso.GetExtensionName(file.Path) = "csv" Then                                            

            '// determine first empy row of KW table from the first empty cell in column(ID)                                            
            If KW.ListRows.Count = 0 Then KW.ListRows.Add 'adding a new row in KW table                                            
            If KW.ListColumns("ID").DataBodyRange.Rows(KW.ListRows.Count) <> Empty Then KW.ListRows.Add 'adding a new row in KW table                                            
            i = KW.ListRows.Count                                            

            '// copy columns of file(.csv) in KW table from the first empty cell of the corresponding column                                            
            Set wb = Workbooks.Open(file, , , 2)
            For j = 0 To UBound(copie) - 1                                            
                With wb.Sheets(1).UsedRange
                    .Columns(copie(j)(0)).Offset(2).Resize(.Rows.Count - 2).Copy KW.DataBodyRange.Cells(i, copie(j)(1))
                End With
            Next j                                            

            '// close file(.csv)                                            
            wb.Close                                            
        End If                                            
    Next file                                            

End Sub
6template1.xlsm (188.28 Ko)
Le 09/05/2018 à 14:58

Bonjour Thev,

Merci de ta reponse et de ton aide! c'est tres sympa de ta part.

Pourrais tu m'expliquer deux, trois trucs?

que fait cette ligne?

Dim fso As Object: Set fso = CreateObject("Scripting.FileSystemObject")

ici tu ouvre tout les fichiers CSV contenues dans le repertoire et tu copie les elements souhaitez. que fait UBound? UsedRange?

            
            Set wb = Workbooks.Open(file, , , 2)
            For j = 0 To UBound(copie) - 1                                            
                With wb.Sheets(1).UsedRange
                    .Columns(copie(j)(0)).Offset(2).Resize(.Rows.Count - 2).Copy KW.DataBodyRange.Cells(i, copie(j)(1))
                End With

pourrais tu m'expliquer cette ligne qui doit servir a copier les elements choisis?

.Columns(copie(j)(0)).Offset(2).Resize(.Rows.Count - 2).Copy KW.DataBodyRange.Cells(i, copie(j)(1))

Est il possible de separer la macro du document excel? au lieu d'avoir une macro dans un document excel j'aimerais bien avoir un script dans un fichier qui lorsque je l'execute effectue la meme chose?

Merci beaucoup de ton aide!

Bien Cordialement,

Le 09/05/2018 à 16:11
gms821 a écrit :

que fait cette ligne?

Code : Tout sélectionnerDim fso As Object: Set fso = CreateObject("Scripting.FileSystemObject")

Cette ligne définit l'objet "FileSystemObject" qui avec ses méthodes et propriétés se comporte en gros comme un explorateur de fichier et présente donc des fonctionnalités bien supérieures à l'instruction "Dir".

NB: Cet objet est défini dans le cadre d'une "liaison tardive". Il peut être défini en "liaison anticipée" si la bibliothèque "Microsoft Scripting Runtime" est ajoutée à l'éditeur VBA.

gms821 a écrit :

ici tu ouvre tout les fichiers CSV contenues dans le repertoire et tu copie les elements souhaitez. que fait UBound? UsedRange?

UBound donne le dernier élément du tableau "copie", c'est à dire 5, sachant que le premier élément est 0.

UsedRange représente la plage utilisée au niveau des lignes et colonnes; ici celle de la première feuille du fichier(.csv) ouvert.

gms821 a écrit :

pourrais tu m'expliquer cette ligne qui doit servir a copier les elements choisis?

Code : .Columns(copie(j)(0)).Offset(2).Resize(.Rows.Count - 2).Copy KW.DataBodyRange.Cells(i, copie(j)(1))

copie(j) représente le j ème élément du tableau "copie". Ce j ème élément étant lui_même un tableau à 2 dimensions, 0 représente la 1ère et 1 la seconde.

.Offset(2) décale de 2 lignes vers le bas, la plage utilisée de des colonnes définies par le tableau "copie"

.Resize(.Rows.Count - 2) compense le décalage des 2 lignes en réduisant de 2 lignes la taille de la plage décalée.

gms821 a écrit :

Est il possible de separer la macro du document excel? au lieu d'avoir une macro dans un document excel j'aimerais bien avoir un script dans un fichier qui lorsque je l'execute effectue la meme chose?

C'est possible mais il faudrait être plus précis sur les autres cas à traiter : autres classeurs Template ?, autres fichiers CSV ? pour déterminer la solution la plus appropriée.

Le 09/05/2018 à 16:27

Un grand merci pour ces precisions!

Concernant le script, il semble que le dernier array (M=>K) ne fonctionne pas. sinon le reste fonctionne parfaitement!

Sais tu comment faire pour avoir la macro dans un fichire script que je peux executer a la place de l'avoir attache au fichier excel?

Le 09/05/2018 à 19:40
gms821 a écrit :

Concernant le script, il semble que le dernier array (M=>K) ne fonctionne pas. sinon le reste fonctionne parfaitement!

Un petit bug à corriger, d'ailleurs, j'ai rectifié la définition de Ubound :

For j = 0 To UBound(copie)

au lieu de

For j = 0 To UBound(copie) -1
gms821 a écrit :

Sais tu comment faire pour avoir la macro dans un fichier script que je peux executer a la place de l'avoir attache au fichier excel?

1- il faut créer un fichier spécifique à la macro, par exemple : copie_csv.xlsm; la macro doit être modifiée pour recevoir en paramètre le classeur Template ; il est supposé que le classeur Template et les fichiers CSV sont dans le même répertoire. (ci-joint fichier correspondant).

2- un script ayant pour paramètre 1 : le classeur contenant la macro et pour paramètre 2 : le classeur template, par exemple : copie_csv.vbs (ci-joint lien).

https://www.cjoint.com/c/HEjrKgYcdUG

3- un raccourci permettant d'exécuter le script avec ses 2 paramètres.

exemple: C:\Windows\System32\wscript.exe <répertoire>\copie_csv.vbs "copie_csv.xlsm" "template1.xlsm"

(en supposant que ces 3 fichiers se trouvent dans le même répertoire)

ci_joint fichiers

6template1.xlsm (177.81 Ko)
8copie-csv.xlsm (22.00 Ko)
Le 09/05/2018 à 19:48

Merci beaucoup, Je vais essayer cela.

In addition, Quelle ligne dois-je ajouter au debut du script si je souhaite effacer les precedentes donnees du tableau a chaque nouvelle appel de la macro?

Best,

Le 09/05/2018 à 20:04
gms821 a écrit :

In addition, Quelle ligne dois-je ajouter au debut du script si je souhaite effacer les precedentes donnees du tableau a chaque nouvelle appel de la macro?

Le mieux est d'insérer l'instruction d'effacement des données du tableau au début de la macro.

ci-jointe nouvelle version

7copie-csv.xlsm (21.98 Ko)
Le 09/05/2018 à 21:00

Hello Thev,

Si je comprend bien la macro de copie est contenu dans le fichier copie_csv.xlsm et le script copie_csv.vbs dit juste de chercher ce fichier excel et d'executer sa macro?

De la meme maniere, si je comprend bien, on utilise un fichier tiers (copie_csv.xlsm) pour n'avoir aucune macro dans notre fichier template et ainsi avoir un simple fichier xls?

Le 09/05/2018 à 21:49
gms821 a écrit :

Si je comprend bien la macro de copie est contenu dans le fichier copie_csv.xlsm

Exact
gms821 a écrit :

et le script copie_csv.vbs dit juste de chercher ce fichier excel et d'executer sa macro?

Oui, mais le script dit également d'ouvrir le fichier Template et d'assigner ce fichier en paramètre de la macro. C'est pourquoi le script a besoin de 2 paramètres : 1) le fichier macro, 2) le fichier Template.
gms821 a écrit :

De la meme maniere, si je comprend bien, on utilise un fichier tiers (copie_csv.xlsm) pour n'avoir aucune macro dans notre fichier template et ainsi avoir un simple fichier xls?

Tout à fait.
Le 09/05/2018 à 21:55

je comprend mieux merci beaucoup,

J'ai egalement ajoute une commande pour rafraichir les autres pivot table du fichier template. le script VBS fonctionne parfaitement! Je l'execute depuis un script shell.

Savez-vous si il y a un moyen d'executer la commande avec un chemin relatif et non absolu?

j'execute la commande de cette facon la:

/c/Windows/System32/wscript.exe macro.vbs "/c/.../macro.xlsm" "/c/.../template.xlsx"

mais je souhaiterais de cette facon:

/c/Windows/System32/wscript.exe macro.vbs "macro.xlsm" "template.xlsx"

Sachant que tout les fichier se situent dans le meme dossier.

Dans tout les cas je tiens a vous remercier car vous avez ete plus que reactif et helpful! Vous m'avez bien aide, c'est really sympa de votre part.

Le 09/05/2018 à 22:20
gms821 a écrit :

mais je souhaiterais de cette facon:

/c/Windows/System32/wscript.exe macro.vbs "macro.xlsm" "template.xlsx"

Avec cette instruction, le répertoire par défaut est " /c/Windows/System32" . Je ne vois pas de solution simple autre que celle de mettre le fichier script dans le même répertoire que macro.xlsm et Template.xlsx.

Auquel cas l'instruction serait :

/c/Windows/System32/wscript.exe /c/.../macro.vbs "macro.xlsm" "template.xlsx"

Le 10/05/2018 à 15:14

Je comprends,

Dans tout les cas, merci beaucoup de ton aide !!

Rechercher des sujets similaires à "2016 copy paste fichiers fichier dest"