Excel var darīt praktiski visu; vai to vajadzētu likt darīt visu, tas ir cits jautājums. Lai gan izklājlapa ir ļoti spēcīga, manipulējot ar datiem, tā nav pārāk lieliska normalizētu datu glabāšanai. Excel izmantošana relāciju datu bāzē, piemēram, SQL Server uzlabo lietojumprogrammas jaudu.
Sākumā jums būs nepieciešama MS Access vai stabilāka un bezmaksas programma. SQL Server Izteikt. Tiek pieņemts, ka lasītājam ir parādīta Excel izstrādātāja lente un viņš ir iepazinies ar VBA redaktoru un strukturēto vaicājumu valodu (SQL). Šis raksts izmanto SQL Server savienojuma virknes. Par MS-Access skatiet Google.
Kaut arī programmai Excel ir sava iebūvēta kārtība, no kuras iegūt informāciju SQL Server (teiksim) rakurstabulā, mūsu piemērs sniegs lielāku elastību datu atlasē.
Savienojuma virkne
Es izmantošu privātu datu bāzi; ievietojiet savu draivera informāciju manējās vietā ConnectDatabase apakšprogrammā. Pēc tam mēs izmantojam connDB kā saziņas kanālu mūsu datubāzei - manā gadījumā atgriezt saglabātās procedūras rezultātus. Varat izmantot vairāk standarta SQL priekšrakstu, piemēram, “Atlasīt * no…”
Darba kārtība
Pirmkārt, mēs ielādēsim kombinēto lodziņu izvēli no SQL Server kad darbgrāmata tiek atvērta, izmantojot Auto_open makro un izgāžot to lapā “ComboData”. Neatkarīgi no tā, vai serveris atrodas mākonī vai lokāli, Excel startēšanas laikā nebūs ievērojamas aizkaves, ja vien datubāze ir pieejama no darbstacijas.
Pēc tam mēs no datu bāzes iegūsim filtrētos datus un nometīsim tos Excel, F līdz K slejās.
Saskarne
Manējā ir nolaižamās izvēles rūtiņas, lai filtrētu informāciju no datu bāzes. The Loma kombinētais lodziņš izraisa meklēšanu, lai aizpildītu tabulu labajā pusē.
Pārdēvējiet “Sheet1” par “Main”. Pievienojiet vismaz vienu kombināciju.
Kodekss
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
Formatējiet kombinētās lodziņa vadību, lai lasītu lapas “ComboData”. Pēc tam ar peles labo pogu noklikšķiniet uz kombinētā lodziņa, lai tam piešķirtu apakšprocedūru ReadData. Kad kombinācijas lodziņā ir atlasīts vienums, ierakstiet tā atslēgu lapas “Main” šūnā D7. VBA kods izmantos šo atslēgu kā filtru (skat. IntRole iepriekš).
Atsauces uz dll bibliotēku
Koda logā izmantojiet Rīki>Atsauces, lai atsauktos uz Microsoft Active X datu objektu bibliotēku. Tas ļaus programmai Excel izmantot kodā deklarētos ADODB objektus.
Iepriekš aprakstītajā ReadData apakšprogrammā tiek izmantota relāciju datu struktūra, kas parādīta zemāk, un to ir grūti sasniegt tikai programmā Excel.
Turpmākas datu izmaiņas var izraisīt datubāzes atpakaļrakstīšanu, kam seko atbilstošais SQL atjaunināšanas priekšraksts connDB.execute (strSQL).
Visbeidzot, pasargājiet savu kodu no skatīšanas vai mainīšanas: Rīki> Rekvizīti> Aizsardzība.
Rīkoties ar Excel problēmām:
Laiku pa laikam, it īpaši, ja tajā ir sarežģītas programmas, Excel var avarēt un neizdoties pareizi pārklāt. Gadījumā, ja bojāts xlsx failu, efektīva atkopšanas rīka izmantošana atrisinās lielāko daļu problēmu.
Autora ievads:
Fēlikss Hukers ir datu atkopšanas eksperts DataNumen, Inc., kas ir pasaules līderis datu atkopšanas tehnoloģiju, tostarp remonts rar kļūda un SQL atkopšanas programmatūras produkti. Lai iegūtu vairāk informācijas, apmeklējiet vietni www.datanumen. Ar