Variable SQL issu d'une liste de données Excel

**Thumb down** · 02/09/2022, 12h16

En même temps je ne connais pas le nom de ta feuille excel !

Il faut mettre le nom de ta [feuille$] entre crochets [] et rajouter un $ à la fin de sont nom!
Le nom du champ c'est l'entête de colonne {le texte qui ce trouve sur la première ligne de la colonne}
"Select [champ] From [Feuille1$]"

**facteur** · 02/09/2022, 14h48

Thumb
Apres quelques corrections pour etre en adéquation avec mon fichier et mes contraintes , ton code marche à merveille

J'ai quelques question à te poser concernant ce code VBA pour le maitriser et le déployer sur d'autres fichier

1)J'ai inhibé les Ligne 36, 53 et 62. D ailleurs pourquoi ces lignes de code. Elles sont inutiles dans mas cas non ?
2) Sur la la ligne de code 24 où est inséré la requete sql , j'ai rajouté une ligne qui supprimer la table afin de pourvoir la créer avec de nouveaux enregistrements
3) j'ai supprimer les lignes de codes 48 et 60 . Nul besoin de déclarer des variables dans mon cas
4) j'ai supprimer les la partie allant de 55 à 60 car elle est en doublon

Donc voici ton code vba avec mes changement

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
Public Enum MyAdo
    ACCESS97
    ACCESS2000
    ACCESS2012
    ODBC
    ORACLE
    SQLSERVER2005
    SQLSERVER2018
    SQLite
    SQLite3
    CSV
    xls
    MySQL
End Enum


Sub Test()
Dim CnServeur As Object, CnExcel As Object: Set CnServeur = CreateObject("AdoDb.Connection"): Set CnExcel = CreateObject("AdoDb.Connection")
CnServeur.Open = GenereCSTRING(SQLSERVER2018, "TblCAB", "MOI", "MOI", "DESKTOP-NQG9COU\SQLEXPRESS")
CnExcel.Open GenereCSTRING(xls, ThisWorkbook.FullName)
 
 Range("A:C").ClearContents
 
sql = "IF OBJECT_ID('tempdb..#MyTblCAB') IS NOT NULL DROP TABLE #MyTblCAB" & vbCrLf & _
       "CREATE  TABLE #MyTblCAB (CAB varchar(30))" & vbCrLf
With CnExcel.Execute("Select [CAB] From [TblCAB$]")
   If Not .EOF Then
        t = "('" & .GetString(, , "", "'),('", "")
        t = Left(t, Len(t) - 3)
        sql = sql & "INSERT INTO #MyTblCAB  VALUES " & vbCrLf
        sql = sql & t & vbCrLf
   End If
   .Close
End With
CnServeur.Execute sql
 'ActiveCell.CopyFromRecordset CnServeur.Execute("Select * from #MyTblCAB")
 

 Debug.Print sql
 
 
sql = "[Customer].dbo.MAJ_TableCAB" ' je nomme ma procédure stockée MAJ_TableCAB
  With CreateObject("ADODB.Command")
    .ActiveConnection = CnServeur
    .CommandText = sql
    .CommandTimeout = 0 'evite que le delai d'attente de requete expire (que la requte bugue)
    .CommandType = 4 'invoque une procédure stockée. Le serveur SQL doit savoir où aller chercher la commande, c'est le pourquoi de la ligne qui spécifie le CommandType
   .Parameters.Append .CreateParameter("@nom", 200, 1, 129, "AA") 'CAB
 
 Debug.Print sql

 Set rs = .Execute
 'ActiveCell.CopyFromRecordset rs
  End With

   With CreateObject("ADODB.Command")
    .ActiveConnection = CnServeur
    .CommandText = sql
    .CommandTimeout = 0 'evite que le delai d'attente de requete expire (que la requte bugue)
    .CommandType = 4 'invoque une procédure stockée. Le serveur SQL doit savoir où aller chercher la commande, c'est le pourquoi de la ligne qui spécifie le CommandType
   .Parameters.Append .CreateParameter("@nom", 200, 1, 129, "BB") 'CAB
 
' ActiveCell.CopyFromRecordset .Execute
  End With
  


With ThisWorkbook.Sheets("test")
     For i = 0 To rs.Fields.Count - 1 'On place le nom des champs sur la ligne 4 de ma feuille Excel
               .Range("A1").Offset(0, i) = rs(i).Name
     Next
        .Range("A2").CopyFromRecordset rs 'la on copie le résultat de la requête
        
        
