Discussion :

[MaxouOslo]

Bonjour,

J'ai déjà vu un sujet du genre nommé "Récupérer dynamiquement des données d'un autre fichier Excel fermé".
Cependant, mon besoin est nuancé. J'ai deux fichiers, le premier (Fichier1) fait office de base de données et détient des informations qui serviront à alimenter le second (Fichier2).
Le "Fichier1" est stocké dans un dossier nommé "Test". Dans le dossier "Test", il y a pas mal de fichiers excel de type (Fichier1) et servent tous à renseigner ce "Fichier2". Il y a un seul "Fichier2" et beaucoup de "Fichier1". Pour repérer les données d'un fichier voulu, les "Fichier1" commencent tous par une série de 9 chiffres qui leur sont propres.

Ainsi, j'aimerais coder en VBA un programme me permettant de :

1- Récupérer la série de chiffres qui vient d'être ajoutée dans la colonne B du Fichier2
2- En fonction de celle-ci, aller ouvrir le dossier Test
3- Trouver dans ce dossier un excel dont le nom commence par la série de chiffres relevée auparavant
4- Récupérer certaines des valeurs (admettons colonne B, C, D du Fichier1 pour les inclure en C, D, E du Fichier2)
5- Refermer le Fichier de type 1 ouvert

J'espère que mon texte est assez clair.
J'ai hâte de vous lire, merci d'avance.

Max

[ChristianBosch]

Salut,

Alors première étape si j'ai bien compris c'est de récupérer les nom des fichiers présents dons ton dossier. Pour ce faire on va utiliser la bibliothèque "microsoft scripting runtime".

En suite pour manipuler ton fichier c'est a toi de voir comment faire, il faut coder a la place de la ligne en commentaire dans la fonction "OuvrirFichier"

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
Sub test()
Dim SerieChiffres As String
Dim cheminFichier As String
Dim dossier As String
 
dossier = "le chemin de ton dossier ici"
SerieChiffres = "Ce que tu veux chercher "
 
cheminFichier = Nomfichier(dossier, SerieChiffres)
 
Select Case cheminFichier
 
Case "fauxD"
    MsgBox "Le chemin du dossier <" & dossier & "> est innaccessible."
Case "fauxI"
    MsgBox "Aucun fichier ne correspond a <" & SerieChiffres & ">"
Case Else
    OuvrirFichier (cheminFichier)
End Select
 
End Sub
Function Nomfichier(CheminDossier As String, SerieChiffre As String) As String
Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.Folder
Dim FileItem As Scripting.File
Dim erreur As Integer
 
Set FSO = CreateObject("Scripting.FileSystemObject")
On Error GoTo err
erreur = 0
Set SourceFolder = FSO.GetFolder(CheminDossier)
erreur = 1
For Each FileItem In SourceFolder.Files
    If InStr(1, FileItem.Name, SerieChiffre) <> 0 Then
        Nomfichier = FileItem
        Exit Function
    End If
Next FileItem
err:
Select Case erreur
Case 0
    Nomfichier = "fauxI"
Case 1
    Nomfichier = "fauxD"
End Select
 
End Function
 
Function OuvrirFichier(cheminFichier As String)
Dim wb As Workbook
Dim xlapp As Excel.Application
Set xlapp = CreateObject("Excel.Application")
xlapp.Visible = False
 
Set wb = xlapp.Workbooks.Open(cheminFichier)
    'ici manipuler le classeur en allant chercher les données dont tu as besoin
wb.Close False
xlapp.Quit
End Function

Il faut activer la bibliothèque de références microsoft scripting runtime sinon ca marchera pas.

[MaxouOslo]

Merci pour cette réponse, après avoir adapté, j'ai une erreur de compilation "Type défini par l'utilisateur non défini" à la ligne 6 : "cheminFichier = Nomfichier(dossier, SerieChiffres)"

De même, je ne comprends pas "on va utiliser la bibliothèque "microsoft scripting runtime"" je ne connais pas du tout et j'ai probablement dû oublier une étape.

Cependant, je vous remercie déjà pour cette première réponse plus que rapide !!

[MaxouOslo]

Je pense que la fonction NomFichier ne fonctionne pas comme voulu car lorsque je fais en pas à pas (F8) je survole la ligne et cela affiche "", or la série de chiffre est bien l'un des noms d'un des excels dans le dossier

[ChristianBosch]

Salut, peux tu mettre le code modifié ?

En suite quand j'ai mis la variable serieChiffres ca fait référence a ta phrase :

