Discussion :

[kemherar]

Bonjour,

Je cherche une solution VBA à mon problème. Je me débrouille sur les bases, mais la ce que je veux faire dépasse mes connaissances.

J'ai deux fichiers un "balance" que je crée à partir de sage50 et l'autre "analyse" que je crée de toute pièce.

Mon objectif final est d'obtenir un tableau de données pour me faire un graphique qui va représenté le niveau d'encaisse (et d'autre données) à chaque vendredi.
Mon fichier d'analyse doit donc rechercher la ligne nommé "encaisse" du fichier "balance" et rapporter dans la première colonne du tableau "analyse" la donnée qui se trouve dans la colonne 3.
Ce type de problème me fait beaucoup penser à la rechercheV, mais je suis sur que ce serait plus efficace avec des validations et des conditions.

Je prévois mettre à jour(écraser) mon fichier "balance" à chaque vendredi et démarrer la macro pour que la nouvelle donnée s'ajoute à "analyse" à la suite des autres.

J'ai joint une extraction de l'entreprise modèle pour aider.

Balance.xls

[Thautheme]

Bonjour Kemherar, bonjour le forum,

Mon fichier d'analyse doit donc rechercher la ligne nommé "encaisse" du fichier "balance" et rapporter dans la première colonne du tableau "analyse" la donnée qui se trouve dans la colonne 2.

Ça ne serait pas plutôt la donnée en colonne 3 ?... (cette remarque surtout pour retrouver ton fil)

[kemherar]

Oui merci, c'est bien la colonne 3 qui contient l'information. j'ai fait l'ajustement.

[Invité]

Bonjour,

Une solution possible dans le fichier joint.

Vous avez une colonne débit et une colonne crédit, fallait-il récupérer les deux valeurs ?

A revoir peut-être le format date.

Cordialement.

[Thautheme]

Bonsoir le fil, bonsoir le forum,

Une autre proposition avec le code ci-dessous (j'ai considéré que les deux classeurs se trouvaient dans le même dossier) :

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
Sub Macro1()
Dim CS As Workbook 'déclare la variable CS (Classeur Source)
Dim CH As String 'déclare la variable CH (Chemin d'accès)
Dim OS As Worksheet 'déclare la variable OS (Onglet Source)
Dim CD As Workbook 'déclare la variable CS (Classeur Destination)
Dim OD As Worksheet 'déclare la variable CS (Onglet Destination)
Dim R As Range 'déclare la variable R (Recherche)
 
Application.ScreenUpdating = False 'masque les rafraîchissements d'écran
Set CS = ThisWorkbook 'définit le classeur source CS
CH = CS.Path & "\" 'définit le chemin d'accès
Set OS = CS.Sheets("Feuil1") 'définit l'onglet source OS
On Error Resume Next 'gestion des errurs (en cas d'erreur passe à la ligne suivante)
Set CD = Workbooks("Analyse.xls") 'définit le classeur destination (génère une erreur si ce classeur n'est pas ouvert)
If Err <> 0 Then 'condition : si une erreur a été générée
    Err.Clear 'supprime l'erreur
    Workbooks.Open (CH & "Analyse.xls") 'ouvre le classeur "Analyse.xls"
    Set CD = ActiveWorkbook 'définit le classeur destination CD
End If 'fin de la condition
On Error GoTo 0 'annule la gestion des erreurs
Set OD = CD.Sheets("Feuil1") 'définit l'onglet ddestination OD
'définit la recherche R (recherche entière de "Encaisse" dans la colonne 2 (=B) de l'nglet source OS
Set R = OS.Columns(2).Find("Encaisse", , xlValues, xlWhole)
If Not R Is Nothing Then 'condition : si il existe au moins une occurrence trouvée
    'définit la cellule de destination DEST (A1 si A1 est vide, sinon la première ligne vide de la colonne A de l'onglet OD)
    Set dest = IIf(OD.Range("A1") = "", OD.Range("A1"), OD.Cells(Application.Rows.Count, 1).End(xlUp).Offset(1, 0))
    R.Offset(0, 1).Copy dest 'copie la valeur (en texte dans l'exemple ?) et la colle dans DEST
    dest.Value = dest.Value * 1 'transforme la valeur en texte en valeur numérique
    dest.NumberFormat = ". ## ;-. #;" 'format nombre à deux décimales
End If 'fin de la condition
CD.Save 'enregistre le classeur destination CD
CD.Activate 'active le classeur destination CD
OD.Activate 'active l'onglet destination OD
dest.Select 'sélectionne la dernière données récupérée
Application.ScreenUpdating = True 'affiche les rafraîchissements d'écran
End Sub

[kemherar]

