VBA - Copier valeurs depuis fichiers à pistes variables Fermé

Question

Bonjour,

Je souhaite créer un fichier de synthèse, qui contient la macro et le bouton qui lance la macro, qui doit être utilisé et complété par plusieurs utilisateurs, et qui doit récupérer des valeurs depuis plusieurs autres fichiers sources, dont le chemin d'accès doit être rentré semi manuellement dans plusieurs cellules du fichier. Je suis débutant sur VBA et j'ai essayé de chercher sur internet mais je n'ai pas trouvé de réponse pour m'aider.

Dans mon fichier de synthèse, les colonne S et U doivent donner les valeurs recherchées.

Les colonnes W à AA (et AD) doivent permettre d'écrire le chemin d'accès au fichier source, toujours au même format. Les colonnes AB et AE donnent le nom de l'onglet source, les colonnes AC et AF doivent donner la cellule source.

J'ai donc testé dans un premier temps la macro suivante, et qui fonctionne bien quand elle tourne seule, mais qui est très manuelle finalement.

Sub Valolignetest()
'Récupérer Valeur de Gestion

'Définir Dimensions et chemins
Dim WBA As Workbook, WBB As Workbook
Dim SHA As Worksheet, SHB As Worksheet
Set WBA = ActiveWorkbook
Set SHA = ActiveSheet

'Définir pistes pour ligne 11
Chemin11 = Range("W11").Value & Range("X11").Value & Range("Y11").Value & Range("Z11") & Range("AA11") & Range("AD11")
Onglet11 = Range("AB11").Value
Cellule11 = Range("AC11").Value
Ongletb11 = Range("AE11").Value
Celluleb11 = Range("AF11").Value

'Définir pistes pour ligne 12
Chemin12 = Range("W12").Value & Range("X12").Value & Range("Y12").Value & Range("Z12") & Range("AA12") & Range("AD12")
Onglet12 = Range("AB12").Value
Cellule12 = Range("AC12").Value
Ongletb12 = Range("AE12").Value
Celluleb12 = Range("AF12").Value

'Définir pistes pour ligne 13
Chemin13 = Range("W13").Value & Range("X13").Value & Range("Y13").Value & Range("Z13") & Range("AA13") & Range("AD13")
Onglet13 = Range("AB13").Value
Cellule13 = Range("AC13").Value
Ongletb13 = Range("AE13").Value
Celluleb13 = Range("AF13").Value

'Définir pistes pour ligne 14
Chemin14 = Range("W14").Value & Range("X14").Value & Range("Y14").Value & Range("Z14") & Range("AA14") & Range("AD14")
Onglet14 = Range("AB14").Value
Cellule14 = Range("AC14").Value
Ongletb14 = Range("AE14").Value
Celluleb14 = Range("AF14").Value

'Copier dans la cellule souhaitée
With SHA
            .Range("S11") = Workbooks.Open(Filename:=Chemin11).Sheets(Onglet11).Range(Cellule11)
            .Range("U11") = Workbooks.Open(Filename:=Chemin11).Sheets(Ongletb11).Range(Celluleb11)
            .Range("S12") = Workbooks.Open(Filename:=Chemin12).Sheets(Onglet12).Range(Cellule12)
            .Range("U12") = Workbooks.Open(Filename:=Chemin12).Sheets(Ongletb12).Range(Celluleb12)
            .Range("S13") = Workbooks.Open(Filename:=Chemin13).Sheets(Onglet13).Range(Cellule13)
            .Range("U13") = Workbooks.Open(Filename:=Chemin13).Sheets(Ongletb13).Range(Celluleb13)
            .Range("S14") = Workbooks.Open(Filename:=Chemin14).Sheets(Onglet14).Range(Cellule14)
            .Range("U14") = Workbooks.Open(Filename:=Chemin14).Sheets(Ongletb14).Range(Celluleb14)
End With
            
End Sub

Mon objectif est maintenant de reproduire cette manip sur plusieurs lignes, par exemple des lignes 11 à 100. Est-ce qu'il y a un moyen "facile" de procéder plutôt que de copier la formule x fois pour chaque ligne (potentiellement des centaines de lignes) ?