Trouver dans ce dossier un excel dont le nom commence par la série de chiffres relevée auparavant

Pour la bibliotheques de références il te faut l'activer dans le devellopeur VBA, dans la barre en haut de la fenetre tu vas sur "outils" ---> "Réferences" et tu coche la reférences correspondante(microsoft scripting runtime). C'est ce qui va te permettre de lire les dossiers.

[MaxouOslo]

alors l'activation via outil-> références j'avais fait, je me disais qu'il fallait peut-être autre chose.
Pour ce qui est de la partie modifiée je n'ai rien mis à la place du commentaire, je me concentre sur le programme et la compréhension de celui-ci c'est pourquoi je fais le pas-à-pas avec F8. La formule chemin fichier affiche "" quand je la survole. Je vous mets un screen dessous :
Je précise j'ai caché volontairement certaines partie de l'adresse du dossier (je ne veux pas me faire taper sur les doigts pour les infos que je donne haha) mais l'adresse je l'ai copié collé avec clic-droit depuis le fichier donc logiquement il n'y a pas d'erreur d'écriture.

[Pierre Fauconnier]

Salut.

A priori, je m'orienterais vers un Power Query paramétré

[ChristianBosch]

Salut, chez moi ça fonctionne par contre je pense que j'ai inversé les deux messages d'erreur dans le select case de ma fonction nomfichier.

Oui je précise ici que ce que je fais en grande partie avec ce code c'est de retrouver le chemin de ton fichier en fonction d'une partie de son nom, libre a toi par la suite de faire n'importe quel type d'opération une fois l'information en ta possession. (ici avec la fonction "OuvrirFichier" je viens juste l'ouvrir en instance cachée). Mais comme le dis Pierre on a d'autres possibilités concernant la récupération des données que tu désires. Je laisse les autres t'aider sur ce point car a par la méthodes workbooks.open et querryTable.add je ne connais rien d'autre

Pierre concernant l'utilisation de power querry en VBA aurait tu de la doc ? ça m’intéresse, c'est un sujet mal renseigné encore.

[MaxouOslo]

