Сыртқы дерекқорды оқу және жазу үшін Excel бағдарламасын қалай пайдалануға болады

Excel іс жүзінде бәрін жасай алады; бәрін жасау керек пе - бұл басқа мәселе. Электрондық кесте деректерді басқаруда өте күшті болғанымен, нормаланған деректерді сақтауға онша қолайсыз. Сияқты реляциялық мәліметтер базасына Excel-ді қолдану SQL Server қосымшаның қуатын арттырады.

Бастапқыда сізге MS Access немесе тұрақтырақ және тегін нұсқасы қажет болады – SQL Server Экспресс. Оқырманға Excel Developer лентасы көрсетілген және ол VBA редакторымен және құрылымдық сұраныстар тілімен (SQL) таныс деп болжанады. Бұл мақалада қолданылады SQL Server байланыс жолдары. MS-Access үшін Google-ге жүгініңіз.

Сонымен Excel бағдарламасында ақпарат алу үшін өзінің кіріктірілген рәсімдері бар SQL Server айналмалы кестеге (айталық), біздің мысал деректерді таңдауда икемділік береді.

Байланыс жолдары

Мен жеке дерекқорды пайдаланатын боламын; өзіңіздің жеке драйверіңіз туралы мәліметтерді ConnectDatabase қосымшасына қосыңыз. Біз содан кейін қолданамыз ConnDB біздің дерекқорға байланыс арнасы ретінде - менің жағдайда сақталған процедураның нәтижелерін қайтару. Сіз «Select * from ...» сияқты стандартты SQL операторларын қолдана аласыз.

Бизнес тәртібі

Біріншіден, біз тізімнен өрістерді таңдаймыз SQL Server жұмыс кітабы ашылған кезде, Auto_open макросын пайдаланып, оны «ComboData» парағына орналастырады. Сервер бұлтта немесе жергілікті жерде болса да, Excel бағдарламасын іске қосуда ешқандай байқалатын кідіріс болмайды – дерекқорға жұмыс станциясынан қол жеткізуге болатын болса.

Әрі қарай, біз дерекқордан сүзілген деректерді шығарамыз және оны Excel-ге, F-K бағандарына тастаймыз.

Интерфейс

Mine базасында ақпаратты сүзуге арналған ашылмалы терезелер бар. The рөлі құрама өріс кестені оң жақта толтыру үшін іздеуді бастайды.

«Парақ1» атын «Негізгі» деп өзгертіңіз. Кем дегенде бір комбокс қосыңыз.

Кодекс

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

«ComboData» парақтарын оқу үшін құрама өріс басқару элементін форматтаңыз. Одан кейін ReadData ішкі процедурасын тағайындау үшін құрама өрісті тінтуірдің оң жағымен басыңыз. Комбокста элемент таңдалғанда, оның кілтін D7 ұяшығына «Басты» парағына жазыңыз. VBA коды бұл кілтті сүзгі ретінде қолданады (intRole, жоғарыда қараңыз).

dll кітапханасына сілтемелер

Microsoft Active X Data Objects кітапханасына сілтеме жасау үшін код терезесінде Құралдар>Сілтемелер тармағын пайдаланыңыз. Бұл Excel бағдарламасына кодта көрсетілген ADODB нысандарын пайдалануға мүмкіндік береді.

Жоғарыда келтірілген ReadData қосымшасы төменде көрсетілген реляциялық деректер құрылымын пайдаланады, оған тек Excel бағдарламасында қол жеткізу қиын.

Деректердің одан әрі өзгеруі дерекқорға SQL Update сәйкес мәлімдемесімен бірге қайта жазуды тудыруы мүмкін connDB.execute (strSQL).

Соңында, кодты қарау немесе өзгертуден қорғаңыз:  Құралдар> Сипаттар> Қорғау.

Excel проблемаларын шешу:

Кейде, әсіресе күрделі бағдарламалар болған кезде, Excel жұмыс істемей қалуы мүмкін және дұрыс жабылмауы мүмкін. Жағдайда зақымдалған xlsx файлды пайдаланбасаңыз, тиімді қалпына келтіру құралының болуы көптеген мәселелерді шешеді.

Автордың кіріспесі:

Феликс Хукер - деректерді қалпына келтіру бойынша сарапшы DataNumen, Соның ішінде деректерді қалпына келтіру технологиялары бойынша әлемдік көшбасшы болып табылатын Inc. жөндеу rar қателік және SQL қалпына келтіру бағдарламалық жасақтама өнімдері. Қосымша ақпарат алу үшін кіріңіз WWW.datanumen.com