none
appel de procédure stockée SQL-Server de Excel (via VB) RRS feed

  • Question

  • Salut,

    j'ai un programme dans Excel qui appelle une procédure stockée paramétrée sous SQL-Server qui va chercher des données dans différentes tables. Mon programme va ensuite récupérer ces données pour les insérer dans mon fichier Excel.

    Ca marchait très bien jusqu'il y a quelques jours. Maintenant quand je lance mon programme, il appelle bien la procédure mais il ne bascule plus vers Excel une fois la procédure Sql-Server terminée. Le plus bizarre, c'est que ce programme plante seulement sur mon PC. Si j'essaie sur un autre poste du réseau, il n'y a aucun problème...

    Pouvez-vous m'aider?

    Merci d'avance

    PS: j'ai vidé le cache internet et j'ai supprimé tous mes fichiers temp

     

    CreAO

    mardi 19 septembre 2006 11:48

Réponses

  • Avant tout, il faut que tu crée un fichier .udl où tu va paramétrer le serveur de ta base de données sql-server

    il faut aussi créer un .dsn de la base de données

    Pour récupérer les données il faut que tu crée une procédure stockée dans sql-server qui va remplir une table des données que tu veut...  Tu peux aussi créer une vue pour harmoniser le tout.

    Voici un exemple de programme.

    Ce programme va ouvrir une connexion à ta base de données puis va exécuter ta procédure stockée et enfin va récupérer tes données à partir d'une table ou d'une vue et va les copier dans excel...

         Dim Rs As ADODB.Recordset

        'initialisation de la connexion vers la base de données Surveillance sous SQL-Server
        InitConSurveillance
        conlocale = False

        'lancement de la procédure paramétrée dans SQL-Server
        ExecuteCmdAction "spExtractBilanMensuelNEB", Annee, Mois
       
        'Récupération des données de la vue "vwBilanMensuelNEB"
        ExecuteCmd Rs, "vwBilanMensuelNEB", adCmdTable, Null


        'Création d'un nouveau classeur excel
        Set Wb = Workbooks.Add
       
        'Affectation et nommage de la feuille active
        Set Ws = Wb.ActiveSheet
        Ws.Name = "NEB"
       
        'Remplissage de la feuille avec les données extraites
        AddQuery Ws, Rs, "A2", "BilanMensuelNEB"
       
        Set Rs = Nothing
       
        RowEnd = Ws.QueryTables("BilanMensuelNEB").ResultRange.Rows.Count + 3

        'Fermeture de la connexion vers la base Surveillance
        CloseConSurveillance

    Cette macro (ou programme) nécessite un module :

    Option Explicit
    Const cstTimeOut As Long = 120 * 60 'en secondes

    Public ConSurveillance As ADODB.Connection
    Public conlocale As Boolean


    Public Sub AddQuery(Ws As Worksheet, Rs As ADODB.Recordset, Destination As String, Nom As String)

        With Ws.QueryTables.Add(Rs, Ws.Range(Destination))
            .Name = Nom
            .FieldNames = True 'insertion des libellés de colonnes dans la plage de résultats
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = True
            .SaveData = True
            .AdjustColumnWidth = True 'ajustement automatique de la largeur des colonnes
            .RefreshPeriod = 0
            .PreserveColumnInfo = True 'conservation des données précédentes
            .Refresh BackgroundQuery:=False
        End With
    End Sub

    Public Sub AddQuerysanstitres(Ws As Worksheet, Rs As ADODB.Recordset, Destination As String, Nom As String)

        With Ws.QueryTables.Add(Rs, Ws.Range(Destination))
            .Name = Nom
            .FieldNames = False
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = True
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .Refresh BackgroundQuery:=False
        End With
    End Sub

    '########################################################################################
    '#                                                                                      #
    '#                                       ADO                                            #
    '#                                                                                      #
    '########################################################################################

    Public Sub ExecuteCmd(pRs As ADODB.Recordset, _
                          pName As String, _
                          CmdType As ADODB.CommandTypeEnum, _
                          ParamArray TabParam() As Variant)
    'Exécution d'une commande paramétrée renvoyant des enregistrements

        Dim Cmd As ADODB.Command
       
        Set pRs = New ADODB.Recordset

        Set Cmd = New ADODB.Command
       
        Set Cmd.ActiveConnection = ConSurveillance
       
        Cmd.CommandType = CmdType
        Cmd.CommandText = pName
        Cmd.CommandTimeout = cstTimeOut
       
        If UBound(TabParam) = 0 Then
            Set pRs = Cmd.Execute()
        Else
            Set pRs = Cmd.Execute(, TabParam)
        End If
       
        Set Cmd = Nothing
    End Sub

    Public Function ExecuteCmdAction(pName As String, ParamArray TabParam() As Variant) As Long
    'Exécution d'une commande paramétrée ne renvoyant pas d'enregistrements
    '(requête action)

        Dim Cmd As ADODB.Command
       
        Set Cmd = New ADODB.Command
       
        Set Cmd.ActiveConnection = ConSurveillance
       
        Cmd.CommandType = adCmdStoredProc
        Cmd.CommandText = pName
        Cmd.CommandTimeout = cstTimeOut
       
        If UBound(TabParam) = 0 Then
            Cmd.Execute ExecuteCmdAction
        Else
            Cmd.Execute ExecuteCmdAction, TabParam
        End If
       
        Set Cmd = Nothing
    End Function

    Public Function ExecuteCmdActionSansParam(pName As String) As Long
    'Exécution d'une commande sans paramètres et ne renvoyant pas d'enregistrements
    '(requête action)
    'Renvoie le nombre d'enregistrements affectés

        Dim Cmd As ADODB.Command
       
        Set Cmd = New ADODB.Command
           
        Set Cmd.ActiveConnection = ConSurveillance
       
        Cmd.CommandType = adCmdStoredProc
        Cmd.CommandText = pName
        Cmd.CommandTimeout = cstTimeOut
       
        Cmd.Execute ExecuteCmdActionSansParam
        Set Cmd = Nothing
    End Function

    Public Function ExecuteCmdCount(pName As String, ParamArray TabParam() As Variant) As Long
    'Exécution d'une commande paramétrée renvoyant un nombre d'enregistrements
    '(requête de type SELECT COUNT(*)...)

        Dim Cmd As ADODB.Command
        Dim Rs As ADODB.Recordset
       
        Set Rs = New ADODB.Recordset

        Set Cmd = New ADODB.Command
       
        Set Cmd.ActiveConnection = ConSurveillance
       
        Cmd.CommandType = adCmdStoredProc
        Cmd.CommandText = pName
       
        Cmd.CommandTimeout = cstTimeOut
       
        If UBound(TabParam) = 0 And IsNull(TabParam(0)) Then
            Set Rs = Cmd.Execute()
        Else
            Set Rs = Cmd.Execute(, TabParam)
        End If
       
        Rs.MoveFirst
       
        ExecuteCmdCount = CLng(Rs![Nb])
       
        Rs.Close
        Set Rs = Nothing
        Set Cmd = Nothing
    End Function

    Public Sub InitConSurveillance()
    'initialise la connexion vers la base SQL-Server
        Set ConSurveillance = New ADODB.Connection
        ConSurveillance.ConnectionString = "File Name=LienBaseSurveillance.udl" 'chemin du fichier de liaison
        ConSurveillance.ConnectionString = ConSurveillance.ConnectionString & ";Password='';" 'dans le cas ou la connexion à la base 'nécessite un mot de passe

        ConSurveillance.CommandTimeout = cstTimeOut
        ConSurveillance.Open
    End Sub

    Public Sub CloseConSurveillance()
        ConSurveillance.Close
        Set ConSurveillance = Nothing
    End Sub

     


    mardi 17 octobre 2006 08:24

