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

**facteur** · 30/08/2022, 13h41

Bonjour
Avoir avoir créée une table et inséré une variable grace à l'aide ce forum à la rubrique SQL, je bascule dans la rubrique Excel pour poursuivre ce que j'essaie de faire

J'essaie d'insérer dans une variable d'une requete SQL, une liste de donnée issue d'une plage de données Excel.
Ci dessous ma procédure stockée.

Code SQL :

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
USE [Customer]
GO
/****** Object:  StoredProcedure [dbo].[MAJ_TableCAB]    Script Date: 30/08/2022 13:16:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** Object:  StoredProcedure [dbo].[MAJ_TableCAB]    Script Date: 30/08/2022 09:50:23 ******/
 
 
ALTER PROCEDURE [dbo].[MAJ_TableCAB] -- Creation de la procedure
 
@Nom varchar(50)
 
AS
BEGIN
SET NOCOUNT ON;
 
 
 
IF OBJECT_ID('tempdb..#MyTblCAB') IS NOT NULL DROP TABLE #MyTblCAB -- 
 
 
CREATE  TABLE #MyTblCAB (
    CAB varchar(30)
  )
 
 
----Inserer les données dans la table  "TblCAB"
INSERT INTO #MyTblCAB (CAB)
Values(@nom)
 
 
select*
from #MyTblCAB MyTblCAB
 
end

Ce que j'essaie de faire, c'est d'insérer dans la variable @nom, une liste de donnée issue d'une feuille Excel de la cellule A2 jusqu'à la dernier ligne vide de la colonne A exemple :

Code :

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

1
2
3
With Sheets("NumCAB")
        Derligne = .Range("A65000").End(xlUp).Row
        For i = 2 To Derligne

Cette variable serait je suppose avec la ligne

Code :

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

sql = "[Customer].dbo.MAJ_TableCAB" ' je nomme ma procédure stockée

Voici le code VBA

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
Sub MAJ_InsertionCAB()
 
 
  Dim cn As ADODB.Connection
  Dim cm As ADODB.Command
  Dim rs As ADODB.Recordset
  Dim sql As String
  Dim GenereCSTRING As String
  Dim i As Integer
  Dim lesCols As Range
  Dim Feuille As Worksheet
 
 
 
Set Feuille = Worksheets("test")
 
  'supprimer les annciennes données
Set lesCols = Feuille.Range("A1")
lesCols.EntireColumn.ClearContents
 
 
Set cn = New ADODB.Connection
 
 sql = "[Customer].dbo.MAJ_TableCAB" ' je nomme ma procédure stockée
 
 GenereCSTRING = "Provider=SQLOLEDB.1;Password=" & "MdP & ";Persist Security Info=True;User ID=" & "exploitation" & ";Initial Catalog=" & "NomServeur" & ";Data Source=" & "adresse IP" & " " '* SQLServer2008R2
 cn.Open GenereCSTRING 'J'ouvre ma connection.
 
 
 
P1 = Sheets("NumCAB").Range("A2")
 
  Set cm = New ADODB.Command
  With cm
    .ActiveConnection = cn
    .CommandText = sql
    .CommandTimeout = 0 'evite que le delai d'attente de requete expire (que la requte bugue)
    .CommandType = adCmdStoredProc '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("P1", adVarChar, adParamInput, 129, Sheets("NumCab").Range("A2")) 'CAB
 
 
  End With
 
 
  Set rs = cm.Execute()
 
 
 
  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

**Thumb down** · 30/08/2022, 15h00

Bonjour,
si ta variable s'appelle @nom pourquoi nomes tu le paramètre "P1" ?

**facteur** · 31/08/2022, 11h59

Thumb
Pour résumer, ce que j'essaie de faire c'est de créer une table sql server avec un seul champ et que ce champ récupère des données issues d'une colonne Excel.
L'import du tableau Excel sous sql ne me convient pas car ce que je souhaiterais ensuite c'est la possibilité de pouvoir supprimer les anciennes données pour de nouvelles données.

**Thumb down** · 31/08/2022, 12h22

Bonjour,
J'ai bien compris tout ça, mais ta variable s'appelle @Nom alors utilises la dans tes paramètres et n'utilises pas p2!

**facteur** · 31/08/2022, 15h15

