Excel può fare praticamente qualsiasi cosa; se debba essere fatto per fare tutto è un'altra questione. Sebbene il foglio di calcolo sia molto potente nella manipolazione dei dati, non è eccezionale nell'archiviazione di dati normalizzati. Sfruttare Excel in un database relazionale come SQL Server aumenta la potenza dell'applicazione.
A start con, avrai bisogno di MS Access o del più stabile e gratuito - SQL Server Esprimere. Si presuppone che il lettore disponga della barra multifunzione di Excel Developer e abbia familiarità con l'editor VBA e il linguaggio di query strutturato (SQL). Questo articolo utilizza SQL Server stringhe di connessione. Per MS-Access, fare riferimento a Google.
Mentre Excel ha le proprie routine integrate per ottenere informazioni da SQL Server in (diciamo) una tabella pivot, il nostro esempio darà maggiore flessibilità nella selezione dei dati.
Stringa di connessione
Userò un database privato; inserisci le tue informazioni sul driver al posto delle mie nella sottoroutine ConnectDatabase. Usiamo quindi connDB come canale di comunicazione al nostro database, nel mio caso per restituire i risultati di una stored procedure. Potresti utilizzare istruzioni SQL più standard come "Seleziona * da ..."
Ordine degli affari
Innanzitutto, caricheremo le scelte della casella combinata da SQL Server quando si apre la cartella di lavoro, utilizzando una macro Auto_open e scaricandola nel foglio "ComboData". Indipendentemente dal fatto che il server sia nel cloud o in locale, non ci saranno ritardi evidenti nei messaggi di posta elettronicatarting Excel – purché il database sia accessibile dalla workstation.
Successivamente, estrarremo i dati filtrati dal database e li trascineremo in Excel, colonne da F a K.
L'interfaccia
Il mio ha caselle a discesa per filtrare le informazioni dal database. IL Ruolo casella combinata attiva una ricerca per popolare la tabella a destra.
Rinominare "Foglio1" come "Principale". Aggiungi almeno una casella combinata.
Il codice
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
Formatta il controllo della casella combinata per leggere i fogli "ComboData". Quindi fare clic con il pulsante destro del mouse sulla casella combinata per assegnarle la procedura secondaria ReadData. Quando un elemento è selezionato nella casella combinata, scrivi la sua chiave nel foglio "Principale", cella D7. Il codice VBA utilizzerà questa chiave come filtro (vedi intRole, sopra).
Riferimenti a dll library
Utilizzare Strumenti>Riferimenti nella finestra del codice per fare riferimento alla libreria Microsoft Active X Data Objectsrarsi. Ciò consentirà a Excel di utilizzare gli oggetti ADODB dichiarati nel codice.
La sottoroutine ReadData sopra utilizza una struttura di dati relazionale, mostrata di seguito, che è difficile da ottenere solo in Excel.
Ulteriori modifiche ai dati potrebbero attivare un write-back nel database, con l'appropriata istruzione SQL Update seguita da connDB.execute(strSQL).
Infine, proteggi il tuo codice dalla visualizzazione o modifica: Strumenti>Proprietà>Protezione.
Gestisci i problemi di Excel:
Di tanto in tanto, in particolare quando contiene programmi complessi, Excel potrebbe bloccarsi e non riuscire a ricoprire correttamente. In caso di a XLSX danneggiato file, avere a portata di mano uno strumento di recupero efficace risolverà most problemi.
Introduzione dell'autore:
Felix Hooker è un esperto di recupero dati in DataNumen, Inc., che è il leader mondiale nelle tecnologie di recupero dati, tra cui riparazione rar errore e prodotti software di recupero SQL. Per maggiori informazioni visita www.datanumen.com