Použití aplikace Excel VBA k dotazování databáze SQL Server

Ačkoli můžeme v aplikaci Excel vytvářet externí datové sady, které čerpají informace z databáze SQL Server, někdy se stane, že chcete zkontrolovat hodnotu nebo vyhledat hodnotu z podkladové databáze SQL Server přímo ze vzorce aplikace Excel.

Podíváme se na příklad, který nám umožní vyhledat hodnotu celkového příjmu zákazníka ze vzorce Excelu.

VBA pro dotazování databáze SQL Serveru

Následující funkci VBA lze použít v rámci projektu Excel VBA. Nejprve však musíte do projektu přidat knihovnu ADODB.

Public Function LookupAWCustomerRevenue(intID As Long) As Currency Dim conn As ADODB.Connection Dim rs As ADODB.Recordset Dim strConnString As String If intID = 0 Then LookupAWCustomerRevenue = 0 Else strConnString = "Provider=SQLOLEDB;Data Source=W10NBMJD\SQL2014;" _ & "Initial Catalog=AdventureWorks2014;Integrated Security=SSPI;" Set conn = New ADODB.Connection conn.Open strConnString Set rs = conn.Execute("SELECT SUM(TotalDue) AS CustRev FROM Sales.SalesOrderHeader WHERE CustomerID = " & intID) If Not IsNumeric(rs.Fields("CustRev").Value) Then LookupAWCustomerRevenue = 0 Else LookupAWCustomerRevenue = rs.Fields("CustRev").Value rs.Close End If End If End Function

V tomto příkladu použijeme připojení ADO pro připojení k instanci SQL Serveru (DBSRV\SQL2014):

 strConnString = "Provider=SQLOLEDB;Data Source=DBSRV\SQL2014;" _ & "Initial Catalog=AdventureWorks2014;Integrated Security=SSPI;"

• Parametr Provider označuje, že bude vytvořeno připojení OLDEB, a parametr Data Source ukazuje na instanci SQL Serveru.
• Parametr Initial Catalog označuje databázi, která bude dotazována (AdventureWorks2014)
• Parametr Integrated Security označuje, že k ověření se serverem SQL Server bude použita autentizace Windows Authemtication.

Objekt RecordSet (rs) slouží k vytvoření sady záznamů z příkazu SELECT:

 Set rs = conn.Execute("SELECT SUM(TotalDue) AS CustRev FROM Sales.SalesOrderHeader WHERE CustomerID = " & intID)

Příkaz SELECT je sestaven z doslovného řetězce a hodnoty proměnné intID, která je předána do funkce při jejím volání.

Příkaz If na začátku kontroluje, zda má proměnná intID hodnotu 0. Celočíselné proměnné mají implicitně hodnotu nula, pokud nejsou inicializovány (jinými slovy, pokud není při volání funkce zadána žádná hodnota). Pokud není funkci předána žádná hodnota, je jako hodnota příjmu vrácena hodnota 0.

 If intID = 0 Then LookupAWCustomerRevenue = 0

Druhý příkaz If testuje, zda příkaz SELECT nevrací jinou než číselnou hodnotu. Pokud je ID zákazníka předané do funkce platné, ale nemá zadané žádné objednávky, výraz SUM(TotalDue) vrátí hodnotu NULL. Pokud se tak stane, funkce místo toho vrátí hodnotu 0.

 If Not IsNumeric(rs.Fields("CustRev").Value) Then LookupAWCustomerRevenue = 0

Scénář s hodnotou NULL je vidět na následujícím snímku obrazovky.

Na řádek kódu VBA obsahující příkaz If jsem umístil bod přerušení a otevřel okno Locals Window, abych mohl vidět všechny hodnoty proměnných v daném bodě provádění.

Funkci jsem otestoval spuštěním z Okna okamžitého spuštění v editoru VBA:

 ?LookupAWCustomerRevenue(1)

Při nastaveném bodu přerušení se provádění kódu automaticky zastaví na označeném řádku a umožní nám zobrazit prostředí v tomto bodě provádění.

V Okně Locals na výše uvedeném snímku obrazovky je zobrazena proměnná objektu Recordset rs, konkrétně hodnota prvního pole z rs, „CustRev“. Vidíme, že je nastavena na hodnotu Null. Je to proto, že zákazník s hodnotou CustomerID 1 nezadal žádnou objednávku, a proto nemá žádnou výslednou hodnotu tržeb.

Následující snímek obrazovky ukazuje, že dotaz při přímém spuštění na instanci SQL Serveru vrací hodnotu NULL:

Pokud je do funkce předáno platné CustomerID a příkaz SELECT vrátí hodnotu, která není NULL, funkce vrátí celkové tržby z prodeje pro tohoto zákazníka jako hodnotu v měně.

 LookupAWCustomerRevenue = rs.Fields("CustRev").Value

Volání funkce VBA ze vzorce aplikace Excel

Volání funkce VBA ze vzorce aplikace Excel je jednoduché. Při konstrukci vzorce Excelu jeho zadáním do buňky zobrazí intellisense odpovídající funkce VBA i vestavěné funkce. To ukazuje následující snímek obrazovky:

Vidíte, že výše jsou uvedeny dvě funkce VBA, které začínají na Lo: LookupAWCustomerRevenue a LookupPersonName.

Následující příklad ukazuje použití funkce LookupAWCustomerRevemue v buňce pro výpočet celkových příjmů pro hodnotu CustomerID oddělenou ve sloupci A pracovního listu:

Souhrn

V tomto článku jsme se seznámili s kódováním VBA, některými technikami zpracování chyb a ladění VBA a viděli jsme, jak můžeme volat funkce VBA ze vzorce Excelu. Pokud máte jakékoli dotazy týkající se tohoto článku, neváhejte nám napsat na adresu [email protected]. Existuje také verze tohoto článku pro Access.

Možná zjistíte, že tyto kurzy se čas od času objeví v nabídce Late Availability, která nabízí úsporu 30 %.

.