Thum
Je ne sais pas comment remplacer la ligne

Code :

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

P1 = Sheets("NumCAB").Range("A2")

par la déclaration de la variable @nom car il faudrait que cette variable pointe vers la liste des données Excel; ce que ne parviens pas à réaliser

**Thumb down** · 05/09/2022, 12h19

regarde l'instruction selon choit TYPEBASE!

Code :

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

Select Case TYPEBASE

selon la valeur de TYPEDEBASE on ce connecte à SqlServeur2018 ou Excel!

tu n'a pas besoin de modifier la fonction GenereCSTRING car tu lui passe déjà les information en paramètres!

Code :

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

1
2
3
4
sub test()
CnServeur.Open = GenereCSTRING(SQLSERVER2018, "Archive" , "MdP", "exploitation","00.00.00.00")
CnExcel.Open GenereCSTRING(xls, ThisWorkbook.FullName)
end sub

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
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)
    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 SQLSERVER2018
              GenereCSTRING = "Provider=SQLOLEDB.1;Password=" & PassWord & ";Persist Security Info=True;User ID=" & User & ";Initial Catalog=" & Base & ";Data Source=" & Server
        Case Else
            GenereCSTRING = "PAS ASSEZ DE PARAMETRES RENSEIGNES !!!"      
    End Select
End Function

**facteur** · 05/09/2022, 12h45

Thum
C'est ce que j'ai cru comprendre lol
Donc pour resumer, dans ta ligne de code

Code :

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

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

On aurait pu mettre n'importe quelle autre expression que "TblCAB" , "moi", "moi" et "DESKTOP-NQG9COU\SQLEXPRESS"

**Thumb down** · 05/09/2022, 13h09

oui tu lui donnes le informations utile à la connexion à la base de données!

Code :

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

1
2
3
4
Base ="Archive" :User="MdP" :PassWord="exploitation" :Server="00.00.00.00")
CnServeur.Open = GenereCSTRING(SQLSERVER2018, Base ,User,PassWord,Server)
Base = "C:\MyRep\MyFichier.Xlsx"
CnExcel.Open GenereCSTRING(xls,Base )

en revanche il faut rétablir le code de GenereCSTRING au moins comme j'ais fais au poste #25!

**Thumb down** · 31/08/2022, 15h43

tout simplement

Code :

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

.Parameters.Append .CreateParameter("@nom", adVarChar, adParamInput, 129, Sheets("NumCab").Range("A2")) 'CAB

EDITE:

Perso voila ce que j'ai fait de mon coté!

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
Public Enum MyAdo
    ACCESS97 
    ACCESS2000 
    ACCESS2012 
    ODBC 
    ORACLE
    SQLSERVER2005
    SQLServer2008R2 
    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(SQLSERVER2005, "TblCAB", "MOI", "MOI", "DESKTOP-NQG9COU\SQLEXPRESS")
CnExcel.Open GenereCSTRING(xls, ThisWorkbook.FullName)
 
Sql = "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")
 
Sql = "dbo.MAJ_TableCAB" ' je nomme ma procédure stockée
  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
 
 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
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 SQLServer2008R2
        GenereCSTRING = "Provider=SQLNCLI;Server=" & Server & ";Database=" & Base & ";UID=" & User & ";PWD=" & PassWord & ";"
    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** · 01/09/2022, 15h37

Bonjour Thum
Wouai wouai ..

je vais regarder ton code de plus prés car là je suis un peu largué

**Thumb down** · 01/09/2022, 18h41

En fait y'a pas grand chose à comprendre !

Si tu regardes la fonction GenereCSTRING tu y retrouveras la méthode de connexion que tu utilises habituellement !

Ce qui compte c'est ce qui ce trouve dans la sub test! GenereCSTRING retourne le connecstring à la base de données choisi ! Dans notre ça sql serveur et Excel.

**facteur** · 02/09/2022, 09h52

Bonjour Thum
J'ai inséré ton code dans ma feuille excel et voici l'ereur que j'ai lorsque j'esséaie d'exécuter le code vba

**facteur** · 02/09/2022, 10h03

Thum
Lorsque je regarde ton code vba, je ne vois pas la ligne de code ou j'insère mes donnes issues de Excel dans ma requete sql

**facteur** · 02/09/2022, 11h51

