Mettre à jour un tableau avec un fichier externe variable

Le 26/01/2021 à 18:55

Bonjour,

Je suis actuellement novice dans le monde VBA et désolé du dérangement mais je ne trouve pas la solution.

Je travaille actuellement sur un tableau de suivi, comportant un nombre de colonne précis, qui se met à jour automatiquement grâce à l'importation d'un fichier d'extraction.

Jusque là tout était simple, j'ai pu récupérer un bout de code qui a marché à la perfection sauf que je me suis aperçu par la suite que les fichiers importés n'avaient pas toujours le même nombre de colonne ce qui déplace les informations d'un fichier à l'autre et m'empêche de récupérer les bonnes données à chaque importation.

J'aimerai savoir s'il était possible de modifier la macro suivante pour que celle-ci recherche le libellé des colonnes qui m'intéressent pour mettre à jour mon tableau.

Voici ci dessous le code

Sub Import_de_données_2()

Dim Fichier As String, j As Long, I As Integer, Cel As Range
Dim T1(1 To 1, 1 To 10) As String           ' Tableau qui va contenir les infos voulues d'une ligne
Dim Ws As Worksheet

  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
        ' Bloque le raffraichissement écran (on ne voit rien de ce qui se passe
  Set Ws = ThisWorkbook.Worksheets("Base (2)")                     ' Plus facile à manipuler

  Application.ScreenUpdating = False 'Ouverture fenêtre de selection du fichier d'entrée
  Fichier = Application.GetOpenFilename
  Workbooks.Open Filename:=Fichier 'supprime le chemin
  Fichier = Dir(Fichier) 'Copie données fichier d'entrée vers fichier de sortie

  With Workbooks.Open(Fichier)                     ' On ouvre le fichier
    With .Sheets(1)                                         ' On va "travailler" avec la page 1
      For j = 2 To .Range("B" & Rows.Count).End(xlUp).Row
        For I = 1 To UBound(T1, 2)        ' Pour chaque élément du tableau
          ' On le remplit avec les infos de la bonne colonne (Regarde l'aide de Choose ou de CHOISIR() dans une page Excel)
          T1(1, I) = .Cells(j, Choose(I, "B", "D", "C", "A", "N", "O", "L", "E", "Q", "R")) 'C'est à ce niveau que j'aimerai intégrer le libellé des colonnes à la place de leur emplacement
        Next I
        ' On recherche dans le fichier principal si ce matricule existe
        Set Cel = Ws.Columns("A").Find(what:=.Range("B" & j), LookIn:=xlValues, lookat:=xlWhole)
        If Not Cel Is Nothing Then
          ' Le matricule existe on remplace les données
            Cel.Resize(1, UBound(T1, 2)) = T1
        Else
          ' ' Le matricule n'existe on écrit les données à la fin
          Ws.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Resize(1, UBound(T1, 2)) = T1
        End If
      Next j
    End With
    .Close savechanges:=False   ' Ferme le fichier sans le sauvegarder
  End With
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
    MsgBox "terminé"
End Sub
13extraction-test.xlsx (35.83 Ko)
13outil-de-suivi-version-test.xlsm (71.99 Ko)
Le 26/01/2021 à 21:53

Bonjour,

Voici un essai en faisant abstraction du reste :

tlib = array("B", "D", "C", "A", "N", "O", "L", "E", "Q", "R") 'A MODIFIER BIEN SUR
With .Sheets(1)
    dl = .Range("B" & .Rows.Count).End(xlUp).Row 
    For k = 1 To UBound(T1, 2)
        libcherche = tlib(k - 1) 'CAR BASE 0
        col = application.match("*" & libcherche & "*", .rows(1), 0) 'position colonne via EQUIV (corresp. partielle)
        for i = 2 to dl
            T1(i - 1, k) = .Cells(i, col)
        next i
    Next k
End With

Cdlt,

Le 27/01/2021 à 15:00

Bonjour @3GB,

Merci pour le temps que tu m'accordes.

Pourrais-tu détailler (commenter) ton code car je ne parviens pas à l'adapter à mon cas surtout la ligne [Col = Application.Match("*" & libcherche & "*", .Rows(1), 0) 'position colonne via EQUIV (corresp. partielle)] qui est bloquante car Variable objet ou variable bloc With est non définie.

Le 27/01/2021 à 15:13

Bonjour MikyG,

Oui, je vais essayer de mieux commenter dessus :

sub a()

