Résolu Mise à jour automatique des valeurs ( VBA excel )

Gaille60

Nouveau membre
Bonjour,

Après de multiples recherches infructueuses, je me décide à vous posez ma question.


Je précise que je suis un débutant complet et qu'il est bien possible que la solution saute directement aux yeux des experts.

Voici mon souci.
J'essaie de créer une macro afin de convertir en automatique des fichiers commandes clients afin de pouvoir par la suite les injecter directement dans un WMS.

Concernant la conversion du fichier, je souhaite, que l'utilisateur n'est qu'a sélectionner le fichier source et enregistrer le fichier final.

J'arrive très (plutôt) bien a ouvrir le fichier et a créer le nouveau, mais j'ai plusieurs formules, et a chaque mise a jour des formules, excel me demande de réouvrir le fichier initial ( qui à un nom différent a chaque fois ). Je pense qu'il manque quelque chose avant ou après les lignes ou j'ai mes formules mais je ne trouve absolument rien pour m'aider

Ci-dessous le code que j'utilise. D'avance merci pour vos retours.
Code:
Private Sub parcourir_Click()

'ajouter nouveau classeur

Workbooks.Add


'ouvrir la boite de dialogue

    With Application.FileDialog(msoFileDialogOpen)
    'arriver à la racine voulue
        .InitialFileName = "C:\Users\gnotta\Desktop\DOC travail\JHAG\"
        
        'autoriser multi selection
        .AllowMultiSelect = False
        .Show
       strFileName = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
        

Workbooks.Open strFileName

Dim Nom As Workbook