Merci, je n'ai pas encore consulté les solutions, car j'ai été occupé sur autre chose (mon nouvel ordi de travail est arrivé!!)

Je vais voir si je peux bien les appliquer et apprendre autant que possible ce qui s'y trouve.

Et oui je peux garder les deux dans le même dossier

Bonsoir le fil, bonsoir le forum,

Une autre proposition avec le code ci-dessous (j'ai considéré que les deux classeurs se trouvaient dans le même dossier) :

J'avais compris dossier dans le sens de répertoire. Il me faut vraiment 2 fichiers distincts. Cependant le code fonctionne et j'aime bien les explications.

Bonjour,

Une solution possible dans le fichier joint.

Vous avez une colonne débit et une colonne crédit, fallait-il récupérer les deux valeurs ?

A revoir peut-être le format date.

Cette solution par contre est impeccable. j'aurais eu tendance à faire la saisi de donnée à l'horizontale plutôt que verticale (habitude de visualitsation), mais vraiment ça fonctionne à merveille.
Pour ce qui est de la récupération de valeur, il n'y a qu'une seule valeur à récupérer, cependant dans mon fichier final j'aurai à récupérer une valeur qui sera au crédit et non au débit. (le débit serait à 0)

Merci Érik, je planche sur votre solution pour voir comment ajouter éléments dans la recherche.
Dans mon final, mes colonnes seraient dans l'ordre: "Date" (la façon de récupérer la date convient, car elle aura toujours le même format à 10 caractère à droite), "Compte courant" (avec la mise à jour de mon logiciel, cela remplace Encaisse, mais ce n'est pas un problème), "comptes clients", "comptes fournisseurs"

J'ai eu 30 min pour y voir donc je vais continuer lundi prochain merci encore.

[Invité]

Bonjour,

A noter que si le nom de l'onglet du fichier Balance était clairement identifié, il n'y aurait pas besoin de faire la boucle sur chaque onglet pour tester la présence du mot Encaisse.
Il suffirait pour cela de préciser que les données sont toujours sur l'onglet Feuil1 par exemple ou donner un nom à cet onglet. Ce serait plus rapide.

On peut imaginer également que le fichier Balance contienne les situations semaine par semaine depuis le début d'un exercice ou sur une année glissante. Avec la boucle vous pourriez reconstituer l'ensemble de l'exercice ou les 12 derniers mois, et terminer le programme sur un graphique.

Cordialement.

[Thautheme]

Bonjour le fil, bonjour le forum,

La solution que je t'ai proposée utilisait bien deux fichiers distincts dans le même dossier/répertoire. Tu as aimé les commentaires mais visiblement tu ne les as pas compris... Mais ce n'est pas bien grave car la proposition d'Eric est excellente.

[kemherar]

Bonjour le fil, bonjour le forum,

La solution que je t'ai proposée utilisait bien deux fichiers distincts dans le même dossier/répertoire. Tu as aimé les commentaires mais visiblement tu ne les as pas compris... Mais ce n'est pas bien grave car la proposition d'Eric est excellente.

J'ai été bête, il y a effectivement la ligne de code pour mette un chemin d'accès différent. Je viens de remarquer qu'il est simplement configurer au classeur actuel faute de l'avoir ajusté

Bonjour,

A noter que si le nom de l'onglet du fichier Balance était clairement identifié, il n'y aurait pas besoin de faire la boucle sur chaque onglet pour tester la présence du mot Encaisse.
Il suffirait pour cela de préciser que les données sont toujours sur l'onglet Feuil1 par exemple ou donner un nom à cet onglet. Ce serait plus rapide.

On peut imaginer également que le fichier Balance contienne les situations semaine par semaine depuis le début d'un exercice ou sur une année glissante. Avec la boucle vous pourriez reconstituer l'ensemble de l'exercice ou les 12 derniers mois, et terminer le programme sur un graphique.

Cordialement.

Le fichier qui est créé automatiquement par mon programme utilise toujours le nom feuil1 par défaut et je ne le change pas pour limiter le nombre de manipulation. Dans quel section du code je dois faire la modification pour ne rien détruire.

Je souhaites effectivement constituer l'ensemble de l'exercice pour sortir un graphique. Mon problème à ce niveau, et je ne peux pas le régler avec excel, c'est que le logiciel ne me permet pas de sortir des comparatifs multiples. Je pourrais avoir tout au plus 2 périodes coller, mais cela ne me rend pas plus rapide.

[Invité]

Le fichier qui est créé automatiquement par mon programme utilise toujours le nom feuil1 par défaut et je ne le change pas pour limiter le nombre de manipulation. Dans quel section du code je dois faire la modification pour ne rien détruire.

Bonjour,

