Az Excel gyakorlatilag bármire képes; hogy mindenre rá kell-e venni, az más kérdés. Bár a táblázat nagyon hatékony az adatok manipulálásában, nem túl nagy a normalizált adatok tárolásában. Az Excel kihasználása relációs adatbázisokhoz, mint pl SQL Server növeli az alkalmazás teljesítményét.
Kezdésként szükséged lesz MS Accessre, vagy a stabilabb és ingyenesebbre – SQL Server Expressz. Feltételezhető, hogy az olvasó megjeleníti az Excel fejlesztői szalagot, és ismeri a VBA-szerkesztőt és a Structured Query Language (SQL) nyelvet. Ez a cikk használja SQL Server kapcsolati karakterláncok. Az MS-Access szolgáltatással kapcsolatban keresse fel a Google-t.
Míg az Excel saját beépített rutinokkal rendelkezik az információk lekéréséhez SQL Server (mondjuk) pivot táblába, példánk nagyobb rugalmasságot biztosít az adatok kiválasztásában.
Csatlakozási karakterlánc
privát adatbázist fogok használni; szúrja be a saját illesztőprogram-információit az enyémek helyett a ConnectDatabase alrutinban. Utána használjuk connDB mint kommunikációs csatorna adatbázisunkhoz – esetemben egy tárolt eljárás eredményeinek visszaadására. Használhat több szabványos SQL-utasítást is, például „Select * from…”
Ügyrend
Először is betöltjük a combo-box opciókat innen SQL Server amikor a munkafüzet megnyílik, egy Auto_open makró használatával, és a „ComboData” munkalapra kiíratva. Akár a felhőben, akár helyileg van a szerver, az Excel indításakor nem lesz észrevehető késés – feltéve, hogy az adatbázis elérhető a munkaállomásról.
Ezután kivonjuk a szűrt adatokat az adatbázisból, és bedobjuk az Excelbe, az F–K oszlopokba.
Az interfész
Az enyémben vannak legördülő listák az adatok szűrésére az adatbázisból. A Szerep kombinált mező keresést indít el a jobb oldali táblázat feltöltéséhez.
Nevezze át a „Sheet1”-et „Main”-re. Adjon hozzá legalább egy kombinált mezőt.
A kód
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
Formázza a kombinált vezérlőt a „ComboData” lapok olvasásához. Ezután kattintson a jobb gombbal a kombinált mezőre, hogy hozzárendelje a ReadData aleljárást. Ha egy elemet kiválaszt a kombinált mezőben, írja be a kulcsát a „Fő” lap D7 cellájába. A VBA-kód ezt a kulcsot fogja használni szűrőként (lásd fent az inRole-t).
dll könyvtárra mutató hivatkozások
A kódablakban az Eszközök>Hivatkozások menüponttal hivatkozhat a Microsoft Active X Data Objects könyvtárra. Ez lehetővé teszi az Excel számára, hogy a kódban deklarált ADODB objektumokat használja.
A fenti ReadData alrutin egy relációs adatstruktúrát használ, amelyet az alábbiakban mutatunk be, amit egyedül Excelben nehéz elérni.
A további adatmódosítások visszaírást válthatnak ki az adatbázisba, a megfelelő SQL Update utasítás követésével connDB.execute(strSQL).
Végül védje meg kódját a megtekintéstől vagy módosítástól: Eszközök>Tulajdonságok>Védelem.
Excel problémák kezelése:
Időről időre, különösen, ha összetett programokat tartalmaz, az Excel összeomolhat, és nem tudja megfelelően lefedni. Abban az esetben, ha a sérült xlsx fájl, egy hatékony helyreállító eszköz kéznél tartása a legtöbb problémát megoldja.
Szerző Bevezetés:
Felix Hooker adat-helyreállítási szakértő DataNumen, Inc., amely világelső az adat-helyreállítási technológiák területén, beleértve javítás rar hiba és SQL helyreállítási szoftvertermékek. További információért látogasson el www.datanumen.com