Au delà de ces éléments, est-ce qu'il est possible d'aller récupérer les valeurs sources sans ouvrir chacun des fichiers source justement à chaque ligne ? L'idée étant que ca tourne plus vite et que ca ne m'ouvre pas tous les fichiers un par un...

Merci par avance pour votre aide.

Windows / Chrome 110.0.0.0

f894009 · Answer

Bonjour,

récupérer les valeurs sources sans ouvrir chacun des fichiers source justement à chaque ligne

Puisque vous ne recuperez que deux valeurs par fichier:

'Lecture de la cellule A1 dans la Feuil1 du classeur fermé				
MsgBox ExecuteExcel4Macro("'C:\Documents and Settings\mimi\dossier\excel\[ClasseurBase.xls]Feuil1'!R1C1")

qu'il y a un moyen "facile" de procéder plutôt que de copier la formule x fois

Faites une boucle pour ligne 11 a ligne x

Lecture des deux cellules fichier 1

Ecriture des deux premieres cellules

et ainsi de suite jusqu'a le derniere ligne

ClassicDudu · Answer

Merci pour votre retour rapide.

Je récupère actuellement 2 valeurs seulement, mais l'objectif à terme est bien de récupérer le plus d'informations clés possibles. Je précise aussi que chaque ligne de mon fichier de synthèse présente une piste de fichier source différente (fichier peut être différent, onglet, ou cellule), sans logique particulière d'une ligne à l'autre, d'où la nécessité d'écrire en semi manuel la piste pour chaque ligne dans les colonnes W à AA.

J'ai tout de même souhaité comprendre le code en MsgBox que vous présentez, et je l'ai reproduit (en adaptant à ma piste) dans une macro seule pour la comprendre, mais cela ne m'ouvre qu'une fenêtre pour ouvrir un fichier... Je ne comprends pas bien comment cela m'aide à récupérer les données sans ouvrir le fichier justement ?

Pour la boucle à créer, j'essaye de voir sur internet comment cela fonctionne, sur la base des codes

For ligne = 11 To 15
Next ligne

Mais comment transformer les taches qui sont aujourd'hui écrites dans mon code en format fixe "W11" en format variable qui pourra se répéter d'une ligne à l'autre ? VBA ne va pas transposer automatiquement dans mon code W11 en W12, W13 etc pour chaque ligne, si ?

Merci encore,

f894009 · Answer

Bonjour,

W11 en W12, W13 etc pour chaque ligne, si ?

Ben si, il faut une ou plusieurs variables qui s'increment a chaque tour

mais l'objectif à terme est bien de récupérer le plus d'informations clés possibles

Vous auriez du le preciser dans votre demande initiale car le code n'est pas du tout le meme pour chercher des infos dans un fichier ferme.

ClassicDudu · Answer

Hello,

Il me semble avoir bien indiqué que je souhaitais récupérer des valeurs dans plusieurs autres fichiers, avec des sources variables. J'ai ensuite donné l'état actuel de mon fichier avec deux valeurs à récupérer.

J'ai tout de même essayé de créer une boucle en commençant sur 5 lignes, cf. code ci-dessous, avec des msgbox pour tester à chaque ligne les infos récupérées (je les supprimerai quand la macro fonctionnera).

Sub Testerboucle()

'Définir Dimensions et chemins
Dim WBA As Workbook, 
Dim SHA As Worksheet,
Set WBA = ActiveWorkbook
Set SHA = ActiveSheet
Dim CA As Range

'Boucle sur chaque cellule de l'onglet
For Each CA In SHA.Range("S11:S15")

k = SHA.Range("AB" & CA.Row).Value
c = SHA.Range("AC" & CA.Row).Value
piste = SHA.Range("W" & CA.Row).Value & Range("X" & CA.Row).Value & Range("Y" & CA.Row).Value & Range("Z" & CA.Row) & Range("AA" & CA.Row) & Range("AD" & CA.Row)

MsgBox k
MsgBox c
MsgBox piste

With SHA
        .Range("S" & CA.Row) = Workbooks.Open(Filename:=piste).Sheets(k).Range(c)
