Paano Gumagamit ng Excel upang Basahin at Sumulat ng isang Panlabas na Database

Maaaring gawin ng Excel ang anuman; kung dapat bang gawin upang gawin ang lahat ay isa pang usapin. Habang ang spreadsheet ay napakalakas sa pagmamanipula ng data, hindi ito masyadong mahusay sa pag-iimbak ng na-normalize na data. Gumagamit ng Excel sa isang nauugnay na database tulad ng SQL Server Pinahuhusay ang kapangyarihan ng application.

Kay start sa, kakailanganin mo ng MS Access o ang mas matatag at libre - SQL Server Ipahayag Ipinapalagay na ang mambabasa ay may ipinapakitang lipi ng Developer Developer, at pamilyar sa VBA Editor at Structured Query Language (SQL). Gumagamit ang artikulong ito SQL Server mga string ng koneksyon. Para sa MS-Access, sumangguni sa Google.

Habang ang Excel ay may sariling built-in na mga gawain para sa pagkuha ng impormasyon mula sa SQL Server sa (sabihin) isang pivot table, ang aming halimbawa ay magbibigay ng higit na kakayahang umangkop sa pagpili ng data.

String ng Koneksyon

Gumagamit ako ng isang pribadong database; ipasok ang iyong sariling impormasyon sa pagmamaneho sa lugar ng minahan sa sub routine ng ConnectDatabase. Ginagamit namin pagkatapos ConnDB bilang isang channel ng komunikasyon sa aming database - sa aking kaso upang ibalik ang mga resulta mula sa isang nakaimbak na pamamaraan. Maaari kang gumamit ng mas pamantayang mga pahayag ng SQL tulad ng "Pumili * mula sa ..."

Order ng Negosyo

Una, maglo-load kami ng mga pagpipilian sa combo-box mula sa SQL Server kapag bumukas ang workbook, gamit ang isang Auto_open macro, at itinatapon ito sa sheet na "ComboData". Kung ang ulap ay nasa cloud o lokal, walang mapapansin na pagkaantala sa starting Excel - basta ma-access ang database mula sa workstation.

Susunod, aalisin namin ang na-filter na data mula sa database at i-drop ito sa Excel, mga haligi F hanggang K.

Ang Interface

Ang minahan ay may mga drop-down box upang salain ang impormasyon mula sa database. Ang Papel ang combo box ay nagpapalitaw ng isang paghahanap upang mapunan ang mesa sa kanan.

Palitan ang pangalan ng "Sheet1" bilang "Pangunahin". Magdagdag ng kahit isang combobox.

Ang Kodigo

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

I-format ang combo box control upang mabasa ang mga sheet na "ComboData". Pagkatapos ay i-right click ang kahon ng combo upang italaga ang ReadData sub na pamamaraan dito. Kapag napili ang isang item sa combobox, isulat ang susi nito sa sheet na "Pangunahin", ang cell D7. Gagamitin ng VBA code ang key na ito bilang isang filter (tingnan ang intRole, sa itaas).

Mga sanggunian sa dll library

Gumamit ng Mga Tool> Mga sanggunian sa window ng code upang mag-refer sa libong Mga Aktibo ng Data ng Microsoft Aktibo Xrary Paganahin nito ang Excel upang magamit ang mga object ng ADODB na idineklara sa code.

Ang sub na gawain ng ReadData sa itaas ay gumagamit ng isang kaugnay na istraktura ng data, ipinapakita sa ibaba, na mahirap makamit sa Excel lamang.

Ang karagdagang mga pagbabago sa data ay maaaring magpalitaw ng isang pabalik-balik sa database, na may naaangkop na pahayag sa SQL Update na sinusundan ng connDB.execut (strSQL).

Panghuli, protektahan ang iyong code mula sa matingnan o mabago:  Mga tool> Katangian> Proteksyon.

Pangasiwaan ang mga problema sa Excel:

Paminsan-minsan, lalo na kapag nagtataglay ito ng mga kumplikadong programa, maaaring mag-crash ang Excel at mabigong muling masakop nang maayos. Sa kaganapan ng a nasira xlsx file, pagkakaroon ng isang mabisang tool sa pag-recover madaling gamiting most problema.

Panimula ng May-akda:

Si Felix Hooker ay isang dalubhasa sa pagbawi ng data sa DataNumen, Inc., na pinuno ng mundo sa mga teknolohiya sa pagbawi ng data, kasama ang magkumpuni rar mali at mga produkto ng software sa pag-recover ng sql. Para sa karagdagang impormasyon pagbisita www.datanumen. Sa