VBA - "Scripting.Dictionary" vs VLOOKUP

[jeromecools]

Bonjour à tous,

J'ai un fichier de 30.000 Lignes, voir plus.
Avant de mettre à jour mon fichier via une connection SQL, je fait 2 copies.
Une copie identique, et aussi une liste avec ITEM sans doublons, et 3 valeurs manuel qui n'existe pas dans la table sql.

Mes macros récupèrent ces valeurs dans ma table d'origine.

Seulement voila, je pensais que la première serait plus rapide grace au dictonary, mais la version avec vlookup me semble plus rapide... que pensez-vous?

Sub CopierLesSelectionAssortiment2()
    Sheets("TRAVAIL").Select
    Dim ChoixL, ChoixMs, ChoixMn As Object
    Dim ItemIdBackup, ItemId As Range
    
    Set ChoixL = CreateObject("Scripting.Dictionary")
    Set ChoixMs = CreateObject("Scripting.Dictionary")
    Set ChoixMn = CreateObject("Scripting.Dictionary")
    
' DB_ITEM est ma liste sans doublons (ID, Col1, Col2, Col3)
    For Each ItemIdBackup In Sheets("DB_ITEM").Range("A2:" & Range("A2").End(xlDown).Address)
        ChoixL.Item(ItemIdBackup.Value) = ItemIdBackup.Offset(0, 1).Value
        ChoixMs.Item(ItemIdBackup.Value) = ItemIdBackup.Offset(0, 2).Value
        ChoixMn.Item(ItemIdBackup.Value) = ItemIdBackup.Offset(0, 3).Value
    Next ItemIdBackup
    
    Application.ScreenUpdating = False
    
    Range("TableTravail[ChoixL]").Interior.Color = RGB(229, 224, 236)
    Range("TableTravail[ChoixMs]").Interior.Color = RGB(253, 233, 217)
    Range("TableTravail[ChoixMn]").Interior.Color = RGB(219, 238, 242)
    
' TableTravail[Item] est la colone ID en référence à celle du sheet DB_ITEM
    For Each ItemId In Range("TableTravail[Item]")
       If ChoixL.Exists(ItemId.Value) Then
            If ChoixL.Item(ItemId.Value) <> ItemId.Offset(0, 29).Value Then
                 ItemId.Offset(0, 29).Value = ChoixL.Item(ItemId.Value)
            End If
       Else
            ItemId.Offset(0, 29).Interior.ColorIndex = 20
       End If
       If ChoixMs.Exists(ItemId.Value) Then
            If ChoixMs.Item(ItemId.Value) <> ItemId.Offset(0, 30).Value Then
                 Cells(ItemId.Row, 31).Value = ChoixMs.Item(ItemId.Value)
            End If
       Else
            Cells(ItemId.Row, 31).Interior.ColorIndex = 20
       End If
       If ChoixMn.Exists(ItemId.Value) Then
            If ChoixMn.Item(ItemId.Value) <> ItemId.Offset(0, 31).Value Then
                 Cells(ItemId.Row, 32).Value = ChoixMn.Item(ItemId.Value)
            End If
       Else
            Cells(ItemId.Row, 32).Interior.ColorIndex = 20
       End If
    Next ItemId
    Application.ScreenUpdating = True
End Sub

Sub CopierLesSelectionAssortiment()
' ETAPE 3 : Copier les choix assort
    Sheets("DB_ITEM").Visible = True
    Sheets("TRAVAIL").Select
    Range("TableTravail[ChoixL]").Select
    Range("TableTravail[ChoixL]").Formula = "=T(VLOOKUP([Item],DB_ITEM!A:D,2,FALSE))"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
    Application.CutCopyMode = False
    Range("TableTravail[ChoixMs]").Select
    Range("TableTravail[ChoixMs]").Formula = "=T(VLOOKUP([Item],DB_ITEM!A:D,3,FALSE))"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
    Application.CutCopyMode = False
    Range("TableTravail[ChoixMn]").Select
    Range("TableTravail[ChoixMn]").Formula = "=T(VLOOKUP([Item],DB_ITEM!A:D,4,FALSE))"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
    Application.CutCopyMode = False
'pour vider le cache
    VidePressePapier
'pour revenir au menu de mon programme
    RetourMenu ("Les choix d'assortiment sont copiés depuis le backup item")
End Sub

Merci pour vos coneilles/remarques.

 

[jeromecools]

Re : VBA - "Scripting.Dictionary" vs VLOOKUP

Encore une chose,

Mon bute premier est de controler les changements dans une base de donnée sous SQL SERVER.
L'extract dans une sheet, et la comparaison me semble le mieux. De plus j'y ajoute des commentaires.

Le truc dommage après un update de SQL, c'est que les colonnes ajoutée manuellement, et surtout les celuule ne sont pas bien liée, et que les données que j'ajoute sont alors décalée si il y a des lignes en plus ou en moins dans la data base.

Il exite peut-être une autre solution pour m'éviter tout le travail de copie/coller entre les tables...

J'espère être assez claire, en tout cas, déjà merci pour vos conseils.

 

[Compte Supprimé 979]

Re : VBA - "Scripting.Dictionary" vs VLOOKUP

Bonsoir

Mon bute premier est de controler les changements dans une base de donnée sous SQL SERVER.

Alors pour ne pas utiliser des requêtes !?

L'extract dans une sheet, et la comparaison me semble le mieux. De plus j'y ajoute des commentaires.

Même chose avec une requête création de table suite à une requête comparative !

Heuuuuu là je pense qu'il faut que tu bosses ton SQL

A+

 

[jeromecools]

Re : VBA - "Scripting.Dictionary" vs VLOOKUP

Salut Bruno,
Je me suis mal exprimé.
Je n'ai pas le choix en fait, les données sont en lecture seul. Et je ne sais pas créer une nouvelle table.
Mon objectif est de gérer les résultats pour pouvoir faire des rapports.
Comme il manque certains champ dans la base, je dois passer par une Excel.
Est-ce plus claire?

La table que je récupère est dans le style :
CLE_UNIQUE (10000 cléf différente et unique)
ITEM_PRODUIT (Il peut y avoir 10 item identique)
ITEM_ANNEE (Pour chaque item, j'ai une année)
NOM_PRODUIT (le nom du produit)
MonChoix1 (par exemple ma selection) (c'est une colonne en plus dans excel qui n'existe pas dans les datas)

Lorsque d'un refesch, la colonne "monChoix1" n'est plus en raccord avec les bonnes lignes.

Mon process est donc de faire un backup avant de mettre à jour, puis de recopier "monchoix1". Ceci me permet aussi de voir les changement dans la base.