maurogsc-mauro gamberini

maurogsc.eu
mauro gamberini

home ==> Excel: come fare per ==> Database: Excel e SQL Server

Database: Excel e SQL Server - VBA

NOTA. La soluzione ed il codice sono forniti *così come sono* e l’autore declina ogni responsabilità per eventuali problemi causati dal codice o dalla soluzione proposta se usati impropriamente. Create e utilizzate una copia del file per le vostre prove, *prima* di utilizzare il codice o la soluzione in files importanti.

0 - Contenuto

Premessa e contesto
Cosa modificare nel vostro contesto
Importare da SQL Server in Excel
Creare un nuovo record in SQL Server da Excel
Creare un nuovo record in SQL Server da Excel utilizzando parametri e stored procedure
Modificare un record in SQL Server da Excel
Modificare un record in SQL Server da Excel utilizzando parametri e stored procedure
Eliminare un record in SQL Server da Excel
Eliminare un record in SQL Server da Excel utilizzando parametri e stored procedure
Recuperare record tramite una View di SQL Server
Importare da SQL Server in Excel utilizzando stored procedure
Importare da SQL Server in Excel utilizzando parametri e stored procedure

1 - Premessa e contesto. (top)
Tutti gli esempi utilizzano ADO (ActiveX Data Objects) e non neccessitano dei riferimenti alle librerie ADO. Si riferiscono inoltre ad un database SQL Server Express 2008 R2 chiamato ProvaDB1 e che contiene la tabella Tabella1. Database e file si trovano dsullo stesso pc. Eventuali settaggi relativi a SQL Server Express esulano dal contesto dell'articolo. Nella Tabella1 sono presenti tre campi:

ID - di tipo int
Nome - di tipo nvarchar(50)
Comune - di tipo nvarchar(50)

Codice, database, tabella, campi e tipo di dati, hanno solo carattere di esempio.

2 - Cosa modificare nel vostro contesto. (top)
Modificate i riferimenti all'istanza di SQL Server, al database, alla tabella e ai campi con i vostri. Eventuali suggerimenti per stringhe di connessione, sintassi SQL e altro qui:

ADO Tutorial (in inglese) - http://www.w3schools.com/sql/default.asp
Stringhe di connessione (in inglese) - http://www.connectionstrings.com/
Documentazione di SQL Server (in italiano) - http://msdn.microsoft.com/it-it/library/ms130214.aspx

In Internet sono reperibili molti siti con tutorial e documentazione(anche in italiano) su ADO, SQL e SQL Server. A voi una ricerca con Bing o Google. E' possibile chiedere aiuto nel forum di Microsoft Excel in italiano qui: http://social.answers.microsoft.com/Forums/it-IT/officeexcelit/threads

3 - Importare da SQL Server a Excel. (top)
Codice che importa tutti i dati della Tabella1 del database ProvaDB1 nel Foglio1 del file di Excel a partire dalla cella A2. Codice da copia/incollare in un modulo standard:

Public Sub mImportaDati()

On Error GoTo RigaErrore

    Dim cn As Object
    Dim rs As Object
    Dim sh As Worksheet
    Dim sSQL As String

    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    Set sh = Worksheets("Foglio1")
    
    sSQL = "SELECT * FROM Tabella1"
    
    With cn
        .CursorLocation = 1
        .Open "Provider=SQLNCLI10;" & _
            "Server=MAURO10\SQLEXPRESS;" & _
            "Database=ProvaDB1;" & _
            "Trusted_Connection=yes;"
    End With
    
    With rs
        .CursorLocation = 1
        .Open sSQL, cn, 1, 3, 1
    End With
    
    With sh
        .Cells.Clear
        .Range("A2").CopyFromRecordset rs
    End With
    
RigaChiusura:
    If rs.State = 1 Then
        rs.Close
    End If
    
    If cn.State = 1 Then
        cn.Close
    End If

    Set rs = Nothing
    Set cn = Nothing
    Set sh = Nothing
    Exit Sub

RigaErrore:
    MsgBox Err.Number & vbNewLine & Err.Description
    Resume RigaChiusura

