J'ai 99 colonnes dans une table appelée tbl_raw . J'ai besoin de copier 96 de ces colonnes dans une autre table avec les mêmes noms d'en-tête exacts, mais elles sont réorganisées dans un ordre différent. Quelle est la manière la plus efficace d'y parvenir?
Le seul moyen que je connaissais était:
raw_data.Range ("tbl_raw [EMPLOYEE]"). Copier
processing_data.Range ("tbl_processed [EMPLOYEE]"). PasteSpecial
Cependant, cela prendrait beaucoup de code (96 * 2 = 192 lignes) et je n'étais pas sûr qu'il y ait un moyen plus efficace de le faire.
J'ai essayé d'utiliser https://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables , mais je n'ai pas trouvé un moyen de le faire avec cette information non plus.
Tout conseil serait grandement apprécié.
3 Réponses :
Voici un exemple de base de copie de toutes les colonnes, sauf certaines, d'une table à une autre:
Dim tbl1 As ListObject, tbl2 As ListObject
Dim h As ListColumn
Set tbl1 = ActiveSheet.ListObjects("Table1")
Set tbl2 = ActiveSheet.ListObjects("Table2")
'loop over the headers from the source table
For Each h In tbl1.ListColumns
'is the column name in the "excluded" list?
If IsError(Application.Match(h.Name, Array("col10", "col11"), 0)) Then
'ok to copy...
h.DataBodyRange.Copy tbl2.ListColumns(h.Name).DataBodyRange(1)
End If
Next h
Je pense que je suis cela. J'ai essayé d'exécuter ce code dans mon script, mais je n'ai pas pu le faire fonctionner. Je suppose que Application.Match est ce qui vérifie le nom de la colonne dans tbl1. Cependant, je ne vois pas où cela correspond à tbl2. Je ne comprends pas non plus la fonction «Array» ici. Dim h As ListColumn For Each h In tbl_raw.ListColumns If IsError (Application.Match (h.Name, Array ("col10", "col11"), 0)) Puis h.DataBodyRange.Copy tbl_imd.ListColumns (h .Name) .DataBodyRange (1) End If Next h Merci pour la réponse et toute aide supplémentaire serait appréciée!
Array () crée simplement un tableau à partir des arguments - il est utilisé car Match a besoin d'un tableau à comparer. Pour la correspondance du tableau 2, cela suppose que les noms de colonne sont identiques et tbl2.ListColumns (h.Name) .DataBodyRange (1) vous donne la destination de collage comme première cellule de la colonne Table2 avec en-tête h.Name
Je pense que l'op veut copier les colonnes où les en-têtes correspondent, pas celles qui ne le font pas. Il devrait également y avoir 96 éléments dans votre tableau de recherche afin qu'il soit plus facile d'utiliser tbl1.HeaderRowRange .
Je pense avoir compris ce que l'OP demandait, et mon tableau n'a besoin que des trois en-têtes "ne pas copier" ...
Évitez de copier des colonnes ListObject et utilisez un transfert de valeur direct.
Option Explicit
Sub raw2processed()
Dim lc As Long, mc As Variant, x As Variant
Dim raw_data As Worksheet, processed_data As Worksheet
Dim raw_tbl As ListObject, processed_tbl As ListObject
Set raw_data = Worksheets("raw")
Set processed_data = Worksheets("processed")
Set raw_tbl = raw_data.ListObjects("tbl_raw")
Set processed_tbl = processed_data.ListObjects("tbl_processed")
With processed_tbl
'clear target table
On Error Resume Next
.DataBodyRange.Clear
.Resize .Range.Resize(raw_tbl.ListRows.Count + 1, .ListColumns.Count)
On Error GoTo 0
'loop through target header and collect columns from raw_tbl
For lc = 1 To .ListColumns.Count
Debug.Print .HeaderRowRange(lc)
mc = Application.Match(.HeaderRowRange(lc), raw_tbl.HeaderRowRange, 0)
If Not IsError(mc) Then
x = raw_tbl.ListColumns(mc).DataBodyRange.Value
.ListColumns(lc).DataBodyRange = x
End If
Next lc
End With
End Sub
Cela fonctionne très bien! C'était très rapide aussi, beaucoup plus rapide que le copier-coller. J'ai un problème où les colonnes perdent leur format. J'ai des valeurs qui sont «001» et j'ai besoin qu'elles restent une chaîne, mais elles sont converties en nombre. Est-il possible de conserver le formatage de la valeur lors de l'utilisation de la méthode de transfert direct de valeur? Je n'ai même pas commencé à disséquer votre code pour le comprendre. J'espère pouvoir poser des questions de clarification après avoir essayé de le comprendre en détail! J'apprécie vraiment la réponse!
ForEach / For sont la magie de travailler avec des tableaux et des collections. Il existe des moyens de rendre le code suivant plus efficace, mais je pense que cela peut empêcher de comprendre ce qui se passe. Cela fait environ 6 mois environ depuis que j'ai travaillé pour la dernière fois avec VBA, mais je pense que cela devrait fonctionner. Je suggère de passer à travers et d'observer vos habitants pour voir ce qui se passe. S'il y a des problèmes avec les affectations de variables, il peut être nécessaire de changer un «Let» en un «Set». Le code suit:
'// PROBLEM:
'// Copy data from one list to a second list.
'// Both lists have the same column names and the same number of columns.
'// Copy data based on the column name.
'// Modify to return a custom source-destination association.
Private Function GetColumnTranslations(zLeftColumns As ListColumns, zRightColumns As ListColumns) As Variant
Dim zReturn(,) As Variant
ReDim zReturn(0 To zLeftColumns.Count As Long, 0 To 1 As Long)
Dim zReturnOffset As Long '// Specifies what index we are working at during our ForEach interations.
Dim zLeftVar As Variant
Dim zRightVar As Variant
ForEach zLeftVar in zLeftColumns
'// Go through each 'left' column to Find the first 'right' column that matches the name of the 'left' column.
'// Only the first 'right' column with a matching name will be used. Issue is solved with another ForEach, but beyond forum question's scope.
ForEach zRightVar in zRightColumns
If zLeftVar.Name = zRightVar.Name Then
'// Store the association and exit the nested ForEach.
Let zReturn(zReturnOffset, 0) = zLeftVar.Range.Column '// Source.
Let zReturn(zReturnOffset, 1) = zRightVar.Range.Column '// Destination.
Let zReturnOffset = zReturnOffset + 1
Exit ForEach
End If
Next zRightVar
Next zLeftVar
'// Assign return value.
Let GetColumnTranslations = zReturn
End Function
'// Take each source row and copy the value to a new destination row.
'// New rows are added to the end of the destination list.
Public Sub CopyList(zSourceList As ListObject, zDestinationList As ListObject)
Dim zColumnTranslations As Variant '// Will be 2-dimensional array.
Dim zTranslationVar As Variant '// Will be array of 2 elements.
Let zColumnTranslations = GetColumnTranslations(zSourceList.Columns, zDestinationList.Columns)
Dim zSourceRowVar As Variant '// Will translate to Range.
Dim zDestinationRow As Range
'// Every source row needs copied to a new row in destination.
ForEach zSourceRowVar in zSourceList.Rows
Set zDestinationRow = zDestinationList.Rows.Add.Range
ForEach zTranslationVar in zColumnTranslations
'// Value may copy formula.
Let zDestinationRow(0,zTranslationVar(1)).Value = zSourceRowVar(0,zTranslationVar(0)).Value
Next zTranslationVar
Next zSourceRowVar
End Sub
Désolé, je travaillais là-dessus un peu avant les articles ci-dessus.
C'est super! J'apprécie vraiment les commentaires et la ventilation. Je cherche vraiment à apprendre autant que je peux et cela m'aide énormément! Je vais aussi commencer à disséquer ce code et voir comment tout fonctionne. Comment diriez-vous que cette solution est relative à la solution @ user11198948 publiée?
Mon code n'efface pas la table de destination. Mon code ajoute des lignes au besoin. Je ne sais pas si le code de user11198948 est plus rapide (je n'ai pas Microsoft Office pour le moment), mais j'ai codé le mien pour permettre les modifications et ainsi vous comprendrez peut-être ce qui se passe (je suis autodidacte et je me souviens des difficultés). GetColumnTranslations vous permet de renvoyer un tableau d'associations personnalisées.
(J'apprends toujours ce site Web.) Pour copier la mise en forme, essayez d'ajouter une ligne sous le commentaire "La valeur peut copier la formule" => Soit zDestinationRow (0, zTranslationVar (1)). Style = zSourceRowVar (0, zTranslationVar (0) )).Style
Avez-vous un bon point de départ pour apprendre certaines des meilleures pratiques en VBA? Je vous vois Dim et ReDim et j'utilise une syntaxe que je ne connais pas du tout. J'ai essayé de parcourir et de rechercher des choses que je ne sais pas, ce qui est bien plus que ce que je pensais!
Eh bien, j'ai appris à utiliser Microsoft Office 2003 Access avec VBA, DAO et les fichiers d'aide qui l'accompagnaient. Je n'avais pas Internet. C'était en 2014, vers juin / juillet. Il m'a fallu 6 mois pour apprendre VBA à partir de zéro, ainsi que des tableaux, des rapports et des requêtes. J'étais en prison à ce moment-là. ... Tout mon codage est autodidacte - VBA et C #. L'ensemble de l'API pour Office est un peu foutu à mon avis.
En ce qui concerne un endroit pour apprendre les meilleures pratiques ... étudiez ce dont vous avez besoin en utilisant " docs.microsoft.com/en-us/office/vba/api "car c'est là que j'ai extrait les informations d'en haut. Commentez tout pour que vous, et les autres, puissiez lire rapidement ce qu'il fait, même si vous le répétez mot à mot. Gardez les choses simples. N'ayez pas peur de briser les choses quand cela devient compliqué (comme je l'ai fait dans la solution ci-dessus). Ce sont des choses que je fais.
Faites une boucle dans ListObject ("tbl_processed"). HeaderRowRange pour collecter chaque colonne correspondante dans ListObject ("tbl_raw"). HeaderRowRange. Il n'est pas clair si vous ajoutez ou remplacez les données dans tbl_processed.
Je remplace les données. La première chose que je fais est d'effacer le contenu du tableau à l'exception de la première ligne de données car je pourrais ajouter des colonnes avec des formules.
Je ne sais pas comment parvenir à votre solution. Je ne suis pas le meilleur avec VBA, mais j'ai réussi à créer des scripts et j'ai appris par la pratique. Serait-ce quelque chose comme: `Si tbl_raw.HeaderRowRange = tbl_processed.HeaderRowRange ALORS ... Je ne sais pas exactement comment aborder cela de manière dynamique.