conversion_fichier.Hide

    End With

 


   
    ActiveSheet.UsedRange.Select
    ActiveWindow.SmallScroll Down:=9
    Range("A14:E503").Select
    Selection.Copy
    Windows("Classeur1").Activate
    'copier uniquement les valeurs
    
    Range("E1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                             SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    
    'suppression des colonnes
    
    Columns("G:H").Select
    Selection.Delete Shift:=xlToLeft
    
    'début des formules
    
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[4]>0,'[Workbook]BDC'!R2C[1],"""")"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[3]>0,'[Workbook]BDC'!R5C,"""")"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[2]>0,""FM"","""")"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[1]>0,""FM"","""")"
    
    'etirer les formules
    
    Range("A1: D1").Select
    Selection.AutoFill Destination:=Range("A1: D490"), Type:=xlFillDefault
    Range("A1: D490").Select
    
    
    
    Range("A1").Select
    
    
    
End Sub
 

drul

Obscur pro du hardware
Staff
Salut,
C'est au moment ou tu ajoutes les formules que tu as le popup ? que dit-il exactement ?
Tu as vraiment besoin d'un lien externe dans tes nouveaux classeurs ?

P.S. le code pondu par l'enregistreur de macro est vraiment atroce ;)
 

Gaille60

Nouveau membre



Oui c'est au moment ou la macro arrive sur les formules, j'ai une boite de dialogue pour ouvrir le fichier avec en entête "mise a jour des valeurs workbook" et la je suis obligé 4 fois de suite de selectionner le fichier que j'ouvre au début de la macro.

J'ai besoin de lien externe dans la mesure ou mes valeurs sont copiées d'un autre classeur si il y'a une autre solution je suis preneur.

Comme je le disais je suis débutant et j'apprends pas à pas, pour le moment la solution la plus rapide ( pour moi ) et d'utiliser l'editeur de macro et de voir comment il se comporte :D

 

drul

Obscur pro du hardware
Staff
A mon avis il manque juste le path du fichier externe

ActiveCell.FormulaR1C1 = "=IF(RC[4]>0,'c:\mypath\[Workbook]BDC'!R2C[1],"""")"

Ou quelques chose du genre ... (vraiment pas l'habiture du format R1C1 ...)
 

Gaille60

Nouveau membre



Non j'ai rajouté le chemin du fichier mais malgré tout ce la ne fonctionne pas :(
 

drul

Obscur pro du hardware
Staff
C'est quoi le nom du fichier exact ?
ça à l'air de fonctionner chez moi si je met le path correct et le bon nom de fichier ...
(dsl, vraiment pas l'habitude de jouer avec des références externes ...)
 

Gaille60

Nouveau membre



Exact cela fonctionne avec le nom di fichier exact, je m'étais arrêté au chemin.

Le problème maintenant c'est que mon fichier n'aura jamais le même nom ( mais toujours le même chemin )

 

drul

Obscur pro du hardware
Staff
Et tu le sais comment le nom du fichier ? c'est dépendant de quoi ?

Si veux créer un lien sur quelquechose, il est toujours utile de savoir comment définir ce quelque chose :o

 

Gaille60

Nouveau membre


Le fichier est envoyé par un client il comporte le n de commande et le destinataire mais il n'y a pas de logique dedans par exemple ça va être 17/802 RWC er me lendemain 17/814 Tartempion.

 

Gaille60

Nouveau membre
Elles sont stockées à chaque fois dans un fichier différent qui eux sont dans un même dossier.
 

Gaille60

Nouveau membre
Je précise par contre, qu'au début de la macro, j'ouvre ce ficher une première fois et je souhaiterais qu'une fois arrivé aux formules, ne pas être obligé de le selectionner 4 fois de plus.
 

drul

Obscur pro du hardware
Staff
Ok, alors ce nom est stocké dans strfilename et la path est: "C:\Users\gnotta\Desktop\DOC travail\JHAG\"
C'est bien ça ?
Tu peux me mettre un exemple de code qui a marché (avec un fichier de ton choix, c'est juste pour la syntaxe) ?
 

Gaille60

Nouveau membre
C'est bien ca,

Voici un exemple de ce qui a fonctionné
Code:
Range("A1").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[4]>0,'C:\Users\gnotta\Desktop\DOC travail\JHAG\[16-1108 UWC.xlsx]BDC'!R2C[1],"""")"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[3]>0,'C:\Users\gnotta\Desktop\DOC travail\JHAG\[16-1108 UWC.xlsx]BDC'!R5C,"""")"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[2]>0,""FM"","""")"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[1]>0,""FM"","""")"
 

drul

Obscur pro du hardware
Staff
Essaie ça:
Code:
ActiveCell.FormulaR1C1 = "=IF(RC[4]>0,'C:\Users\gnotta\Desktop\DOC travail\JHAG\[" & strFileName & "]BDC'!R2C[1],"""")"

Après, si ça marche, je te propose de nettoyer un peu ta macro, si ça t'intéresse ...

P.S. essaye de poster ton code entre balise: [code="vb"] ... ton code ... [/code]
 

Gaille60

Nouveau membre
Alors il y'a du mieux, je n'ai plus les boites de dialogues pour ouvrir les fichiers qui s'affichent, mais les résultats de la formules sont en erreur #REF

En regardant la formule générée, je me suis aperçu que j'avais deux fois le path du fichier, je l'ai donc retirer du code pour arriver à ceci
Code:
ActiveCell.FormulaR1C1 = "=IF(RC[4]>0,'[" & strFileName & "]BDC'!R2C[1],"""")"

Voila ce que donne la formule générée
=SI(E1>0;'[C:\Users\gnotta\Desktop\DOC travail\JHAG\[16-1102 AROMATEKA.xlsx]BDC]16-1102 AROMATEKA.xlsx]BDC'!B$2;"")

Je suis perdu, je ne comprends pas pourquoi j'ai deux fois le fichier dans la formule ?

PS : Je suis ok pour nettoyer le code ensuite et si tu as quelques conseils pour commencer à les écrire plutot que d'utiliser l'assistant macro, je suis preneur aussi.
 

drul

Obscur pro du hardware
Staff
Meilleure réponse
Ok, alors strfilename contient aussi le path, j'avais pas penser à ça ...

Donc on va faire ceci:
Code:
Private Sub parcourir_Click()
 
'ajouter nouveau classeur
Dim openWb As Workbook
Workbooks.Add
 
 
'ouvrir la boite de dialogue
 
    With Application.FileDialog(msoFileDialogOpen)
    'arriver à la racine voulue
        .InitialFileName = "C:\Users\gnotta\Desktop\DOC travail\JHAG\"
 
        'autoriser multi selection
        .AllowMultiSelect = False
        .Show
       strFileName = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
 
 
Set openWb = Workbooks.Open(strFileName)
 
conversion_fichier.Hide
 
    End With
 
 
 
 
 
    ActiveSheet.UsedRange.Select
    ActiveWindow.SmallScroll Down:=9
    Range("A14:E503").Select
    Selection.Copy
    Windows("Classeur1").Activate
    'copier uniquement les valeurs
 
    Range("E1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                             SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
 
    'suppression des colonnes
 
    Columns("G:H").Select
    Selection.Delete Shift:=xlToLeft
 
    'début des formules
 
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[4]>0,'" & openWb.Path & "\[" & openWb.Name & "]BDC'!R2C[1],"""")"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[3]>0,'" & openWb.Path & "\[" & openWb.Name & "]BDC!R5C,"""")"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[2]>0,""FM"","""")"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[1]>0,""FM"","""")"
 
    'etirer les formules
 
    Range("A1: D1").Select
    Selection.AutoFill Destination:=Range("A1: D490"), Type:=xlFillDefault
    Range("A1: D490").Select
 
 
 
    Range("A1").Select
 
 
 
End Sub

Il faut rajouter toute les lignes ou openWb est présent
 

Gaille60

Nouveau membre
Et bien je n'ai qu'une chose à dire MERCI.

Ça fonctionne enfin !!!

Par contre concrètement a quoi sert cette commande ?
 

drul

Obscur pro du hardware
Staff
openWb n 'est pas une commande, mais une variable qui représente un workbook.
je lui affecte le workbook ouvert ici:
Code:
Set openWb = Workbooks.Open(strFileName)
A partir de ce moment openWb EST le workbook ouvert.

ensuite pour reconstruire la formule, j'utilise deux propriété des workbook qui sont "path" et "name". Le premier contient le chemin d'accès (sans le dernier \) et le second le nom du workbook.

N.B. cette façon de faire fonctionne aussi si tes workbook ne sont pas dans le même dossier.
Pour le nettoyage, la première chose à faire est:
Code:
toto.select
selection.quelquechose
'se remplace très avantageusement par:
toto.quelquechose
Commence par faire ce remplacement partout ou tu le peux et reposte ton code, on verra s'il reste 2 ou 3 trucs à améliorer
 

Gaille60

Nouveau membre
J'ai commencer le nettoyage selon tes préconisations et un peu plus au niveau selection des cellules et saisie des formules, voila ce que ca donne

Code:
Private Sub parcourir_Click()

'ajouter nouveau classeur
Dim openWb As Workbook
Workbooks.Add


'ouvrir la boite de dialogue
    With Application.FileDialog(msoFileDialogOpen)
    'arriver à la racine voulue
        .InitialFileName = "C:\Users\gnotta\Desktop\DOC travail\JHAG\"
        'autoriser multi selection
        .AllowMultiSelect = False
        .Show
       strFileName = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
        

Set openWb = Workbooks.Open(strFileName)


'fermeture user form
conversion_fichier.Hide

    End With
   
   
    ActiveSheet.UsedRange.Select
    Range("A14:E503").Copy
    Windows("Classeur1").Activate
    
    'copier uniquement les valeurs
    Range("E1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                             SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    
    'suppression des colonnes
    Columns("G:H").Delete Shift:=xlToLeft
    
    'début des formules
    Range("A1").FormulaR1C1 = "=IF(RC[4]>0,'" & openWb.Path & "\[" & openWb.Name & "]BDC'!R2C[1],"""")"
    Range("B1").FormulaR1C1 = "=IF(RC[3]>0,'" & openWb.Path & "\[" & openWb.Name & "]BDC'!R5C,"""")"
    Range("C1").FormulaR1C1 = "=IF(RC[2]>0,""FM"","""")"
    Range("D1").FormulaR1C1 = "=IF(RC[1]>0,""FM"","""")"
    
    'etirer les formules
    Range("A1:D1").AutoFill Destination:=Range("A1:D490"), Type:=xlFillDefault
    Range("A1").Select
    
  
    
End Sub
 
Vous devez vous inscrire ou vous connecter pour répondre ici.
Derniers messages publiés
Statistiques globales
Discussions
730 098
Messages
6 717 097
Membres
1 586 286
Dernier membre
petitangebleu1977
Partager cette page
Haut