End Sub

Alcune stringhe SQL alternative per l'esempio qui sopra:

sSQL = "SELECT ID, Comune FROM Tabella1" - Importa solo i campi ID e Comune
sSQL = "SELECT * FROM Tabella1 WHERE Comune = 'Bologna'" - Importa i record nei quali il campo Comune è uguale a Bologna

Per importare record utilizzando stored procedure vedere il punto 11.
Per importare record utilizzando stored procedure e parametri vedere il punto 12.

4 - Creare un nuovo record in SQL Server da Excel. (top)
Codice che esporta i dati delle celle A1, B1, C1 del Foglio1 da Excel a SQL Server nella Tabella1 creando un nuovo recore. Codice da copia/incollare in un modulo standard:

Public Sub mEsportaDati()

On Error GoTo RigaErrore

    Dim cn As Object
    Dim sh As Worksheet
    Dim sSQL As String

    Set cn = CreateObject("ADODB.Connection")
    Set sh = Worksheets("Foglio1")
    
    With sh
        sSQL = "INSERT INTO Tabella1 " & _
            "(ID, Nome, Comune) " & _
            " VALUES (" & _
            "'" & .Range("A1").Value & "', " & _
            "'" & .Range("B1").Value & "', " & _
            "'" & .Range("C1").Value & "'" & _
            ")"
    End With
    
    With cn
        .CursorLocation = 1
        .Open "Provider=SQLNCLI10;" & _
            "Server=MAURO10\SQLEXPRESS;" & _
            "Database=ProvaDB1;" & _
            "Trusted_Connection=yes;"
        .Execute sSQL, , 1
    End With
    
RigaChiusura:
    If cn.State = 1 Then
        cn.Close
    End If

    Set cn = Nothing
    Set sh = Nothing
    Exit Sub

RigaErrore:
    MsgBox Err.Number & vbNewLine & Err.Description
    Resume RigaChiusura

End Sub

5 - Creare un nuovo record in SQL Server da Excel utilizzando parametri e stored procedure. (top)
L'utilizzo di stored procedures e parametri è sempre consigliato per questioni di sicurezza.

1. Questo lo statement che sul database ha creato la stored procedure che utilizzerò:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE INS_DATI
   @ID INT,
   @Nome VARCHAR(50),
   @Comune VARCHAR(50)
AS
BEGIN
   SET NOCOUNT ON;
  
   INSERT INTO Tabella1
   (ID,
   Nome,
   Comune)
   VALUES (@ID,
   @Nome, @Comune);
END
GO

2. Questo il codice che da Excel utilizzerà la stored procedure, per creare un nuovo record nella Tabella1. I dati da esportare nel del database sono in Foglio1, celle A1, B1, C1. Codice da copia/incollare in un modulo standard:

Public Sub mEsportaDatiParSP()

On Error GoTo RigaErrore

    Dim cn As Object
    Dim cmd As Object
    Dim sh As Worksheet

    Set cn = CreateObject("ADODB.Connection")
    Set cmd = CreateObject("ADODB.Command")
    Set sh = Worksheets("Foglio1")
    
    With cn
        .CursorLocation = 1
        .Open "Provider=SQLNCLI10;" & _
            "Server=MAURO10\SQLEXPRESS;" & _
            "Database=ProvaDB1;" & _
            "Trusted_Connection=yes;"
    End With
    
    Set cmd.ActiveConnection = cn
    
    With cmd
        .CommandText = "INS_DATI"
        .CommandType = 4
        .Parameters("@ID").Value = sh.Range("A1").Value
        .Parameters("@Nome").Value = sh.Range("B1").Value
        .Parameters("@Comune").Value = sh.Range("C1").Value
        .Execute
    End With
    
RigaChiusura:
    If cn.State = 1 Then
        cn.Close
    End If
    
    Set cmd = Nothing
    Set cn = Nothing
    Set sh = Nothing
    Exit Sub

RigaErrore:
    MsgBox Err.Number & vbNewLine & Err.Description
    Resume RigaChiusura

End Sub