End With
  
  
End Sub
Private Function GenereCSTRING(TYPEBASE As MyAdo, Base As String, Optional User As String, Optional PassWord As String, Optional Server As String, Optional AvecTitre As Boolean = True)
'Permet de générer le Cornec String
'1 - ACCESS 97
'2 - ACCESS 2000
'3 - ACCESS 2012
'4 - ODBC
'5 - ORACLE
'6 - SQL SERVER 2005
'7 - SQL Server 2008 R2
'8 - SQLite
'9 - SQLite3
If Trim("" & Base) = "" Then Base = Base
Select Case TYPEBASE
    Case xls
            GenereCSTRING = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Base & ";Extended Properties=""Excel 12.0;HDR=" & Array("No", "YES")(Abs(AvecTitre)) & ";"""
            
    Case ACCESS97
        GenereCSTRING = "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=" & Base
        
    Case ACCESS2000
        GenereCSTRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Base & ";Persist Security Info=False"
        
    Case ACCESS2012
        GenereCSTRING = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Base & ";"
        
    Case MySQL
    GenereCSTRING = " DRIVER={MySQL ODBC 5.1 Driver};SERVER=" & Server & ";UID=" & User & ";DATABASE=" & Base & ";Password=" & PassWord
    
    Case ODBC
        GenereCSTRING = "Provider=MSDASQL.1;Password=" & PassWord & ";Persist Security Info=True;User ID=" & User & ";Data Source=" & Base
        
    Case ORACLE
        GenereCSTRING = "Provider=OraOLEDB.Oracle.1;Password=" & PassWord & ";Persist Security Info=True;User ID=" & User & ";Data Source=" & Base
        
    Case SQLSERVER2005
        'GenereCSTRING = "Driver={SQL Server};SERVER=" & Server & ";DATABASE=" & Base & ";UID=" & User & ";Pwd=" & PassWord & ";"
        GenereCSTRING = "Provider=SQLOLEDB.1;Password=" & PassWord & ";Persist Security Info=True;User ID=" & User & ";Initial Catalog=" & Base & ";Data Source=" & Server
        
    Case SQLSERVER2018
        GenereCSTRING = "Provider=SQLOLEDB.1;Password=" & "exploitation" & ";Persist Security Info=True;User ID=" & "MdP" & ";Initial Catalog=" & "Archive" & ";Data Source=" & "00.00.00.00" & " " '* SQLServer2018
        
    Case SQLite
        GenereCSTRING = "Provider=OleSQLite.SQLiteSource.3; Data Source=" & Base
        GenereCSTRING = "Driver={SQLite ODBC (UTF-8) Driver};Database=" & Base & ";StepAPI=;Timeout="
        
    Case SQLite3
        GenereCSTRING = "Driver={SQLite3 ODBC Driver};Database=" & Base & ";LongNames=0;Timeout=4000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;"
    Case CSV
        GenereCSTRING = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Server & ";Extended Properties=""Text;HDR=" & Array("No", "YES")(Abs(AvecTitre)) & ";FMT=Delimited;"""
        
    Case Else
        GenereCSTRING = "PAS ASSEZ DE PARAMETRES RENSEIGNES !!!"
        
End Select

End Function

**facteur** · 02/09/2022, 15h16

Thum encore une question.
Pourrais tu m'expliquer cette ligne de code ci-dessous

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CnServeur.Open = GenereCSTRING(SQLSERVER2018, "TblCAB", "MOI", "MOI", "DESKTOP-NQG9COU\SQLEXPRESS")

**Thumb down** · 02/09/2022, 15h41

Envoyé par facteur

Thum encore une question.
Pourrais tu m'expliquer cette ligne de code ci-dessous

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CnServeur.Open = GenereCSTRING(SQLSERVER2018, "TblCAB", "MOI", "MOI", "DESKTOP-NQG9COU\SQLEXPRESS")

cette ligne tu la connais bien elle ce trouve en ligne 87 du poste #6 c'est le code que tu utilisais précédament!
GenereCSTRING = "Provider=SQLOLEDB.1;Password=" & PassWord & ";Persist Security Info=True;User ID=" & User & ";Initial Catalog=" & Base & ";Data Source=" & Server
ce type de connexion est apparu avec la version 2005 ce qui explique le nom que je lui avais donné à l'époque et que tu as renommé en 2018 ce qui n'est pas choquant!

voici la même chose pour effectuer un connexon sur un fichier Excel lige 68 du même poste

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

GenereCSTRING = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Base & ";Extended Properties=""Excel 12.0;HDR=" & Array("No", "YES")(Abs(AvecTitre)) & ";"""

en fait à l'origine GenereCSTRING est une fonction qui permet de générer un conctionString pour n'importe quelle base de données Access, SQL Serveur, Oracle Autre...

j'ai donc défini un tableau de constantes sou forme d'énumérateur, cela permet de numéroter automatiquement des constante en leur donnant un nom!

Code énumérateur :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
Public Enum MyAdo
    ACCESS97
    ACCESS2000
    ACCESS2012
    ODBC
    ORACLE
    SQLSERVER2005
    SQLSERVER2018
    SQLite
    SQLite3
    CSV
    xls
    MySQL End Enum

ici nous avons
TYPEBASE As MyAdo qui nous permet de sélectionner dans l'énumérateur MyADO la bas de données ou le fichier Excel dans notre ca
User pour sql serceur , Oracle etc.
PassWord pour sql serceur , Oracle etc.
Server pour sql serceur , Oracle etc.
AvecTitre pour Excel et CSV

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Function GenereCSTRING(TYPEBASE As MyAdo, Base AsString, Optional User AsString, Optional PassWord AsString, Optional Server AsString, Optional AvecTitre AsBoolean = True)