Merci à vous deux pour ces conseils, je suis de retour (j'étais en période école) et j'ai pu me remettre sur le code à tête reposé et faire fonctionner le code.
Power quéry je ne connais pas du tout !!
En tout cas vous m'avez bien aidé encore un GRAND MERCI !!!

[78chris]

Bonjour à tous

Power quéry je ne connais pas du tout !!

Etudiant ? C'est le moment ou jamais de t'y intéresser : cela existe depuis 10 ans...

[Pierre Fauconnier]

Salut Christian

[...]
Pierre concernant l'utilisation de power querry en VBA aurait tu de la doc ? ça m’intéresse, c'est un sujet mal renseigné encore.

Je suis en train de préparer des tutos sur Power Query, et notamment ce qu'on peut en faire avec du VBA, mais honnêtement, il y a déjà moyen de faire beaucoup sans VBA:

• Pondre du langage M en VBA me semble un peu inutile;
• Paramétrer une requête avec des données Excel ou TXT ou CSV est assez simple;
• Les options de mise à jour, notamment à l'ouverture du fichier, me semble simples à utiliser et couvrent la très grande majorité des besoins;
• ...

Dès lors, je pense qu'il est préférable d'avoir déjà une bonne maîtrise de Power Query avant que d'avoir besoin de VBA. Si en plus on allie Power Query avec Power Pivot et Dax, on repousse encore les limites après lesquelles on a besoin de VBA.

[MaxouOslo]

Oui vous avez raison !!
Petite question, là mon code est capable d'ouvrir le fichier en fonction de ce que j'ai comme série de chiffres puis le fermer.
Donc pour rappel, j'ai un fichier avec des séries de chiffres en colonne A et en fonction de la série de chiffre sélectionnée, le code va ouvrir le dossier TEST et puis l'excel portant la série de chiffres dans son nom et va permettre la récupération de certaines données avant de se fermer.
Maintenant, j'aimerais savoir si c'est possible, au lieu de rentrer cette série de chiffre manuellement en colonne A avant de lancer le code, que la colonne A se remplisse seule en récupérant les 9 premiers caractères de tous les fichiers excel du dossier TEST ?
cordialement

[MaxouOslo]

Oups désolé, mais il doit y avoir une coquille dans le code, le fichier semble ne pas s'ouvrir dans la fonction OuvrirFichier, Est-ce que vous voyez des erreurs ?

[Pierre Fauconnier]

Qu'est-ce qui te fait dire que le classeur ne s'ouvre pas? L'application est placée par code en "invisible", du coup, tu ne vois pas le fichier s'ouvrir. As-tu une ligne "en jaune" sur laquelle le code bloque? Si oui, il serait judicieux de préciser laquelle et de mentionner le message d'erreur que tu reçois. Sans cela, on doit activer notre boule de cristal et j'ai perdu son câble de connexion usb...

Peut-être pourrais-tu exécuter le code en pas à pas et désactiver la ligne qui met l'appli en invisible, tu pourrais mieux suivre ce qui se passe.


NB: Il est préférable de copier le code et de le mettre entre les balises de code (bouton # lors de l'édition du message), ça facilite la recopie et nos tests.

[MaxouOslo]

dommage pour votre boule de cristal, j'en ai vu des pas chères sur wish je vous en ferai livrer une
Le pas à pas je l'avais fait c'est pourquoi je disais ça, c'est au moment de travailler sur le fichier ouvert que cela pose problème (la ligne jaune arrive jusqu'au commentaire puis disparait et affiche la fenêtre ci-dessous)
Pourtant mon onglet est bien nommé "SYNTHESE" dans le fichier ouvert.
Je m'en remets aux experts haha

[MaxouOslo]

Le débogage cible bien la ligne "Sheets("SYNTHESE").Activate

[MaxouOslo]

L'intégralité du code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
 
Sub test()
Dim SerieChiffres As String
Dim cheminFichier As String
Dim dossier As String
 
 
 
dossier = "C:\Users\max\Desktop\TEST"
SerieChiffres = 123456789
 
cheminFichier = Nomfichier(dossier, SerieChiffres)
 
Select Case cheminFichier
 
Case "fauxD"
    MsgBox ("Aucun fichier ne correspond a <" & SerieChiffres & ">")
Case "fauxI"
    MsgBox ("Le chemin du dossier <" & dossier & "> est innaccessible.")
Case Else
    OuvrirFichier (cheminFichier)
End Select
'ce que je veux qu'il fasse dans le fichier de recup
 
Sheets("RECUP").Range("B12:B300").Find(SerieChiffres).Select
 
    With ActiveCell
        .Offset(0, -1).Value = Parc
    End With
 
 
 
End Sub
 
Function Nomfichier(CheminDossier As String, SerieChiffre As String) As String
Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.Folder
Dim FileItem As Scripting.File
Dim erreur As Integer
 
Set FSO = CreateObject("Scripting.FileSystemObject")
On Error GoTo err
erreur = 0
Set SourceFolder = FSO.GetFolder(CheminDossier)
erreur = 1
For Each FileItem In SourceFolder.Files
    If InStr(1, FileItem.Name, SerieChiffre) <> 0 Then
        Nomfichier = FileItem
        Exit Function
    End If
Next FileItem
err:
Select Case erreur
Case 0
    Nomfichier = "fauxI"
Case 1
    Nomfichier = "fauxD"
End Select
 
End Function
 
Function OuvrirFichier(cheminFichier As String)
Dim wb As Workbook
Dim xlapp As Excel.Application
 
Dim Parc As Variant
Dim ABC As Variant
Dim Designation As Variant
Dim Famille As Variant
 
Set xlapp = CreateObject("Excel.Application")
xlapp.Visible = False
 
Set wb = xlapp.Workbooks.Open(cheminFichier)
    'ici manipuler le classeur en allant chercher les données
    Sheets("SYNTHESE").Activate
    Parc = Sheets("SYNTHESE").Range("A5").Value
    ABC = Sheets("SYNTHESE").Range("C5").Value
    Designation = Sheets("SYNTHESE").Range("D5").Value
    Famille = Sheets("SYNTHESE").Range("E5").Value
 
wb.Close False
xlapp.Quit
End Function

[Pierre Fauconnier]

Le message me semble clair: La feuille SYNTHESE n'existe pas...

Problème d'accent, d'espace à la droite du nom?

[MaxouOslo]

Non justement, j'ai re enregistré également le document qui s'ouvre de manière à ce qu'il accepte les macros, je me suis assuré qu'il y avait bien Microsoft runtime scripting..
Là c'est un mystère, je vais essayer avec d'autres noms d'onglet

[MaxouOslo]

Maintenant, lorsque j'ouvre manuellement le fichier que le code doit ouvrir il me marque qu'il est verrouillé par un autre utilisateur en cours d'utilisation sauf que cet autre utilisateur est moi, peut-être est-ce la raison ? je peux tout de même l'ouvrir en lecture seule