tlib = array("B", "D", "C", "A", "N", "O", "L", "E", "Q", "R") 'tableau contenant les noms des colonnes recherchées. L'ordre des noms de colonnes définit l'ordre de restitution dans la destination. Tableau de 10 éléments en base 0 (de 0 à 9).
With .Sheets(1) 'avec la feuille n° 1
    dl = .Range("B" & .Rows.Count).End(xlUp).Row  'dernière ligne en partant de la dernière cellule en colonne B
    For k = 1 To UBound(T1, 2) 'pour chaque colonne de l'array (tableau) T1
        libcherche = tlib(k - 1) 'libcherche recoit la valeur de l'item k-1 de tlib. On définit pour chaque colonne la colonne à cibler (par le nom correspondant en tlib).
        col = application.match("*" & libcherche & "*", .rows(1), 0) 'col vaut la position de libcherche (en correspondance partielle) dans la ligne 1. Donc si k = 1, libcherche = tlib(0) = "B", col = première position où "B" apparait en ligne 1.
        for i = 2 to dl 'pour les lignes 2 à dl (de la feuille)
            T1(i - 1, k) = .Cells(i, col) 'l'item à la ligne i-1 (décalage car boucle commence à 2) et la colonne k vaut la valeur de la cellule à la ligne i et la colonne col.
        next i
    Next k
End With

Application.match est l'équivalent de EQUIV sur Excel.

Je ne vois pas de raison d'un tel bug sur cette ligne donc peut-être que tu devrais avant toute chose définir les variables (dl&, k&, i&, tlib, libcherche$, col&) et poster le code entier.

Cdlt,

Le 27/01/2021 à 16:49

@3GB voici le code

Sub Import_détaillé()

Dim Fichier As String, K As Long, I As Integer, Col As Variant

Dim T1(1 To 1, 1 To 10) As String           ' Tableau qui va contenir les infos voulues d'une ligne
Dim Ws As Worksheet

  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
        ' Bloque le raffraichissement écran (on ne voit rien de ce qui se passe
  Set Ws = ThisWorkbook.Worksheets("Base")                     ' Plus facile à manipuler

  Application.ScreenUpdating = False 'Ouverture fenêtre de selection du fichier d'entrée
  Fichier = Application.GetOpenFilename
  Workbooks.Open Filename:=Fichier 'supprime le chemin
  Fichier = Dir(Fichier) 'Copie données fichier d'entrée vers fichier de sortie

  With Workbooks.Open(Fichier)                     ' On ouvre le fichier
    tlib = Array("B", "A", "C", "D", "E", "F", "G", "H", "J", "K") 'tableau contenant les noms des colonnes recherchées. L'ordre des noms de colonnes définit l'ordre de restitution dans la destination. Tableau de 10 éléments en base 0 (de 0 à 9).
    With .Sheets(1) 'avec la feuille n° 1
        dl = .Range("B" & .Rows.Count).End(xlUp).Row  'dernière ligne en partant de la dernière cellule en colonne B
        For K = 1 To UBound(T1, 2) 'pour chaque colonne de l'array (tableau) T1
        libcherche = tlib(K - 1) 'libcherche recoit la valeur de l'item k-1 de tlib. On définit pour chaque colonne la colonne à cibler (par le nom correspondant en tlib).
            Col = Application.Match("*" & libcherche & "*", .Rows(1), 0) 'col vaut la position de libcherche (en correspondance partielle) dans la ligne 1. Donc si k = 1, libcherche = tlib(0) = "B", col = première position où "B" apparait en ligne 1.
            For I = 2 To dl 'pour les lignes 2 à dl (de la feuille)
            T1(I - 1, K) = .Cells(I, Col) 'l'item à la ligne i-1 (décalage car boucle commence à 2) et la colonne k vaut la valeur de la cellule à la ligne i et la colonne col.
        Next I
    Next K
    End With
    .Close savechanges:=False   ' Ferme le fichier sans le sauvegarder
  End With
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
    MsgBox "terminé"
End Sub
Le 27/01/2021 à 17:07

Bizarre, bizarre...

Peux-tu essayer avec ceci à la place :

Col = Application.Match("*" & libcherche & "*", .range("1:1"), 0)

Et si tu cherches une correspondance exacte, plus besoin de

"*" & libcherche & "*"

Il suffit de mettre dans le match

libcherche
Le 27/01/2021 à 18:32

Je viens de faire le test et j'ai un incident suivant [Variable objet ou variable de bloc With non définie] sur cette ligne

[Col = Application.Match(libcherche, .Range("1:1"), 0)]

Sub Import_détaillé()

Dim Fichier As String, K As Long, I As Integer, Col As Range

