Excel은 거의 모든 작업을 수행 할 수 있습니다. 모든 것을해야하는지 여부는 또 다른 문제입니다. 스프레드 시트는 데이터를 조작하는 데 매우 강력하지만 정규화 된 데이터를 저장하는 데는 그리 크지 않습니다. Excel을 다음과 같은 관계형 데이터베이스에 활용 SQL Server 응용 프로그램의 성능을 향상시킵니다.
우선 MS Access 또는 더 안정적이고 무료인 프로그램이 필요합니다. SQL Server 표현하다. 독자에게 Excel 개발자 리본이 표시되고 VBA 편집기 및 SQL (구조적 쿼리 언어)에 익숙하다고 가정합니다. 이 기사는 SQL Server 연결 문자열. MS-Access에 대해서는 Google을 참조하십시오.
Excel에는 정보를 가져 오는 자체 루틴이 있습니다. SQL Server 예를 들어 피벗 테이블에 추가하면 데이터 선택에 더 많은 유연성을 제공 할 것입니다.
연결 문자열
개인 데이터베이스를 사용할 것입니다. ConnectDatabase 하위 루틴에서 내 드라이버 정보를 직접 삽입하십시오. 그런 다음 콘디비 데이터베이스에 대한 통신 채널로 – 제 경우에는 저장 프로 시저에서 결과를 반환합니다. "Select * from…"과 같은 더 많은 표준 SQL 문을 사용할 수 있습니다.
사업 순서
먼저 콤보 상자 선택 항목을로드합니다. SQL Server 통합 문서가 열릴 때 자동 열기 매크로를 사용하여 데이터를 "ComboData" 시트에 저장합니다. 서버가 클라우드에 있든 로컬에 있든, 워크스테이션에서 데이터베이스에 액세스할 수 있는 한 Excel 시작에 눈에 띄는 지연은 발생하지 않습니다.
다음으로, 데이터베이스에서 필터링 된 데이터를 추출하여 F ~ K 열의 Excel에 놓습니다.
인터페이스
Mine에는 데이터베이스에서 정보를 필터링하는 드롭 다운 상자가 있습니다. 그만큼 직위별 콤보 상자는 검색을 트리거하여 오른쪽 테이블을 채 웁니다.
"Sheet1"의 이름을 "Main"으로 바꿉니다. 콤보 상자를 하나 이상 추가하십시오.
코드
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 하위 프로 시저를 할당합니다. 콤보 상자에서 항목이 선택되면 해당 키를 시트 "Main", 셀 D7에 씁니다. VBA 코드는이 키를 필터로 사용합니다 (위의 intRole 참조).
DLL 라이브러리에 대한 참조
코드 창에서 도구 > 참조를 사용하여 Microsoft ActiveX Data Objects 라이브러리를 참조하십시오. 이렇게 하면 Excel에서 코드에 선언된 ADODB 개체를 사용할 수 있습니다.
위의 ReadData 하위 루틴은 아래에 표시된 관계형 데이터 구조를 사용하며 이는 Excel만으로는 달성하기 어렵습니다.
추가 데이터 변경은 적절한 SQL Update 문과 함께 데이터베이스에 다시 쓰기를 트리거 할 수 있습니다. connDB.execute (strSQL).
마지막으로 코드가 보거나 변경되지 않도록 보호합니다. 도구> 속성> 보호.
Excel 문제 처리 :
때때로, 특히 복잡한 프로그램을 포함하는 경우 Excel이 충돌하고 제대로 복구하지 못할 수 있습니다. 의 경우 손상된 xlsx 파일의 경우, 효과적인 복구 도구를 준비해 두면 대부분의 문제를 해결할 수 있습니다.
저자 소개 :
Felix Hooker는 데이터 복구 전문가입니다. DataNumen, Inc.는 다음과 같은 데이터 복구 기술 분야의 세계적 리더입니다. 수리 rar 오류 및 SQL 복구 소프트웨어 제품. 자세한 내용은 WWW.datanumen.COM