Mise à jour d'un fichier Excel avec un autre fichier dont la structure est
Bonjour, un sujet datant de janvier 2016 traitait le sujet. les réponses fournies par le membre BANZAI64 furent armées de patience et très complète, néanmoins après adaptation de son généreux code, j'aimerai une subtilité que je ne parviens pas à réaliser dans la modification du code VBA.
En effet, mon Tableau1 à mettre à jour comporte en colonne A l'élément à repérer dans le Tableau2 (situé en colonne A aussi) et prendre les données se trouvant en colonne B et C du Tableau2 afin de mettre respectivement le Tableau1 à jour en colonne B et H; les données en B du Tableau2 sont à placer en B du tableau1 et les données en C du Tableau2 sont à placer en H du Tableau1.
Idem pour les données manquantes dans mon Tableau1, le A du tableau2 doit se mettre en A du tableau 1, le B du Tableau2 doit se mettre en B du Tableau1 et enfin le C du Tableau2 doit se mettre en H du Tableau1 sans modifier les autres colonnes.
le code actuel ci-dessous m'efface malheureusement les données de la colonnes A du Tableau1, mais remplit correctement le B et le H
Etant novice et le code utilisé inhabituel pour moi, pourriez-vous me guider sur le soucis.
Sub UPDATE_Stock_et_Ref()
Dim Chemin As String, Fichier As String, J As Long, I As Integer, Cel As Range
Dim T1(1 To 2, 1 To 8) As String
Dim Ws As Worksheet
Application.ScreenUpdating = False
Set Ws = ActiveSheet
Chemin = ThisWorkbook.Path & Application.PathSeparator
'désigne la variable de notre fichier Cde Auto
Fichier = "Liste_des_stocks_Stock_Cathedrale.xlsm"
'désigne la variable du listing stock exporté de MatTrack
If Dir(Chemin & Fichier) = "" Then
MsgBox "Fichier pour mise à jour introuvable" & vbCr & Fichier
Exit Sub
'Condition si le listing stock n'est pas trouvé afin de sortir de la procédure
End If
With Workbooks.Open(Chemin & Fichier)
With .Sheets(1)
'Prend en compte la feuille 1 du fichier qu'il vient d'ouvrir (si besoin de mettre le nom de l'onglet, placer celui-ci entre guillemets
For J = 4 To .Range("A" & Rows.Count).End(xlUp).Row
' Parcourt de la ligne 4 dans la colonne A jusqu'à la dernière cellule non vide de cette colonne (références MatTrack à rechercher)
T1(1, 2) = .Cells(J, "B")
T1(1, 8) = .Cells(J, "C")
Set Cel = Ws.Columns("A").Find(what:=.Range("A" & J), LookIn:=xlValues, lookat:=xlWhole)
'recherche les correspondance des codes MatTrack entre la collone A du fichier des Cdes Auto et la colonne A du listing MatTrack
If Not Cel Is Nothing Then
Cel.Resize(1, UBound(T1, 2)) = T1
'Si correspondance code MatTrack Cde Auto trouvée dans le listing Cde Auto, mise à jour des données description et Stock
Else
Ws.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Resize(1, UBound(T1, 2)) = T1
'insère les code MatTrack manquants venant du listing en bas du tableau
End If
Next J
End With
.Close savechanges:=False
'ferme le listing sans le sauvgarder
End With
End SubBonjour,
Le problème vient peut-être de l'utilisation d'un tableau (T1), dont on ne semble garnir que les 2e et 8e colonnes (les autres restant vides, on colle donc du "vide" en première colonne ... mais aussi, sans doute dans le colonnes de C à G ??)
Sans fichier pour tester, c'est compliqué !
Par ailleurs, on est d'accord qu'une partie des manipulations semble correspondre à des RECHERCHEV ?
J'allais aussi évoquer Power Query, mais ta version d'Excel, c'est vraiment ... 97 ?
Bonsoir, tout d'abord merci de l'intérêt porté à mon problème.
J'ai corrigé ma version Excel en 365FR
En effet, je viens de comprendre que l'utilisation en tableau (T1) n'était pas le code à utiliser.
il est assez tard et je viens de rentrer du travail, demain je refais un topo de mes souhait et si besoin ajouterai les fichiers.
J'avais abandonner le fait de créer des formules RECHERCHEV dans mon fichier, car en effet c'est une grosse partie du résultat à obtenir, mais je voudrais gérer la mise à jour à la demande et non pas à l'ouverture de mon fichier allant rechercher systématiquement les liens vers les deux autres fichiers concernés.
Je m'attarde dés demain en soirée à essayer d'expliquer bien mieux le résultat escompté.
J'aimerai si possible rester en langage VBA, déjà que j'y suis pas fortiche, alors le power query... désolé
Bon, je pense que pour aller vers une solution optimale est d’expliquer du mieux que je peux, ce à quoi j’aimerai arriver.
Je travaille avec un fichier « Cdes Auto » composé de 16 colonnes donc celles qui nous intéressent sont A (code), B (Description), C (stock up to date), D (utilisation) et la G, I, J K et L comportent des formules. Ce fichier permet pour son principal d’effectuer un calcul de commandes automatiques.
Le logiciel sous licence du travail est capable de m’importer deux fichiers à jour en XLS.
Le premier « Stock » est composé de 7 colonnes dont seules les trois premières (A, B et C) m’intéressent (respectivement : Code, description et stock. (info: Comporte tous les codes possibles)
Le deuxième « Utilisations » est composé lui de 5 colonnes dont seules la A(code) et la D (utilisation) me sont nécessaires. (info: Ne comporte que les codes pour lesquels il y a eu une utilisation).
Le point commun entre mon fichier et les imports sont bien sûr les codes, le restes des infos (Description, Stock et utilisation) sont à mettre à jour dans mon fichier Cdes Auto.
Le but est grâce aux deux fichiers importés dans le même dossier que le fichier de commandes est de mettre à jour (uniquement sur demande (via bouton VBA)) et ce,
- Pour les codes existants : la description, le stock, et l’utilisation (si utilisation il y à)
- Pour les codes non repris dans le fichiers Cde Auto », mais bien dans le fichier « Stock », ajouter des lignes et remplir A, B, C, D et les 4 colonnes comportant des formules (G, I, J, K et L)
- Ajouter une particularité (par exemple une colonne spécifique et en donner la valeur « A ») aux ajouts du point 2 afin de pouvoir effectuer un filtre sur ceux-ci par la suite via bouton VBA
- Si possible effectuer une comparaison inverse pour les codes présents dans le fichier « Cde Auto » mais pas dans le fichier « Stock (un code qui serait devenu obsolète) ; Par exemple : dans la même colonne spécifique et en donner la valeur « O » permettant un filtre via bouton VBA sur cette valeur
Si besoin, je peux essayer d’envoyer les 3 fichiers à titre d’exemple.
J’espère avoir été le plus clair possible et ne pas être trop gourmand concernant la finalité désirée.
Voici 3 fichiers à titre d'exemple, mais toujours dans l'état de brouillon...
Bonjour, Personne pour un soutien?
bonjour,
ma macro pour la partie "stocks" en utilisant un "tableau", alors si les formules sont okay pour toute la colonne, il ne faut plus rien faire.
Option Explicit
Public Const Fichier = "Stocks.xlsm" '"Liste_des_stocks_Stock_Cathedrale.xlsm" 'nom fixe du fichier
Sub UPDATE_Stocks_BSALV()
Dim Chemin As String, J As Long, I As Integer, Cel As Range
Dim Ws As Worksheet, WB_Cath As Workbook, b As Boolean, arr, arr1, LO, r As Variant, cDBR As Range, s
Application.ScreenUpdating = False
'PARTIE : EVENTUELLEMENT OUVRIR CATHEDRALE ET LIRE LE CONTENU DANS UN ARRAY ************************
On Error Resume Next
Set WB_Cath = Workbooks(Fichier)
On Error GoTo 0
b = (WB_Cath Is Nothing) 'workbook n'est pas ouvert
If b Then 'alors ouvrir
Chemin = ThisWorkbook.Path & Application.PathSeparator
'désigne la variable de notre fichier Cde Auto
'désigne la variable du listing stock exporté de MatTrack
If Dir(Chemin & Fichier) = "" Then
MsgBox "Fichier pour mise à jour introuvable" & vbCr & Fichier
Exit Sub
'Condition si le listing stock n'est pas trouvé afin de sortir de la procédure
End If
Set WB_Cath = Workbooks.Open(Chemin & Fichier)
End If
arr = WB_Cath.Sheets(1).UsedRange.Value 'le contenu >>> array
arr1 = WB_Cath.Sheets(1).UsedRange.Columns(1).Value 'le contenu >>> array
If b Then WB_Cath.Close savechanges:=False 'si ce fichier n'était pas ouvert, fermez !!!
'PARTIE : COMPARAITRE AVEC ARRAY ET COMPLETER *******************************************************
Set LO = ThisWorkbook.Worksheets("BD Articles").ListObjects("TBL_Articles") '---> un "tableau"
For I = 4 To UBound(arr) 'les vrai données ne commencent qu'a la 4ième ligne
If Len(arr(I, 1)) > 0 Then 'un vrai article
If I Mod 50 = 0 Then Application.StatusBar = "1 : " & I & " " & UBound(arr) 'voir progrès sur le statusbar
r = Application.Match(arr(I, 1), LO.DataBodyRange.Columns(1), 0) 'recherche de cet article dans le tableau colonne A
If Not IsNumeric(r) Then 'pas trouvé
LO.ListRows.Add 'ajouter nouvelle ligne
r = LO.ListRows.Count 'numéro de cette ligne
End If
LO.ListRows(r).Range.Resize(, 3).Value = Array(arr(I, 1), arr(I, 2), arr(I, 3)) 'ajouter comme nouvelle ligne
End If
Next
Set cDBR = LO.DataBodyRange
cDBR.Columns(1).Interior.Color = xlNone
For I = 1 To cDBR.Rows.Count
If I Mod 50 = 0 Then Application.StatusBar = "2 : " & I & " " & UBound(arr) 'voir progrès sur le statusbar
If Len(cDBR(I, 1).Value) > 0 Then 'un vrai article
If I Mod 50 = 0 Then Application.StatusBar = I & " " & UBound(arr) 'voir progrès sur le statusbar
r = Application.Match(cDBR(I, 1).Value, arr1, 0) 'recherche de cet article dans le tableau colonne A
If Not IsNumeric(r) Then 'pas trouvé
cDBR(I, 1).Interior.ColorIndex = 4 'colorer vert les articles inconnu dans "Stocks"
s = s & vbLf & cDBR(I, 1)
End If
End If
Next
Application.StatusBar = ""
DoEvents
With LO.Range
.Sort .Range("A1"), xlAscending, Header:=xlYes
End With
If Len(s) > 0 Then MsgBox s, vbInformation, "articles de trop"
End SubUn grand merci pour la réactivité.
Je teste et reviens vers vous.
SUPER...
Super le fonctionnement. Un tout grand merci.
Il me reste à finaliser code afin qu'il crée des bordures et recopie les formules en colonnes GIJK et L.
Ensuite m'occuper de la macro VBA pour le traitement des utilisations.
encore un tout grand merci.
si c'est un "tableau" (=listobject), les bordures et les formules s'ajustent eux-mêmes, donc créez un bordure autour et copiez les formules GIJKL sur toute la plage. Ce tableau n'a pas besoin de code supplémentair.
I
Encore un grand bravo cela fonctionne.
Si je peux être exigeant, pourrais-je vous demander si il est possible de créer un module afin de comparer cette fois les fichiers "utilisation" et "Cde_auto" afin que tout les codes repris dans "Cde_Auto" mais ne se trouvant pas dans "Utilisation" le code se colorie en rouge .
et que pour les codes correspondants, prendre la valeur du fichier "Utilisation" se trouvant en colonne D et la reporter en colonne D dans le fichier "CdeAuto".
encore merci de votre aide.
re,
ce sont quoi exactement ces fichiers "Utilisation" et "Stocks", ce sont de fichiers CSV ou TXT ou directement des fichiers excel, qu'un autre application gère ?
A peu prêt le même code que "Stocks", sans l'inverse.
Ce sont des fichiers extraits d'un autre logiciel sous format xls ou xlsm. Mon soucis est que je ne dois recuprrer qu'une seule colonne. Cela peut se faire aussi sous forme array?
merci
re,
non, un fichier excel sera la meilleur solution, c'est dangereux d'entrer dans un autre application.
Après comparaison des fichiers "Cde Auto" et "utilisation", je ne tiens pas à ajouter de lignes.
juste parcourir utilisation et si code existant dans Cde Auto, insérer dans Cde Auto en colonne D la valeur trouvée en colonne D du fichier utilisation.
j'ai essayé de modifier votre code, mais j'obtiens des erreurs, si vous avez facile et le temps de le modifier, je serai preneur
Pour le changement de couleur, je le ferai via mise en forme conditionnelle afin que la couleur se modifie si je rentre un valeur à la main. du genre si rien n'est écrit dans la colonne D du fichier "Cde Auto" alors code en vert..
Merci pour le soutien
j'ai essayé à expliquer les changements dans le commentaire.
Bonjour, un tout grand merci pour les explications détaillées me permettant de mieux comprendre le code et de pouvoir l'utiliser dans le futur aussi.
Cela fonctionne très bien.
Félicitation pour la réactivité, j'ai grâce à toi progresser à grand pas dans l 'élaboration de mon projet.
Je poursuis celui-ci et espère ne plus être confronté à des soucis dans le reste de son élaboration. (si jamais, je vous enverrai un msg, lol)
Un énorme grand merci à vous et au forum...qui permettez à des novices d'aboutir dans leur réalisation et surtout de les faire avancer à la compréhension VBA