Excel mund të bëjë pothuajse çdo gjë; nëse duhet bërë për të bërë gjithçka është tjetër çështje. Ndërsa spreadsheet është shumë i fuqishëm në manipulimin e të dhënave, nuk është shumë i mirë në ruajtjen e të dhënave të normalizuara. Përdorimi i Excel në një bazë të dhënash relacionale si SQL Server rrit fuqinë e aplikacionit.
Për të filluar, do t'ju duhet MS Access ose një version më i qëndrueshëm dhe falas – SQL Server shprehin. Supozohet se lexuesi ka të shfaqur shiritin e Zhvilluesit të Excel dhe është i njohur me Redaktorin VBA dhe gjuhën e strukturuar të pyetjeve (SQL). Ky artikull përdor SQL Server vargjet e lidhjes. Për MS-Access, referojuni Google.
Ndërsa Excel ka rutinat e veta të integruara për marrjen e informacionit SQL Server në (të themi) një tabelë kryesore, shembulli ynë do të japë më shumë fleksibilitet në përzgjedhjen e të dhënave.
Vargu i lidhjes
Unë do të përdor një bazë të dhënash private; futni informacionin tuaj të shoferit në vend të timit në nënrutinë ConnectDatabase. Më pas përdorim connDB si një kanal komunikimi në bazën tonë të të dhënave – në rastin tim për të kthyer rezultatet nga një procedurë e ruajtur. Ju mund të përdorni më shumë deklarata standarde SQL si "Zgjidh * nga ..."
Urdhri i Biznesit
Së pari, ne do të ngarkojmë zgjedhjet e kutisë së kombinuar nga SQL Server kur hapet libri i punës, duke përdorur një makro Auto_open dhe duke e hedhur atë në fletën "ComboData". Pavarësisht nëse Serveri është në cloud apo lokal, nuk do të ketë vonesë të dukshme në nisjen e Excel - për sa kohë që baza e të dhënave është e arritshme nga stacioni i punës.
Më pas, ne do të nxjerrim të dhënat e filtruara nga baza e të dhënave dhe do t'i hedhim në Excel, kolonat F deri në K.
Ndërfaqja
Mini ka kuti rënëse për të filtruar informacionin nga baza e të dhënave. Të Rol kutia e kombinuar shkakton një kërkim për të mbushur tabelën në të djathtë.
Riemërto "Fleta1" si "Kryesore". Shtoni të paktën një kuti të kombinuar.
Kodi
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
Formatoni komandën e kutisë së kombinuar për të lexuar fletët "ComboData". Pastaj kliko me të djathtën në kutinë e kombinuar për t'i caktuar asaj nënprocedurën ReadData. Kur një artikull zgjidhet në kutinë e kombinuar, shkruani çelësin e tij në fletën "Main", qeliza D7. Kodi VBA do ta përdorë këtë çelës si filtër (shih intRole, më lart).
Referenca për bibliotekën dll
Përdorni Tools>References në dritaren e kodit për t'iu referuar bibliotekës Microsoft Active X Data Objects. Kjo do t'i mundësojë Excel-it të përdorë objektet ADODB të deklaruara në kod.
Nën-rutina ReadData e mësipërme përdor një strukturë të dhënash relacionale, e paraqitur më poshtë, e cila është e vështirë të arrihet vetëm në Excel.
Ndryshime të mëtejshme të të dhënave mund të shkaktojnë një rikthim në bazën e të dhënave, me deklaratën e duhur SQL Update të ndjekur nga connDB.execute(strSQL).
Më në fund, mbroni kodin tuaj nga shikimi ose ndryshimi: Mjetet>Vetitë>Mbrojtja.
Trajtoni problemet e Excel:
Herë pas here, veçanërisht kur mban programe komplekse, Excel mund të dështojë dhe të mos arrijë të mbulojë përsëri siç duhet. Në rast të një xlsx e dëmtuar skedar, të kesh në dispozicion një mjet efektiv rikuperimi do të zgjidhë shumicën e problemeve.
Hyrje e autorit:
Felix Hooker është një ekspert i rikuperimit të të dhënave në DataNumen, Inc., e cila është lider botëror në teknologjitë e rikuperimit të të dhënave, duke përfshirë riparim rar gabim dhe produkte softuerike për rikuperimin sql. Për më shumë informacion vizitoni www.datanumen.com