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.
Per cominciare, avrai bisogno di MS Access o della versione più stabile e gratuita – 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 la cartella di lavoro si apre, utilizzando una macro Auto_open, il contenuto viene salvato nel foglio "ComboData". Indipendentemente dal fatto che il server si trovi nel cloud o in locale, non si noterà alcun ritardo nell'avvio di Excel, a condizione che 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 alla libreria dll
Nella finestra del codice, utilizza Strumenti > Riferimenti per aggiungere un riferimento alla libreria Microsoft ActiveX Data Objects. In questo modo, Excel potrà 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 Per quanto riguarda i file, avere a portata di mano uno strumento di recupero efficace risolverà la maggior parte dei 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