Toutes les réponses

  • je cherche comment excuter un programme dans Excel qui appelle une procédure stockée paramétrée sous SQL-Server qui va chercher des données dans différentes tables.Et ensuite le programme va ensuite récupérer ces données pour les insérer dans mon fichier Excel.

    merci et a bientot

    lundi 16 octobre 2006 19:40
  • Avant tout, il faut que tu crée un fichier .udl où tu va paramétrer le serveur de ta base de données sql-server

    il faut aussi créer un .dsn de la base de données

    Pour récupérer les données il faut que tu crée une procédure stockée dans sql-server qui va remplir une table des données que tu veut...  Tu peux aussi créer une vue pour harmoniser le tout.

    Voici un exemple de programme.

    Ce programme va ouvrir une connexion à ta base de données puis va exécuter ta procédure stockée et enfin va récupérer tes données à partir d'une table ou d'une vue et va les copier dans excel...

         Dim Rs As ADODB.Recordset

        'initialisation de la connexion vers la base de données Surveillance sous SQL-Server
        InitConSurveillance
        conlocale = False

        'lancement de la procédure paramétrée dans SQL-Server
        ExecuteCmdAction "spExtractBilanMensuelNEB", Annee, Mois
       
        'Récupération des données de la vue "vwBilanMensuelNEB"
        ExecuteCmd Rs, "vwBilanMensuelNEB", adCmdTable, Null


        'Création d'un nouveau classeur excel
        Set Wb = Workbooks.Add
       
        'Affectation et nommage de la feuille active
        Set Ws = Wb.ActiveSheet
        Ws.Name = "NEB"
       
        'Remplissage de la feuille avec les données extraites
        AddQuery Ws, Rs, "A2", "BilanMensuelNEB"
       
        Set Rs = Nothing
       
        RowEnd = Ws.QueryTables("BilanMensuelNEB").ResultRange.Rows.Count + 3

        'Fermeture de la connexion vers la base Surveillance
        CloseConSurveillance

    Cette macro (ou programme) nécessite un module :

    Option Explicit
    Const cstTimeOut As Long = 120 * 60 'en secondes

    Public ConSurveillance As ADODB.Connection
    Public conlocale As Boolean


    Public Sub AddQuery(Ws As Worksheet, Rs As ADODB.Recordset, Destination As String, Nom As String)

        With Ws.QueryTables.Add(Rs, Ws.Range(Destination))
            .Name = Nom
            .FieldNames = True 'insertion des libellés de colonnes dans la plage de résultats
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = True
            .SaveData = True
            .AdjustColumnWidth = True 'ajustement automatique de la largeur des colonnes
            .RefreshPeriod = 0
            .PreserveColumnInfo = True 'conservation des données précédentes
            .Refresh BackgroundQuery:=False
        End With
    End Sub

    Public Sub AddQuerysanstitres(Ws As Worksheet, Rs As ADODB.Recordset, Destination As String, Nom As String)

        With Ws.QueryTables.Add(Rs, Ws.Range(Destination))
            .Name = Nom
            .FieldNames = False
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = True
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .Refresh BackgroundQuery:=False
        End With
    End Sub

    '########################################################################################
    '#                                                                                      #
    '#                                       ADO                                            #
    '#                                                                                      #
    '########################################################################################

    Public Sub ExecuteCmd(pRs As ADODB.Recordset, _
                          pName As String, _
                          CmdType As ADODB.CommandTypeEnum, _
                          ParamArray TabParam() As Variant)
    'Exécution d'une commande paramétrée renvoyant des enregistrements

        Dim Cmd As ADODB.Command
       
        Set pRs = New ADODB.Recordset

        Set Cmd = New ADODB.Command
       
        Set Cmd.ActiveConnection = ConSurveillance
       
        Cmd.CommandType = CmdType
        Cmd.CommandText = pName
        Cmd.CommandTimeout = cstTimeOut
       
        If UBound(TabParam) = 0 Then
            Set pRs = Cmd.Execute()
        Else
            Set pRs = Cmd.Execute(, TabParam)
        End If
       
        Set Cmd = Nothing
    End Sub

    Public Function ExecuteCmdAction(pName As String, ParamArray TabParam() As Variant) As Long
    'Exécution d'une commande paramétrée ne renvoyant pas d'enregistrements
    '(requête action)

        Dim Cmd As ADODB.Command
       
        Set Cmd = New ADODB.Command
       
        Set Cmd.ActiveConnection = ConSurveillance
       
        Cmd.CommandType = adCmdStoredProc
        Cmd.CommandText = pName
        Cmd.CommandTimeout = cstTimeOut
       
        If UBound(TabParam) = 0 Then
            Cmd.Execute ExecuteCmdAction
        Else
            Cmd.Execute ExecuteCmdAction, TabParam
        End If
       
        Set Cmd = Nothing
    End Function

    Public Function ExecuteCmdActionSansParam(pName As String) As Long
    'Exécution d'une commande sans paramètres et ne renvoyant pas d'enregistrements
    '(requête action)
    'Renvoie le nombre d'enregistrements affectés

        Dim Cmd As ADODB.Command
       
        Set Cmd = New ADODB.Command
           
        Set Cmd.ActiveConnection = ConSurveillance
       
        Cmd.CommandType = adCmdStoredProc
        Cmd.CommandText = pName
        Cmd.CommandTimeout = cstTimeOut
       
        Cmd.Execute ExecuteCmdActionSansParam
        Set Cmd = Nothing
    End Function

    Public Function ExecuteCmdCount(pName As String, ParamArray TabParam() As Variant) As Long
    'Exécution d'une commande paramétrée renvoyant un nombre d'enregistrements
    '(requête de type SELECT COUNT(*)...)

        Dim Cmd As ADODB.Command
        Dim Rs As ADODB.Recordset
       
        Set Rs = New ADODB.Recordset

        Set Cmd = New ADODB.Command
       
        Set Cmd.ActiveConnection = ConSurveillance
       
        Cmd.CommandType = adCmdStoredProc
        Cmd.CommandText = pName
       
        Cmd.CommandTimeout = cstTimeOut
       
        If UBound(TabParam) = 0 And IsNull(TabParam(0)) Then
            Set Rs = Cmd.Execute()
        Else
            Set Rs = Cmd.Execute(, TabParam)
        End If
       
        Rs.MoveFirst
       
        ExecuteCmdCount = CLng(Rs![Nb])
       
        Rs.Close
        Set Rs = Nothing
        Set Cmd = Nothing
    End Function

    Public Sub InitConSurveillance()
    'initialise la connexion vers la base SQL-Server
        Set ConSurveillance = New ADODB.Connection
        ConSurveillance.ConnectionString = "File Name=LienBaseSurveillance.udl" 'chemin du fichier de liaison
        ConSurveillance.ConnectionString = ConSurveillance.ConnectionString & ";Password='';" 'dans le cas ou la connexion à la base 'nécessite un mot de passe

        ConSurveillance.CommandTimeout = cstTimeOut
        ConSurveillance.Open
    End Sub

    Public Sub CloseConSurveillance()
        ConSurveillance.Close
        Set ConSurveillance = Nothing
    End Sub

     


    mardi 17 octobre 2006 08:24
  • Merci c genial je vais tester de suite

     

    jeudi 16 novembre 2006 11:33