Excel umí prakticky cokoli; to, zda by se mělo dělat všechno, je jiná věc. I když tabulka manipuluje s daty velmi efektivně, při ukládání normalizovaných dat není příliš velká. Využití aplikace Excel k relační databázi, jako je SQL Server zvyšuje výkon aplikace.
Pro začátek budete potřebovat MS Access nebo stabilnější a bezplatnější verzi – SQL Server Vyjádřit. Předpokládá se, že čtenář má zobrazenou pásku aplikace Excel Developer a je obeznámen s editorem VBA a jazykem strukturovaných dotazů (SQL). Tento článek používá SQL Server připojovací řetězce. Informace o MS-Access najdete na Googlu.
Zatímco Excel má vlastní vestavěné rutiny pro získávání informací SQL Server do (řekněme) kontingenční tabulky, náš příklad poskytne větší flexibilitu při výběru dat.
Připojovací řetězec
Budu používat soukromou databázi; vložte své vlastní informace o ovladači namísto do podprogramu ConnectDatabase. Poté použijeme connDB jako komunikační kanál do naší databáze - v mém případě vrátit výsledky uložené procedury. Můžete použít více standardních příkazů SQL, jako je „Vybrat * z ...“
Pracovní řád
Nejprve načteme možnosti se seznamem z SQL Server při otevření sešitu pomocí makra Auto_open a jeho uložení do listu „ComboData“. Ať už je server v cloudu nebo lokálně, nedojde k žádnému znatelnému zpoždění při spuštění Excelu – pokud je databáze přístupná z pracovní stanice.
Dále extrahujeme filtrovaná data z databáze a vložíme je do Excelu, sloupce F až K.
Rozhraní
Důl má rozevírací pole k filtrování informací z databáze. The Role pole se seznamem spustí vyhledávání k naplnění tabulky vpravo.
Přejmenujte „List1“ na „Hlavní“. Přidejte alespoň jeden kombinovaný box.
Kodex
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
Naformátujte ovládací prvek pole se seznamem na čtení listů „ComboData“. Potom klepněte pravým tlačítkem na pole se seznamem a přiřaďte k němu dílčí postup ReadData. Když je v kombinovaném poli vybrána položka, zapište její klíč do listu „Hlavní“, buňka D7. Kód VBA použije tento klíč jako filtr (viz intRole výše).
Odkazy na knihovnu DLL
Pro odkazování na knihovnu objektů dat Microsoft Active X použijte v okně kódu nabídku Nástroje>Odkazy. To umožní aplikaci Excel používat objekty ADODB deklarované v kódu.
Výše uvedená rutina ReadData používá relační datovou strukturu, která je uvedena níže, což je obtížné dosáhnout pouze v aplikaci Excel.
Další změny dat by mohly vyvolat zpětný zápis do databáze s příslušným příkazem SQL Update následovaným connDB.execute (strSQL).
Nakonec ochraňte svůj kód před zobrazením nebo změnou: Nástroje> Vlastnosti> Ochrana.
Řešení problémů s Excelem:
Čas od času, zejména když obsahuje složité programy, může dojít k selhání aplikace Excel a selhání správného opětovného pokrytí. V případě poškozený xlsx soubor, mít po ruce účinný nástroj pro obnovu vyřeší většinu problémů.
Úvod autora:
Felix Hooker je odborník na obnovu dat v oboru DataNumen, Inc., která je světovým lídrem v oblasti technologií pro obnovu dat, včetně opravit rar chyba a SQL softwarové produkty pro obnovu. Pro více informací navštivte www.datanumen.com