6 - Modificare un record in SQL Server da Excel. (top)
Codice che modifica un record sulla Tabella1 nel database SQL Server utilizzando i dati delle celle A1, C1 del Foglio1. Verrà modificato il Comune del record relativo all'ID di cella A1. Codice da copia/incollare in un modulo standard:

Public Sub mModificaRecord()

On Error GoTo RigaErrore

    Dim cn As Object
    Dim sh As Worksheet
    Dim sSQL As String

    Set cn = CreateObject("ADODB.Connection")
    Set sh = Worksheets("Foglio1")

    With sh
        sSQL = "UPDATE Tabella1 " & _
            "Set Comune = " & "'" & .Range("C1").Value & "'" & _
            " WHERE ID = " & "'" & .Range("A1").Value & "'"
    End With
    
    With cn
        .CursorLocation = 1
        .Open "Provider=SQLNCLI10;" & _
            "Server=MAURO10\SQLEXPRESS;" & _
            "Database=ProvaDB1;" & _
            "Trusted_Connection=yes;"
        .Execute sSQL, , 1
    End With
    
RigaChiusura:
    If cn.State = 1 Then
        cn.Close
    End If

    Set cn = Nothing
    Set sh = Nothing
    Exit Sub

RigaErrore:
    MsgBox Err.Number & vbNewLine & Err.Description
    Resume RigaChiusura

End Sub

7 - Modificare un record in SQL Server da Excel utilizzando parametri e stored procedure. (top)
L'utilizzo di stored procedures e parametri è sempre consigliato per questioni di sicurezza.

1. Questo lo statement che sul database ha creato la stored procedure che utilizzerò:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE MOD_DATI
   @ID INT,
   @Comune VARCHAR(50)
AS
BEGIN
   SET NOCOUNT ON;
  
   UPDATE Tabella1
   Set Comune = @Comune
   WHERE ID = @ID
END
GO

2. Questo il codice che da Excel utilizzerà la stored procedure, modificando nella Tabella1 del database il campo Comune nel record con l'ID specificato in cella A1 e con il valore da modificare in cella C1 Codice da copia/incollare in un modulo standard:

Public Sub mModificaRecordParSP()

On Error GoTo RigaErrore

    Dim cn As Object
    Dim sh As Worksheet
    Dim cmd As Object

    Set cn = CreateObject("ADODB.Connection")
    Set sh = Worksheets("Foglio1")
    Set cmd = CreateObject("ADODB.Command")
    
    With cn
        .CursorLocation = 1
        .Open "Provider=SQLNCLI10;" & _
            "Server=MAURO10\SQLEXPRESS;" & _
            "Database=ProvaDB1;" & _
            "Trusted_Connection=yes;"
    End With
    
    Set cmd.ActiveConnection = cn
    
    With cmd
        .CommandText = "MOD_DATI"
        .CommandType = 4
        .Parameters("@ID").Value = sh.Range("A1").Value
        .Parameters("@Comune").Value = sh.Range("C1").Value
        .Execute
    End With
    
RigaChiusura:
    If cn.State = 1 Then
        cn.Close
    End If

    Set cmd = Nothing
    Set cn = Nothing
    Set sh = Nothing
    Exit Sub

RigaErrore:
    MsgBox Err.Number & vbNewLine & Err.Description
    Resume RigaChiusura

End Sub

8 - Eliminare un record in SQL Server da Excel. (top)
Codice che cancella un record sulla Tabella1 nel database SQL Server utilizzando il dati della cella A1 del Foglio1. Verrà eliminato il record relativo all'ID di cella A1. Codice da copia/incollare in un modulo standard:

Public Sub mEliminaaRecord()

On Error GoTo RigaErrore

    Dim cn As Object
    Dim sh As Worksheet
    Dim sSQL As String

    Set cn = CreateObject("ADODB.Connection")
    Set sh = Worksheets("Foglio1")

    With sh
        sSQL = "DELETE FROM Tabella1 " & _
            " WHERE ID = " & "'" & .Range("A1").Value & "'"
    End With
    
    With cn
        .CursorLocation = 1
        .Open "Provider=SQLNCLI10;" & _
            "Server=MAURO10\SQLEXPRESS;" & _
            "Database=ProvaDB1;" & _
            "Trusted_Connection=yes;"
        .Execute sSQL, , 1
    End With
    