**facteur** · 05/09/2022, 08h41

Bonjour Thumb
Une question
Donc tu ouvres le serveur sql à partir d excel, par cette la ligne de code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

GenereCSTRING(TYPEBASE As MyAdo, Base As String, Optional User As String, Optional PassWord As String, Optional Server As String, Optional AvecTitre As Boolean = True)

Ce qui me chagrine , c'est ta ligne de code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CnServeur.Open = GenereCSTRING(SQLSERVER2018, "TblCAB", "MOI", "MOI", "DESKTOP-NQG9COU\SQLEXPRESS")

et est ce que la remplacer par

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CnServeur.Open = GenereCSTRING(SQLSERVER2018, ThisWorkbook.FullName)

n'est pas mieux ?

**Thumb down** · 06/09/2022, 14h14

la méthode Execute de l'objet ADODB.CONNECTION execute une requête mais retourne également le résultat de celle-ci sous forme de RecordSet

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Set RecordSet= CnExcel.Execute(SQL)

il ne suffit pas de récupérer le résultat d'une requête dans un recordSet encor faut-il savoir si elle retourne une ou plusieurs valeurs avant de la traiter

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

if not RecordSet.eof then 'Vérifie si le récorset retourne des valeurs!

si le recordSet retourne des valeurs on récupère la totalitée des valeurs sou forme de texte

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

txt=RecordSet.getString

fais ce test

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Sub test()
Dim CnEXCEL As Object
Set CnEXCEL = CreateObject("Adodb.Connection"): CnEXCEL.Open GenereCSTRING(xls, ThisWorkbook.FullName)
Dim rs As ADODB.Recordset
Set rs = CnEXCEL.Execute("Select * From [NumCAB$A1:B10]")
Debug.Print rs.GetString
Debug.Print "Tous"
Debug.Print CnEXCEL.Execute("Select * From [NumCAB$A1:B10]").GetString
Debug.Print "Deux lignes"
Debug.Print CnEXCEL.Execute("Select * From [NumCAB$A1:B10]").GetString(, 2)
Debug.Print "Deux lignes avec sépareteur de colonnes"
Debug.Print CnEXCEL.Execute("Select * From [NumCAB$A1:B10]").GetString(, 2, "[Colonne]")
Debug.Print "Deux lignes avec sépareteur de colonnes et séparateur ligne"
Debug.Print CnEXCEL.Execute("Select * From [NumCAB$A1:B10]").GetString(, 2, "[Colonne]", "[Ligne]")
End Sub

**facteur** · 06/09/2022, 14h46

Merci pour tout Thumb.

Je ferme cette discussion

**Thumb down** · 05/09/2022, 11h17

Oui c'est bien ce que je t'ai dit cette ligne ne concerne pas sql serveur mais excel !
Donc regardes bien mon code et tu verras la différence !

**facteur** · 05/09/2022, 11h26

Thum
reprenons à zero

voici ta ligne de code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CnServeur.Open = GenereCSTRING(SQLSERVER2018, "TblCAB", "MOI", "MOI", "DESKTOP-NQG9COU\SQLEXPRESS")

puis je la remplacer par
celle-ci

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CnServeur.Open = GenereCSTRING(SQLSERVER2018, ThisWorkbook.FullName)

**Thumb down** · 05/09/2022, 11h28

Non en replace rien par rien!

ThisWorkbook.FullName c'est le fichier Excel sur lequel tu travailles, c'est pas ta base de données SQL Serveur !

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
GenereCSTRING(xls, ThisWorkbook.FullName)
 
Dim CnServeur As Object, CnExcel As Object: Set CnServeur = CreateObject("AdoDb.Connection"): Set CnExcel = CreateObject("AdoDb.Connection")
 
CnServeur.Open = GenereCSTRING(SQLSERVER2018, "TblCAB", "MOI", "MOI", "DESKTOP-NQG9COU\SQLEXPRESS")
 CnExcel.Open GenereCSTRING(xls, ThisWorkbook.FullName)

**facteur** · 05/09/2022, 11h37

Thumb
Désolé d'insister
Je comprend la ligne de code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CnServeur.Open = GenereCSTRING(SQLSERVER2018, ThisWorkbook.FullName)

mais c'est cette ligne de code ci dessous que je ne parviens à pas à analyser

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CnServeur.Open = GenereCSTRING(SQLSERVER2018, "TblCAB", "MOI", "MOI", "DESKTOP-NQG9COU\SQLEXPRESS")

"TblCAB" est le nom du fichier mais si le nom du fichier change quel impact ?
et à quoi correspond les 2 "MOI" ?

Variable SQL issu d'une liste de données Excel

Macros et VBA Excel

Vue hybride

Discussions similaires

Partager

Partager