Excel voi tehdä käytännössä mitä tahansa; Se, pitäisikö sen saada tekemään kaikki, on toinen asia. Vaikka laskentataulukko on erittäin tehokas tietojen käsittelyssä, se ei ole liian hyvä normalisoitujen tietojen tallentamiseen. Excelin valjastaminen relaatiotietokantaan, kuten SQL Server lisää sovelluksen tehoa.
Aluksi tarvitset MS Accessin tai vakaamman ja ilmaisemman ohjelman – SQL Server Ilmaista. Lukijassa oletetaan olevan Excel-kehittäjänauha näytössä ja että hän tuntee VBA-editorin ja Structured Query Language (SQL) -kielen. Tämä artikkeli käyttää SQL Server yhteysmerkkijonoja. Katso MS-Access Googlesta.
Vaikka Excelillä on omat sisäänrakennetut rutiinit tietojen saamiseksi SQL Server (sanotaan) pivot-taulukkoon, esimerkkimme antaa enemmän joustavuutta tietojen valinnassa.
Yhteysmerkkijono
Käytän yksityistä tietokantaa; lisää omat ajuritietosi minun tilalle ConnectDatabase-alirutiinissa. Käytämme sitten connDB viestintäkanavana tietokantaamme – minun tapauksessani palauttamaan tulokset tallennetusta menettelystä. Voit käyttää tavallisia SQL-käskyjä, kuten "Select * from…"
Käsittelyjärjestys
Ensin lataamme yhdistelmälaatikon valinnat kohteesta SQL Server kun työkirja avautuu, käytä Auto_open-makroa ja kopioi se "ComboData"-arkkiin. Olipa palvelin pilvessä tai paikallisesti, Excelin käynnistyksessä ei ole havaittavaa viivettä – kunhan tietokantaan pääsee työasemalta.
Seuraavaksi poimimme suodatetut tiedot tietokannasta ja pudotamme ne Exceliin, sarakkeet F–K.
Liitäntä
Omassani on pudotusvalikot tietojen suodattamiseksi tietokannasta. The Rooli yhdistelmäruutu käynnistää haun oikeanpuoleisen taulukon täyttämiseksi.
Nimeä "Sheet1" uudelleen nimellä "Main". Lisää vähintään yksi yhdistelmälaatikko.
Koodi
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
Muotoile yhdistelmäruudun ohjausobjekti lukeaksesi arkit "ComboData". Napsauta sitten hiiren kakkospainikkeella yhdistelmäruutua määrittääksesi sille ReadData-alimenettelyn. Kun kohde on valittu yhdistelmäruudussa, kirjoita sen avain arkin "Pää" soluun D7. VBA-koodi käyttää tätä avainta suodattimena (katso inRole edellä).
Viittaukset dll-kirjastoon
Käytä koodi-ikkunassa Työkalut>Viittaukset-valikkoa viitataksesi Microsoft Active X Data Objects -kirjastoon. Tämä mahdollistaa Excelin käyttää koodissa määriteltyjä ADODB-objekteja.
Yllä oleva ReadData-alirutiini käyttää alla näkyvää relaatiotietorakennetta, jota on vaikea saavuttaa yksin Excelissä.
Muut datamuutokset voivat laukaista takaisinkirjoituksen tietokantaan, ja sitä seuraa sopiva SQL Update -käsky connDB.execute(strSQL).
Suojaa lopuksi koodisi katselulta tai muuttamiselta: Työkalut> Ominaisuudet> Suojaus.
Käsittele Excel-ongelmia:
Ajoittain, varsinkin kun se sisältää monimutkaisia ohjelmia, Excel saattaa kaatua eikä peitä sitä kunnolla. Siinä tapauksessa, että a vaurioitunut xlsx tiedoston, tehokkaan palautustyökalun pitäminen kätevänä ratkaisee useimmat ongelmat.
Tekijän esittely:
Felix Hooker on tietojen palauttamisen asiantuntija DataNumen, Inc., joka on maailman johtava tietojen palautustekniikoissa, mukaan lukien korjaus rar virhe ja sql-palautusohjelmistotuotteet. Lisätietoja osoitteessa www.datanumen.com