RigaChiusura:
    If cn.State = 1 Then
        cn.Close
    End If

    Set cn = Nothing
    Set sh = Nothing
    Exit Sub

RigaErrore:
    MsgBox Err.Number & vbNewLine & Err.Description
    Resume RigaChiusura

End Sub

9 - Eliminare un record in SQL Server da Excel utilizzando parametri e stored procedure. (top)
L'utilizzo di stored procedures e parametri è sempre consigliato per questioni di sicurezza.

1. Questo lo statement che sul database ha creato la stored procedure che utilizzerò:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE DEL_DATI
   @ID INT
AS
BEGIN
   SET NOCOUNT ON;
  
   DELETE FROM Tabella1
   WHERE ID = @ID
END
GO

2. Questo il codice che da Excel utilizzerà la stored procedure, modificando nella Tabella1 del database il campo Comune nel record con l'ID specificato in cella A1. Codice da copia/incollare in un modulo standard:

Public Sub mEliminaRecordParSP()

On Error GoTo RigaErrore

    Dim cn As Object
    Dim sh As Worksheet
    Dim cmd As Object

    Set cn = CreateObject("ADODB.Connection")
    Set sh = Worksheets("Foglio1")
    Set cmd = CreateObject("ADODB.Command")
    
    With cn
        .CursorLocation = 1
        .Open "Provider=SQLNCLI10;" & _
            "Server=MAURO10\SQLEXPRESS;" & _
            "Database=ProvaDB1;" & _
            "Trusted_Connection=yes;"
    End With
    
    Set cmd.ActiveConnection = cn
    
    With cmd
        .CommandText = "DEL_DATI"
        .CommandType = 4
        .Parameters("@ID").Value = sh.Range("A1").Value
        .Execute
    End With
    
RigaChiusura:
    If cn.State = 1 Then
        cn.Close
    End If

    Set cmd = Nothing
    Set cn = Nothing
    Set sh = Nothing
    Exit Sub

RigaErrore:
    MsgBox Err.Number & vbNewLine & Err.Description
    Resume RigaChiusura

End Sub

10 - Importare in Excel i record tramite una View di SQL Server. (top)
Codice importa nel Foglio1 apartire dalla cella A2, i record restituiti da una View presente nel database SQL Server. Codice da copia/incollare in un modulo standard:

Public Sub mImportaDaView()

On Error GoTo RigaErrore

    Dim cn As Object
    Dim rs As Object
    Dim sh As Worksheet
    Dim sSQL As String

    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    Set sh = Worksheets("Foglio1")
    
    sSQL = "SELECT * FROM View_1"
    
    With cn
        .CursorLocation = 1
        .Open "Provider=SQLNCLI10;" & _
            "Server=MAURO10\SQLEXPRESS;" & _
            "Database=ProvaDB1;" & _
            "Trusted_Connection=yes;"
    End With
    
    With rs
        .CursorLocation = 1
        .Open sSQL, cn, 1, 3, 1
    End With
    
    With sh
        .Cells.Clear
        .Range("A2").CopyFromRecordset rs
    End With
    
RigaChiusura:
    If rs.State = 1 Then
        rs.Close
    End If
    
    If cn.State = 1 Then
        cn.Close
    End If

    Set rs = Nothing
    Set cn = Nothing
    Set sh = Nothing
    Exit Sub

RigaErrore:
    MsgBox Err.Number & vbNewLine & Err.Description
    Resume RigaChiusura

End Sub

11 - Importare da SQL Server in Excel utilizzando stored procedure. (top)
Codice che importa nel Foglio1 a partire dalla cella A2, i record restituiti da una stored procedure presente nel database SQL Server.

1. Questo lo statement che sul database ha creato la stored procedure che utilizzerò:

USE ProvaDB1;
GO
CREATE PROCEDURE IMP_DATI
AS
    SELECT * FROM Tabella1;
GO

