Data på en server kan endres ved å undersøke postene "klientsiden" i Excel VBA, endre dem etter behov og lagre dem tilbake til serveren.
En mer effektiv måte å gjøre dette på, spesielt hvis databasen er på et eksternt sted og det er mye trafikk involvert, er å gjøre jobben "server-side". Denne øvelsen kaller en lagret prosedyre fra Excel for å kategorisere ansatte i aldersgrupper i henhold til deres fødselsdato (dvs. 18-25 år, 26-35 år, osv.), uten rikelig utveksling av data mellom serveren og Excel.
Denne artikkelen forutsetter at leseren har utviklerbåndet vist og er kjent med VBA Editor. Hvis ikke, vennligst Google "Excel Developer Tab" eller "Excel Code Window".
Det er tre elementer i øvelsen:
- En datatabell tblAnsatte i en database TestDB;
- En lagret prosedyre spAgeRange;
- En Excel xlsm, som vi kaller xlsm. Du kan finne et eksempel på en Excel-fil her.
data~~POS=TRUNC
Lag en database i SQL Server som heter DBTest.
Sett opp følgende kolonner for en tabell tblAnsatte.
Kopier følgende inn i tabellen:
| 2017/05/25 | 1 | brun | J | 1946/12/02 | M | ||
| 2017/05/25 | 2 | Smart | A | 1976/03/26 | F | ||
| 2017/05/25 | 3 | cruise | T | 1962/07/03 | M | ||
| 2017/05/25 | 4 | Lohan | L | 1986/07/02 | F | ||
| 2017/05/25 | 5 | Fredricksen | F | 1964/03/15 | M | ||
| 2017/05/25 | 6 | Snyder | L | 1968/07/05 | F | ||
| 2017/05/25 | 7 | Lipnicki | J | 1983/11/25 | M | ||
| 2017/05/25 | 8 | Hoover | S | 2002/12/08 | F | ||
| 2017/05/25 | 9 | Watson | E | 1990/04/15 | F |
Lagret prosedyre.
Kjør dette skriptet mot TestDB for å lage den lagrede prosedyren:
BRUK [TestDB] GO /****** Objekt: StoredProcedure [dbo].[spAgeRange] Skriptdato: 2017/05/10 12:16:28 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER PÅ GÅ OPPRETT PROSEDYRE [dbo].[spAgeRange] @PayrollDate varchar(50) AS BEGIN SET NOCOUNT ON; OPPDATERING tblStaff SET Age = CONVERT(int, DATEDIFF(day, DateOfBirth, GETDATE()) / 365.25, 0) WHERE tblStaff.PayrollDate = @PayrollDate Oppdater tblStaff set AgeRange = '>56' =PagerollDate56 =PayrollDate>= Oppdater tblStaff set AgeRange = '46 til 55' der Alder >= 46 og Alder < 56 og PayrollDate = PayrollDate Oppdatering tblStaff set AgeRange = '39 til 45' der Alder >= 39 og Alder < 46 og PayrollDate = @PayrollStaff set Update tbl AgeRange = '31 til 38' der Alder >= 30 og Alder < 39 og PayrollDate = @PayrollDate Update tblStaff set AgeRange = '25 to 30' der Alder >= 25 og Alder < 30 og PayrollDate = @PayrollDate Update tblStaff set AgeRange '18 til 24' der Alder >= 18 og Alder < 25 og PayrollDate = @PayrollDate Update tblStaff set AgeRange = '<18' der Alder < 18 og PayrollDate = @PayrollDate END
Den lagrede prosedyren vil lagres under "Programmerbarhet" i databasen.
Excel VBA
Alt som gjenstår er å kalle opp den lagrede prosedyren fra Excel, og gi Lønnsdato parameter "2017/05/25". Du vil merke at jeg ganske enkelt har skrevet inn data Lønnsdato som en streng i stedet for å kjempe med varierende datoformater. Det er enkelt nok å konvertere en streng til en dato ved å bruke Konverter funksjon hvis Lønnsdato skal brukes til aritmetiske formål.
Opprett en ny arbeidsbok. Åpne VBA-kodevinduet og sett inn en modul.
Fra kodevinduets Verktøy-meny, referer til det aktuelle Active X 2.nn library for å lette bruken av dataobjekter.
Lim inn følgende kode i kodevinduet. Når denne er aktivert, kobles den til SQL Server, i henhold til ConnectDatabase-underprosedyren
'Alle "offentlige" i tilfelle koden er spredt over flere moduler. Public connDB As New ADODB.Connection Public rs As New ADODB.Recordset Public strSQL As String Public strConnectionstring As String Public strServer As String Public strDBase As String Public strUser As String Public strPwd As String Public PayrollDate As String Sub WriteStoredProcedure() PayrollDate = "2017Date /05/25" Call ConnectDatabase on Error GoTo errSP strSQL = "EXEC spAgeRange '" & PayrollDate & "'" connDB.Execute (strSQL) Exit Sub errSP: MsgBox Err.Description End Sub Sub ConnectDatabase() If connDB.State = 1DB.State = XNUMX Deretter connDB.Close On Error GoTo ErrConnect strServer = "SERVERNAME" 'Navnet eller IP-adressen til SQL Server
strDBase = "TestDB" strUser = "" 'la dette stå tomt for Windows-autentisering strPwd = "" Hvis strPwd > "" Så strConnectionstring = "DRIVER={SQL Server};Server=" & strServer & ";Database=" & strDBase & ";Uid=" & strUser & ";Pwd=" & strPwd & ";Tidsavbrudd for tilkobling=30;" Else strConnectionstring = "DRIVER={SQL Server};SERVER=" & strServer & ";Trusted_Connection=yes;DATABASE=" & strDBase 'Windows-autentiseringsslutt If connDB.ConnectionTimeout = 30 connDB.Open strConnectionstring Exit Sub ErrConnect: MsgBox Err.Description End Sub
Legg til en knapp i Ark1 og tilordne den til underprosedyre "WriteLagret prosedyre"
Resultatene
Trykk på knappen, og undersøk deretter tblStaff, som bør oppdateres med aldre og aldersgrupper. Behandlingen har foregått på serversiden.
Gjenoppretting av ødelagte arbeidsbøker
Skulle Excel krasjer kan det godt ta den eneste kopien av arbeidsboken ned med den. En god prosentandel av tiden Excel er ofte ikke i stand til å gjenopprette skadede arbeidsbøker; i et slikt tilfelle kan alt arbeidet som er gjort siden opprettelsen av arbeidsboken være irrevocably lost, med mindre du har et verktøy for å reparere Excel xlsx- eller xlsm-filer.
Forfatterintroduksjon:
Felix Hooker er en datagjenopprettingsekspert innen DataNumen, Inc., som er verdensledende innen datagjenopprettingsteknologier, inkludert rar reparasjon og sql-programvareprodukter. For mer informasjon besøk www.datanumen. Med