Thumb
J'ai pu exécuter ton Code. C'est moi qui avait mal orthographier ton code lorsque je l'ai inséré dans ma feuille Excel.

Je regarde cela et je reviens vers toi

**facteur** · 02/09/2022, 12h04

Thum
Apré&s avoir exceuter ton code, voici le message d'erreur que j'ai à la ligne

Code :

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

With CnExcel.Execute("Select [CAB] From [TblCAB$]")

**Thumb down** · 05/09/2022, 13h42

tu utilise le mêmes informations pour te connecter Via Sql Manager?
car le message dit que tu n'as pas les droits! de plus ce n'est plus un un table Temporaire #MyTblCAB!

j'ai fais ce test là et ça a fonctionné!

Code :

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

1
2
3
4
5
6
7
8
9
10
Sub test()
Sql = "if OBJECT_ID('customer.dbo.#MyTblCAB') IS NOT NULL   DROP TABLE customer.dbo.#MyTblCAB"
Sql = Sql & vbCrLf & "CREATE TABLE customer.dbo.#MyTblCAB (CAB varchar(30))"
Sql = Sql & vbCrLf & "INSERT INTO customer.dbo.#MyTblCAB VALUES"
Sql = Sql & vbCrLf & "('CA330624175DE'),('CM918473853DE'),('CC090971735DE'),('6A21054526032'),('6A21054526018'),('6A21054526025'),('9V31125509666')"
Set CnServeur = CreateObject("AdoDb.Connection")
CnServeur.Open = GenereCSTRING(SQLSERVER2018, "TblCAB", "MOI", "MOI", "DESKTOP-NQG9COU\SQLEXPRESS")
CnServeur.Execute Sql
CnServeur.Close
End Sub

**facteur** · 06/09/2022, 08h18

Bonjour Thumb
effectivement , tu as raison , je n'utilisais pas les mêmes informations pour me connecter à Sql Manager.

ET là cà fonctionne..

Mais j'ai encore quelques questions pour en finir avec cette discussion lol.

Question 1)
La table temporaire local que je crée #MyTblCAB est une table où je n'insère qu'un seul champ (CAB). Ce sont les données de ce champ qui seront amenées à être supprimées et remplacées par des nouvelles données issues de Excel.
Et cette table local est visible uniquement sur la scession où je me connecte. Je me pose alors la question si je dois créer une table temporaire local ou une table définitif qui sera supprimer et créer à chaque fois que je lancerais ce code VBA
Pour toi, tu ferais quoi ?

Question 2)
Que la table soit temporaire ou définitif, j'essaye de transformer le champ CAB en clef primaire afin que mes requêtes avec ce champ soit optimisées.
Mais je n'y parviens car à cause des violations de clefs primaire.

Voici ce que j'ai fait :

Code :

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

1
2
3
sql = "IF OBJECT_ID('customer.dbo.MyTblCAB') IS NOT NULL DROP TABLE customer.dbo.MyTblCAB" & vbCrLf & _
     '"CREATE  TABLE customer.dbo.MyTblCAB (CAB varchar(30)," & vbCrLf & _
     '"Primary Key(CAB))"

et voila ce que cela me donne avec le print

Code SQL :

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

1
2
3
4
IF OBJECT_ID('customer.dbo.MyTblCAB') IS NOT NULL DROP TABLE customer.dbo.MyTblCAB
CREATE  TABLE customer.dbo.MyTblCAB (CAB varchar(30))
INSERT INTO customer.dbo.MyTblCAB  VALUES 
('CA330624175DE'),('CM918473853DE'),('CC090971735DE'),('6A21054526032'),('6A21054526018'),('6A21054526025'),(''),(''),(''),(''),(''),(''),(''),(''),(''),(''),(''),(''),('')

le problème vient de cette redondance de

(''),(''),(''),(''),(''),(''),(''),(''),(''),(''),(''),(''),('')

Comment faire pour les supprimer ??
ci dessous le message d'erreur

**Thumb down** · 02/09/2022, 11h48

Bonjour,
T'occupe de mon code!

Pour ce qui te concerne il te faut remplace Parameters.Append .CreateParameter("P1", adVarCharPart
Parameters.Append .CreateParameter("@nom", adVarChardans ton code actuel du poste #1 et ça fonctionnera