2. Questo il codice che da Excel utilizzerà la stored procedure, importando tutti i record della Tabella1 del database a partire dalla cella A2. Codice da copia/incollare in un modulo standard:

Public Sub mImportaDatiSP()

On Error GoTo RigaErrore

    Dim cn As Object
    Dim sh As Worksheet
    Dim cmd As Object
    Dim rs As Object

    Set cn = CreateObject("ADODB.Connection")
    Set sh = Worksheets("Foglio1")
    Set rs = CreateObject("ADODB.Recordset")
    Set cmd = CreateObject("ADODB.Command")
    
    With cn
        .CursorLocation = 1
        .Open "Provider=SQLNCLI10;" & _
            "Server=MAURO10\SQLEXPRESS;" & _
            "Database=ProvaDB1;" & _
            "Trusted_Connection=yes;"
    End With
    
    Set cmd.ActiveConnection = cn
    
    With cmd
        .CommandText = "IMP_DATI"
        .CommandType = 4
        rs.Open .Execute
    End With
    
    With sh
        .Cells.Clear
        .Range("A2").CopyFromRecordset rs
    End With
    
RigaChiusura:
    If rs.State = 1 Then
        rs.Close
    End If

    If cn.State = 1 Then
        cn.Close
    End If

    Set cmd = Nothing
    Set rs = Nothing
    Set cn = Nothing
    Set sh = Nothing
    Exit Sub

RigaErrore:
    MsgBox Err.Number & vbNewLine & Err.Description
    Resume RigaChiusura

End Sub

12 - Importare da SQL Server in Excel utilizzando parametri e stored procedure. (top)
Codice che importa nel Foglio1 a partire dalla cella A2, i record restituiti da una stored procedure presente nel database SQL Server.

1. Questo lo statement che sul database ha creato la stored procedure che utilizzerò:

USE provaDB1;
GO
CREATE PROCEDURE IMP_DATISP   
   @Comune VARCHAR(50)
AS
    SELECT * FROM Tabella1 
    WHERE Comune = @Comune;
GO

2. Questo il codice che da Excel utilizzerà la stored procedure, importando tutti i record della Tabella1 del database che hanno una corrispondenza nel campo Comune con quanto contenuto in cella C1. Codice da copia/incollare in un modulo standard:

Public Sub mImportaDatiSPPar()

On Error GoTo RigaErrore

    Dim cn As Object
    Dim sh As Worksheet
    Dim cmd As Object
    Dim rs As Object

    Set cn = CreateObject("ADODB.Connection")
    Set sh = Worksheets("Foglio1")
    Set rs = CreateObject("ADODB.Recordset")
    Set cmd = CreateObject("ADODB.Command")
    
    With cn
        .CursorLocation = 1
        .Open "Provider=SQLNCLI10;" & _
            "Server=MAURO10\SQLEXPRESS;" & _
            "Database=ProvaDB1;" & _
            "Trusted_Connection=yes;"
    End With
    
    Set cmd.ActiveConnection = cn
    
    With cmd
        .CommandText = "IMP_DATISP"
        .CommandType = 4
        .Parameters("@Comune").Value = sh.Range("C1").Value
        rs.Open .Execute
    End With
    
    With sh
        .Cells.Clear
        .Range("A2").CopyFromRecordset rs
    End With
    
RigaChiusura:
    If rs.State = 1 Then
        rs.Close
    End If

    If cn.State = 1 Then
        cn.Close
    End If

    Set cmd = Nothing
    Set rs = Nothing
    Set cn = Nothing
    Set sh = Nothing
    Exit Sub

RigaErrore:
    MsgBox Err.Number & vbNewLine & Err.Description
    Resume RigaChiusura

End Sub

Per ulteriori informazioni su Microsoft Excel ed il suo Visual Basic, utilizzate questo forum:
http://social.answers.microsoft.com/Forums/it-IT/officeexcelit/threads

Ultimo aggiornamento di questa pagina: 16/02/2011 - mauro gamberini

maurogsc.eumauro gamberini

Database: Excel e SQL Server - VBA

maurogsc.eu
mauro gamberini