End With

Next CA

End Sub

Avec ce code, qui doit me servir de test pour alimenter la colonne S  (et une fois que cela fonctionnera, je pourrai dupliquer sur d'autre éléments), la macro bug pour alimenter la deuxième ligne.

Pourtant, quand j'enlève la partie de ma formule ci-dessous, tous les msgbox donnent les bonnes infos, avec la piste complète, l'onglet et la cellule souhaitée pour chaque ligne.

With SHA
        .Range("S" & CA.Row) = Workbooks.Open(Filename:=piste).Sheets(k).Range(c)
End With

Mais quand j'ajoute cette partie à mon code, la piste en msgbox dès la deuxième ligne est incomplète, et du coup la macro bug car elle ne retrouve pas le fichier source.

Quelqu'un peut il m'aider à corriger la macro et m'expliquer pourquoi cette partie du code fait buger ma macro ?

Merci par avance,

f894009 · Answer

Re,

Ok, je fais plusieurs choses en meme temps, ai perdu le fil de la chose

Pouvez mettre un fichier exemple a dispo

Pour transmettre un fichier,
Veillez a ce qu'il n'y ait PAS DE DONNEES CONFIDENTIELLES
il faut passer par un site de pièce jointe tel que cjoint.com

Allez sur ce site : http://cjoint.com
Clic sur parcourir,
Cherche ton fichier,
clic sur ouvrir,
Clic sur "Créer le lien cjoint",
Copier le lien,
Revenir ici le coller dans une réponse...

ClassicDudu · Answer

Ci-joint le fichier pour exemple

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

f894009 · Answer

Re,

J'ai teste votre code avec des fichiers a moi et pas de probleme.

Comprends pas

ClassicDudu · Answer

De mon côté, le code fonctionne bien et colle bien la valeur pour la première ligne (dans le fichier exemple en cellule S11 donc). En revanche, pour la seconde ligne, la piste ne va considérer que la cellule W12, au lieu de prendre W12 & X12 & Y12 & Z12 & AA12 comme je l'écris dans mon code. Ce qui fait qu'il ne va pas trouver le fichier source, et arrêter la macro...

Avez vous bien essayé en allant chercher sur plusieurs lignes et dans plusieurs fichiers différents ? Je suis surpris que ca fonctionne chez vous et pas chez moi... :(

D'ailleurs, j'en profite pour poser une autre question : est-il possible d'intégrer une notion d' "erreur.si" dans le code, afin que la macro continue même s'il ne trouve pas le fichier source sur une ligne ? --> Si la macro ne trouve pas le fichier source sur la ligne 12, alors elle laisse la cellule S12 vide, et continue sur la ligne 13.

Si oui, pouvez-vous me donner l'ébauche de code à utiliser / adapter dans mon cas ?

Encore merci

f894009 · Answer

Bonjour,

est-il possible d'intégrer une notion d' "erreur.si" dans le code

Oui

Sub Testerboucle()
    
    'Définir Dimensions et chemins
    Dim WBA As Workbook
    Dim SHA As Worksheet
    Set WBA = ActiveWorkbook
    Set SHA = ActiveSheet
    Dim CA As Range
    
    On Error GoTo Suite
    'Boucle sur chaque cellule de l'onglet
    For Each CA In SHA.Range("S11:S15")
        
        k = SHA.Range("AB" & CA.Row).Value
        c = SHA.Range("AC" & CA.Row).Value
        piste = SHA.Range("W" & CA.Row).Value & Range("X" & CA.Row).Value & Range("Y" & CA.Row).Value & Range("Z" & CA.Row) & Range("AA" & CA.Row) & Range("AD" & CA.Row)
        
        MsgBox k
        MsgBox c
        MsgBox piste
        
        With SHA
                .Range("S" & CA.Row) = Workbooks.Open(Filename:=piste).Sheets(k).Range(c)
        End With
    
Suite:
    Next CA

End Sub

Quand vous dites plusieurs lignes dans un fichier, je les case ou?

VBA - Copier valeurs depuis fichiers à pistes variables

9 réponses

Discussions similaires