Excel może zrobić praktycznie wszystko; czy należy zrobić wszystko, to inna sprawa. Chociaż arkusz kalkulacyjny jest bardzo wydajny w manipulowaniu danymi, nie jest zbyt dobry w przechowywaniu znormalizowanych danych. Wykorzystanie programu Excel do relacyjnej bazy danych, takiej jak SQL Server zwiększa możliwości aplikacji.
Na początek będziesz potrzebować MS Access lub bardziej stabilnego i darmowego – SQL Server Wyrazić. Zakłada się, że czytelnik ma wyświetloną wstążkę programu Excel Developer i zna Edytor VBA oraz Structured Query Language (SQL). Ten artykuł używa SQL Server ciągi połączeń. W przypadku MS-Access, skontaktuj się z Google.
Chociaż program Excel ma własne wbudowane procedury pobierania informacji z SQL Server do (powiedzmy) tabeli przestawnej, nasz przykład zapewni większą elastyczność w wyborze danych.
Ciąg połączenia
Będę korzystać z prywatnej bazy danych; wstaw własne informacje o sterowniku zamiast moich w procedurze podrzędnej ConnectDatabase. Następnie używamy connDB jako kanał komunikacyjny do naszej bazy danych - w moim przypadku do zwracania wyników z procedury składowanej. Możesz użyć bardziej standardowych instrukcji SQL, takich jak „Wybierz * z…”
Porządek biznesowy
Najpierw załadujemy opcje z listy rozwijanej SQL Server po otwarciu skoroszytu, używając makra Auto_open i umieszczając go w arkuszu „ComboData”. Niezależnie od tego, czy serwer znajduje się w chmurze, czy lokalnie, nie będzie zauważalnego opóźnienia w uruchomieniu programu Excel – o ile baza danych jest dostępna ze stacji roboczej.
Następnie wyodrębnimy przefiltrowane dane z bazy danych i upuścimy je do Excela, kolumny od F do K.
Interfejs
Mój ma rozwijane pola do filtrowania informacji z bazy danych. Plik Rola pole kombi uruchamia wyszukiwanie w celu wypełnienia tabeli po prawej stronie.
Zmień nazwę „Arkusz1” na „Główny”. Dodaj co najmniej jeden combobox.
Kod
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
Sformatuj formant pola kombi, aby odczytywał arkusze „ComboData”. Następnie kliknij prawym przyciskiem myszy pole kombi, aby przypisać do niego procedurę podrzędną ReadData. Gdy element jest zaznaczony w polu combo, zapisz jego klucz w arkuszu „Main”, komórce D7. Kod VBA użyje tego klucza jako filtru (patrz intRole powyżej).
Odniesienia do biblioteki dll
Użyj opcji Narzędzia>Odwołania w oknie kodu, aby odwołać się do biblioteki obiektów danych Microsoft Active X. Umożliwi to programowi Excel korzystanie z obiektów ADODB zadeklarowanych w kodzie.
Procedura podrzędna ReadData powyżej wykorzystuje relacyjną strukturę danych, pokazaną poniżej, co jest trudne do osiągnięcia w samym programie Excel.
Dalsze zmiany danych mogą wywołać zapis zwrotny do bazy danych, z odpowiednią instrukcją SQL Update, po której następuje connDB.execute (strSQL).
Na koniec chroń swój kod przed przeglądaniem lub zmianą: Narzędzia> Właściwości> Ochrona.
Rozwiązywanie problemów z programem Excel:
Od czasu do czasu, zwłaszcza gdy zawiera złożone programy, program Excel może ulec awarii i nie może ponownie prawidłowo pokryć danych. W przypadku uszkodzony xlsx Jeśli masz pod ręką skuteczne narzędzie do odzyskiwania plików, rozwiążesz większość problemów.
Wprowadzenie autora:
Felix Hooker jest ekspertem w dziedzinie odzyskiwania danych w DataNumen, Inc., która jest światowym liderem w technologiach odzyskiwania danych, w tym naprawa rar błąd i oprogramowanie do odzyskiwania sql. po więcej informacji odwiedź www.datanumen.com