Comment utiliser Excel pour lire et écrire une base de données externe

Excel peut pratiquement tout faire ; s'il faut lui faire tout faire, c'est une autre affaire. Bien que la feuille de calcul soit très puissante pour manipuler les données, elle n'est pas très performante pour stocker des données normalisées. Exploiter Excel à une base de données relationnelle comme SQL Server augmente la puissance de l'application.

Pour commencer, vous aurez besoin de MS Access ou de la version plus stable et gratuite – SQL Server Exprimer. On suppose que le lecteur a le ruban Excel Developer affiché et qu'il est familiarisé avec l'éditeur VBA et le langage de requête structuré (SQL). Cet article utilise SQL Server chaînes de connexion. Pour MS-Access, consultez Google.

Bien qu'Excel ait ses propres routines intégrées pour obtenir des informations à partir de SQL Server dans (disons) un tableau croisé dynamique, notre exemple donnera plus de flexibilité dans la sélection des données.

Chaîne de connexion

J'utiliserai une base de données privée ; insérez vos propres informations de pilote à la place des miennes dans la sous-routine ConnectDatabase. On utilise alors connDB comme canal de communication vers notre base de données - dans mon cas, pour renvoyer les résultats d'une procédure stockée. Vous pouvez utiliser des instructions SQL plus standard telles que "Select * from …"

Ordre des travaux

Tout d'abord, nous allons charger les choix de combo-box à partir de SQL Server À l'ouverture du classeur, grâce à une macro Auto_open, les données sont enregistrées dans la feuille « ComboData ». Que le serveur soit dans le cloud ou local, le démarrage d'Excel sera instantané, à condition que la base de données soit accessible depuis le poste de travail.

Ensuite, nous allons extraire les données filtrées de la base de données et les déposer dans Excel, colonnes F à K.

L'interface

Le mien a des listes déroulantes pour filtrer les informations de la base de données. Le Rôle combo box déclenche une recherche pour remplir le tableau de droite.

Renommez "Sheet1" en "Main". Ajoutez au moins une zone de liste déroulante.

Le code

Public connDB As New ADODB.Connection
Public rstNew As New ADODB.Recordset
Public rs As New ADODB.Recordset
Public strSQL As String
Public nID As Integer

Sub auto_Open()
    Call PopulateComboData     'kicks off the first process  on Open
End Sub

Sub PopulateComboData()
    Sheets("ComboData").Range("A3:C100").ClearContents
    Call ConnectDatabase      'use the ConnectDatabase routine
    strSQL = "Select DeptID, Department, Phase from tblDept Order by Department"
    Set rs = connDB.Execute(strSQL)
    ActiveSheet.Range("A3").CopyFromRecordset rs      'copies the recordset in bulk
End Sub

Sub ReadData()
    intRole = Sheets("main").Range("D7")
    Sheets("Main").Range("F4:L100").ClearContents
    Call ConnectDatabase
    strSQL = "EXEC DBTest " & intRole    'calls a stored proc with parameter
    Set rs = connDB.Execute(strSQL)
    ActiveSheet.Range("F4").CopyFromRecordset rs      'copies the recordset in bulk
End Sub

Sub ConnectDatabase()
    On Error GoTo ErrConnect
    If connDB.State = 1 Then connDB.Close     'closes connection if already open
    strServer = "197.200.28.164" 
    strDBase = "Qcrew_sql"
    strUser = "joesoap_sql"
    strPWD = "frU6ra!@"
    If strPWD > "" Then 
        strConnectionstring = "DRIVER={SQL Server};Server=" & strServer & _
        ";Database=" & strDBase & ";Uid=" & strUser & ";Pwd=" & strPWD & _
        ";Connection Timeout=30;"
    Else        'Use windows authentication
        strConnectionstring = "DRIVER={SQL Server};SERVER=" & strServer & _
        ";Trusted_Connection=yes;DATABASE=" & strDBase
    End If
    connDB.Open strConnectionstring
Exit Sub
ErrConnect:
    MsgBox Err.Description
End Sub

Formatez le contrôle de la zone de liste déroulante pour lire les feuilles "ComboData". Cliquez ensuite avec le bouton droit sur la zone de liste déroulante pour lui affecter la sous-procédure ReadData. Lorsqu'un élément est sélectionné dans la liste déroulante, écrivez sa clé dans la feuille "Main", cellule D7. Le code VBA utilisera cette clé comme filtre (voir intRole, ci-dessus).

Références à la bibliothèque DLL

Dans la fenêtre de code, utilisez Outils > Références pour référencer la bibliothèque Microsoft Active X Data Objects. Excel pourra ainsi utiliser les objets ADODB déclarés dans le code.

La sous-routine ReadData ci-dessus utilise une structure de données relationnelle, illustrée ci-dessous, qui est difficile à réaliser dans Excel seul.

D'autres modifications de données pourraient déclencher une écriture différée dans la base de données, avec l'instruction SQL Update appropriée suivie de connDB.execute(strSQL).

Enfin, protégez votre code contre la visualisation ou la modification :  Outils>Propriétés>Protection.

Gérer les problèmes Excel :

De temps en temps, en particulier lorsqu'il contient des programmes complexes, Excel peut se bloquer et ne pas récupérer correctement. En cas de endommagé xlsx Pour résoudre la plupart des problèmes de fichiers, disposer d'un outil de récupération efficace permettra de les résoudre.

Introduction de l'auteur:

Felix Hooker est un expert en récupération de données dans DataNumen, Inc., qui est le leader mondial des technologies de récupération de données, y compris réparation rar erreur et produits logiciels de récupération sql. Pour plus d'informations, visitez www.datanumen.com