Microsoft 365 Charger nombre variable de tableaux Query avec VBA

bluesky12000 · 2 Décembre 2021

Bonsoir à tous,

Dans mon classeur, j'ai plusieurs tableaux Query qui sont en connexion et que je veux insérer dans mon deuxième onglet.

J'ai créé une macro via l'enregistreur pour transformer toutes ces connexions en tableau.
Cela fonctionne très bien (voir bouton vert)
J'arrive aussi à toutes les supprimer (voir bouton rouge)

Mon idée est de charger et de supprimer que le nombre de tableaux indiqué en cellule B1 à la place de For i = 1 to 4

Dans ma macro pour ajouter les tableaux (module 2 - Test) j'ai essayé de changer le nom du tableau en variable, mais cela n'a pas l'air de fonctionner.

VB:

Sub Test()

Dim i
Dim myLastRow As Long
myLastRow = Range("B1000").End(xlUp).Row

For i = 1 To 1
 
 With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table"" & i & ""_client"";Extended Properties=""""" _
        , Destination:=Range(myLastRow)).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [""Table"" & i & ""_client]""")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table" & i & "_client"
        .Refresh BackgroundQuery:=False
    End With

Next

End Sub

Je suis un peu perdu avec mes connaissances limitées. Est ce que quelqu'un saurait comment faire?

Merci pour votre aide et bonne soirée à tous,

Wayki · 2 Décembre 2021

Bonsoir,
Essayez ceci :

VB:

Sub Test()

Dim i, ws As Worksheet
Set ws = Worksheets("client")
Dim myLastRow As Long
myLastRow = ws.Range("B1000").End(xlUp).Row

For i = 1 To ws.Range("B1")
 
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Table" & i & "_client;Extended Properties=""""" _
        , Destination:=Range("B" & myLastRow + 1)).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table" & i & "_client]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table" & i & "_client"
        .Refresh BackgroundQuery:=False
    End With
myLastRow = ws.Range("B1000").End(xlUp).Row

Next i

End Sub

Vous avez beaucoup de connections, vous pouvez les enlever comme ceci :

Code:

sub effacer()
For Each cn In ActiveWorkbook.Connections
       cn.Delete
Next cn
End sub

A +

bluesky12000 · 2 Décembre 2021

Mille mercis Wayki ça marche très bien
Je n'ai pas trop compris la logique de quand mettre les " avant ou après le texte par contre.

Dans le reste de mon vrai fichier j'ai des formules comme :

VB:

ws.Range("J55").Formula = "=SUMIF(Jour1_client[Pays]:Jour8_client[Pays],""Lituanie"",Jour1_client[Prix total HT]:Jour8_client[Prix total HT])"

Comment dois-je l'écrire sur le prince que 8 = i ?
Est-ce comme cela ?

VB:

ws.Range("J55").Formula = "=SUMIF(Jour1_client[Pays]:Jour"& i &"_client[Pays],""Lituanie"",Jour1_client[Prix total HT]:Jour" & i &"_client[Prix total HT])"

Merci beaucoup

Wayki · 2 Décembre 2021

C'est assez complexe les "
En gros et pour résumer à l'extrême, le 1er " entre dans une zone de texte, le 2eme en sort. Meme moi je galère par moment
Pour votre formule ça devrait marcher comme ceci oui,
Attention si c'est la même boucle que vos tableaux, I s'arrête à la valeur de B1.
A +

bluesky12000 · 3 Décembre 2021

Merci encore une fois Wayki,

Malheureusement ma formule ne fonctionne pas avec le i , je pense avoir tout essayé.

Du coup, J'ai essayé de remplacer le nom du tableau par un String faisant référence à une cellule qui à le nom du plus grand tableau mais rien ne fonctionne

Wayki · 3 Décembre 2021

Essayez ça :

ws.Range("J55").Formula = "=SUMIF(Jour"& i &"_client[Pays],""Lituanie"",Jour" & i &"_client[Prix total HT])"

On ne peut pas utiliser des : avec des tableaux structurés comme si c'était un range classique.
A +

bluesky12000 · 3 Décembre 2021

Bonjour Wayki,

La dernière écriture ne fonctionne pas non plus.

Du coup j'ai essayé :

If i = 1

ws.Range("J55").Formula = "=SUMIF(Jour1_client[Pays],""Lituanie"",Jour1_client[Prix total HT])"

End if

If i = 2

ws.Range("J55").Formula = "=SUMIF(Jour1_client[Pays]:Jour2_client[Pays],""Lituanie"",Jour1_client[Prix total HT]:Jour2_client[Prix total HT])"

Enf if

If i = 3

ws.Range("J55").Formula = "=SUMIF(Jour1_client[Pays]:Jour3_client[Pays],""Lituanie"",Jour1_client[Prix total HT]:Jour3_client[Prix total HT])"

End if

Cela fonctionne bien mais rallonge l'écriture.

Merci encore et bonne journée

Microsoft 365 Charger nombre variable de tableaux Query avec VBA

bluesky12000

XLDnaute Junior

Pièces jointes

Wayki

XLDnaute Impliqué

bluesky12000

XLDnaute Junior

Wayki

XLDnaute Impliqué

bluesky12000

XLDnaute Junior

Wayki

XLDnaute Impliqué

bluesky12000

XLDnaute Junior

Discussions similaires