Le fichier joint contient la macro et la boite de dialogue modifiées.

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
 
Sub RecuperationEncaisse(ByVal WbBalance As Workbook, CelluleCible2 As Range)
 
Dim CelluleRecherchee As Range
'Dim ShBalance As Worksheet
 
       ' For Each ShBalance In WbBalance.Worksheets
 
        With Sheets("Feuil1")
            Set CelluleRecherchee = .Cells.Find(what:="Encaisse", LookIn:=xlValues, lookat:=xlWhole)
            If Not CelluleRecherchee Is Nothing Then CelluleCible = CDbl(CelluleRecherchee.Offset(0, 1))
            Set CelluleRecherchee = Nothing
 
            Set CelluleRecherchee = .Cells.Find(what:="Balance de vérification", LookIn:=xlValues, lookat:=xlPart)
            If Not CelluleRecherchee Is Nothing Then CelluleCible.Offset(0, 1) = Right(CelluleRecherchee, 10)
            Set CelluleRecherchee = Nothing
        End With
       ' Next ShBalance
 
End Sub

La boite de dialogue ne contient plus le bouton Récupérer. La récupération se fait dans la foulée de la sélection du fichier Balance et la boite de dialogue est déchargée à la fin.

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
 
Private Sub BoutonRepertoire_Click()
 
    FichierAOuvrir = Application.GetOpenFilename("Fichiers(*.xls*),*.xls*")
    If FichierAOuvrir <> False Then
 
       TextBoxFichier = Dir(FichierAOuvrir) 'CurDir
       Repertoire = CurDir
       NomDuFichier = Dir(FichierAOuvrir)
       Sheets("Analyse").Range("RepertoireEncaisse") = CurDir
 
       Application.ScreenUpdating = False
       OuvertureFichiers NomDuFichier, Repertoire
       Set WbEncaisse = ActiveWorkbook
       RecuperationEncaisse WbEncaisse, CelluleCible
       FermetureFichiers NomDuFichier, False
       Set WbEncaisse = Nothing
       Unload UserFichier
       Application.ScreenUpdating = True
 
       MsgBox "Récupération terminée !", vbInformation
 
    End If
 
End Sub

Si vous souhaitez ne générer qu'un seul onglet dans un nouveau fichier, ajoutez cette ligne de commande :

Code :

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

1
2
3
 
Application.SheetsInNewWorkbook = 1
'Workbooks.Add

Attention, cette ligne de commande modifie la valeur de réglage par défaut dans les Options d'Excel.

Cordialement.

[kemherar]

Jusqu'à maintenant c'est super. J'ai réussi à ajuster le code pour l'adapter aux 4 données que je dois chercher. c'est merveilleux, j'ai galéré un peu, mais ça m'a permis d'apprendre quelques trucs.

Mon dernier point est au niveau du bouton d'importation. J'aimerais le simplifier encore plus.
Sans tout enlever ce qui est déjà, j'aimerais comprendre où je pourrais ajuster l'action qui se produit lorsque je clique sur le bouton. Actuellement le fichier inscrit le répertoire qui contient mon fichier d'encaisse et j'aimerais être encore plus précis en lui disant d'ouvrir automatique le fichier "balance" qui s'y trouve

[Invité]

Bonjour,

Si le répertoire et le nom du fichier contenant la valeur à récupérer sont toujours les mêmes, il n'y a pas besoin de boite de dialogue. La récupération peut être lancée directement depuis le bouton en modifiant la procédure MettreAJourLEncaisse :

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
 
Sub MettreAJourLEncaisse()
 
Dim LigneCibleEnCours As Long
 
        Application.ScreenUpdating = False
 
        Repertoire = "C:\Users\......" ' A adapter
        NomDuFichier = "Balance.xls"
 
        Set ShCible = ActiveWorkbook.Sheets("Analyse")
        With ShCible
 
             LigneCibleEnCours = .Cells(.Rows.Count, 1).End(xlUp).Row
             Set CelluleCible = ShCible.Cells(LigneCibleEnCours + 1, 1)
 
             OuvertureFichiers NomDuFichier, Repertoire
             Set WbEncaisse = ActiveWorkbook
             RecuperationEncaisse WbEncaisse, CelluleCible
             FermetureFichiers NomDuFichier, False
             Set WbEncaisse = Nothing
 
             Set CelluleCible = Nothing
 
        End With
        Set ShCible = Nothing
        Application.ScreenUpdating = True
        MsgBox "Récupération terminée !", vbInformation
 
End Sub

Cordialement.

[kemherar]

c'est magique, merci à vous.
J'en ai encore beaucoup à apprendre, mais j'ai beaucoup de plaisir à décoder ce fichier que vous m'avez créé.