Dim T1(1 To 1, 1 To 10) As String           ' Tableau qui va contenir les infos voulues d'une ligne
Dim Ws As Worksheet

  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
        ' Bloque le raffraichissement écran (on ne voit rien de ce qui se passe
  Set Ws = ThisWorkbook.Worksheets("Base")                     ' Plus facile à manipuler

  Application.ScreenUpdating = False 'Ouverture fenêtre de selection du fichier d'entrée
  Fichier = Application.GetOpenFilename
  Workbooks.Open Filename:=Fichier 'supprime le chemin
  Fichier = Dir(Fichier) 'Copie données fichier d'entrée vers fichier de sortie

  With Workbooks.Open(Fichier)                     ' On ouvre le fichier
    tlib = Array("B", "A", "C", "D", "E", "F", "G", "H", "J", "K") 'tableau contenant les noms des colonnes recherchées. L'ordre des noms de colonnes définit l'ordre de restitution dans la destination. Tableau de 10 éléments en base 0 (de 0 à 9).
    With .Sheets(1) 'avec la feuille n° 1
        dl = .Range("B" & .Rows.Count).End(xlUp).Row  'dernière ligne en partant de la dernière cellule en colonne B
        For K = 1 To UBound(T1, 2) 'pour chaque colonne de l'array (tableau) T1
        libcherche = tlib(K - 1) 'libcherche recoit la valeur de l'item k-1 de tlib. On définit pour chaque colonne la colonne à cibler (par le nom correspondant en tlib).
            Col = Application.Match(libcherche, .Range("1:1"), 0)
            For I = 2 To dl 'pour les lignes 2 à dl (de la feuille)
            T1(I - 1, K) = .Cells(I, Col) 'l'item à la ligne i-1 (décalage car boucle commence à 2) et la colonne k vaut la valeur de la cellule à la ligne i et la colonne col.
        Next I
    Next K
    End With
    .Close savechanges:=False   ' Ferme le fichier sans le sauvegarder
  End With
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
    MsgBox "terminé"
End Sub
Le 27/01/2021 à 20:50

C'est très étonnant. Et en essayant worksheetfunction.match ? Puis Application.worksheetfunction.match

Le 01/02/2021 à 14:11

Bonjour 3GB,

Même avec la fonction proposé cela ne fonctionne pas malheureusement

Le 01/02/2021 à 20:44

Bonjour,

C'est bon, j'ai trouvé l'erreur : il faut déclarer col en long Dim Col as long

Cdlt,

Le 04/02/2021 à 09:17

Bonjour,

Merci pour le temps accordé, je viens de faire la modification et j'ai un nouveau message qui s'affiche.

Cette fois-ci c'est à la ligne : "T1(I - 1, K) = .Cells(I, Col)"

J'ai l'indication qui me dit que "l'indice n'appartient pas à la sélection"

Le 04/02/2021 à 09:30

Bonjour Miky,

Très bien si l'étape est passée. Cette fois, c'est parce que tu as déclaré ton tableau T1 avec une seule ligne. Donc dès la ligne 3 de la boucle (ligne 2 du tableau), ça bloque.

Que cherches-tu à faire exactement ? Tu veux prendre toutes les lignes du fichier ? Les coller à la suite, remplacer celles qui existent en feuille de destination ?

Le 08/02/2021 à 12:06

Bonjour 3GB,

Désolé de mettre autant de temps à répondre, ce que je souhaite faire c'est mettre à jour mon tableau en prenant comme référence l'ensemble des lignes du tableau de mon fichier importé. Le but n'est pas de faire un simple copier/coller du tableau, je souhaite que les actions suivante se déroulent :

1. Pour les éléments qui n'ont pas eu de modification = Rien ne se passe dans le tableau final (La ligne n'est pas impacté par l'import)

2. Pour les éléments qui ont subi des modifications = La ligne du tableau se met à jour en récupérant chaque éléments mis à jour (Colonnes modifiées)

3. Pour les éléments nouveaux = La ligne se rajouté automatiquement à la fin du tableau

Sachant que les éléments sont identifiés par leur ID

Le 08/02/2021 à 15:50

Bonjour Miky,

D'accord, mais en quoi cette opération se distingue d'un simple copier/coller ? Si je ne me trompe pas, un copier/coller produirait exactement le même résultat ? A moins que la tableau de destination de contienne plus de lignes que le tableau d'import...

Cdlt,

Le 08/02/2021 à 17:10

3GB,

C'est plus pour une question de suivi, car la formule que j'avais précédemment me permettais d'identifier les éléments qui étaient supprimés et ainsi mettre en avant les différents delta. ça me permettait de faire des comparaisons entre le tableau avant import et après import

Rechercher des sujets similaires à "mettre jour tableau fichier externe variable"