[엑셀 VBA] 자동화 - 엑셀 데이터베이스의 기본 작동 원리 및 구현 방법

작성자
dra****
작성일
2021-06-28 22:19
조회
195

질문의 내용 속에 DATABASE에 대한 목마름이 많이 계신듯 하여 기본적인 내용을 포스팅하려 합니다.

이 부분이 DB를 자동화 할 수 있는 매우 중요한(?) 초석입니다.

 

1. ADODB.Connection 개체

일종의 PipeLine의 역할을 하는 개체로서 Data Transaction의 핵심을 담당 하는 부분입니다.

이녀석한테 어떠한 데이터베이스를 쓰는지, 데이터 파일이 어디에 있는지 기본 정보를 넘겨 주어야 제 역할을 할 수 있습니다.

그 기본 정보를 넘져 주기 위해서 문자열에 그 정보를 담아 두는데, 그 정보를 Connection String이라 합니다.

Connection String에 관련된 정보를 담아 둔 아래 참조 사이트를 보시면 이해가 쉽습니다.

https://www.connectionstrings.com/

xlsx : "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";"
Treating data as text : 
	   "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";"
xlsb : "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myBinaryExcel2007file.xlsb;Extended Properties="Excel 12.0;HDR=YES";"
xlsm : "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsm;Extended Properties="Excel 12.0 Macro;HDR=YES""

파일 형식에 따른 커넥션 스트링들입니다.

"Microsoft.ACE.OLEDB.12.0;" 이 부분이 엑셀을 DATABASE로 쓰겠다고 알려주는 영역입니다.

"c:\myFolder\myExcel2007file.xlsx;" 이 부분이 소스 파일의 경로를 알려 주는 것입니다.

이 정보를 이용하여 ADODB.Connection 개체를 연결하고 Open 메서드를 실행하면 서로 연결이 됩니다.

이 부분의 좋은 점은 파일을 열지 않고 데이터를 주고 받을수 있다는 것입니다. 다음은 개체 연결하여 구성하는 소스 코드 입니다.

Public ConXl As ADODB.Conneciton '전역변수로 커넥션 개체 선언
 
Function ConnectionOpen(srcPath As String) As Boolean
	On Error Goto ErrOpenCon
 
	Set ConXl = New ADODB.Conneciton
 
	With ConXl
		.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _"
						    "Data Source=" & srcPath & ";" & _ 
						    "Extended Properties="Excel 12.0 Xml;HDR=YES";"
		.Open '개체를 연결하고 파일을 연다 (눈에 보이지는 않음).
	End With
 
	ConnectionOpen= True
	Exit Function
ErrOpenCon:
	ConnectionOpen= False
End Function

 

2 기본 INSERT, UPDATEDELETEADODB.Recordset 개체 없이도 바로 가능합니다.

Sub Update()
	Dim SQL As String
	Dim recAff As Long '업데이트가 몇개가 되었는지 받아주는 변수
	Dim reQ
 
	SQL = "UPDATE 구문..."
	Call ConnectionOpen(scrPath)
		reQ = ConXl.Execute(SQL, recAff) '이부분이 Connection개체를 이용하여 업데이트 하는 부분입니다.
	Call ConnectionClose
End Sub

 

3 기본 SELECTADODB.Recordset 개체를 통해 워크시트에 뿌려 주어야 합니다.

Sub ImportData()
	Dim rs As new ADODB.RecordSet	'레코드세트 개체 생성
	Dim fIndex As Long              '필드열의 갯수를 받아줄 변수
	Dim SQL As String
 
	SQL= "SELECT * FROM [시트이름$]"
 
	Call ConnectionOpen(srcPath)
		rs.Open SQL, ConXl, adOpenDynamic, adLockPessimistic, adCmdTable   '이 부분이 데이터를 가져오라 하는 부분입니다.
 
		Sheet1.Cells.Clear
 
		'필드명 삽입하기
		For fIndex = 0 To rs.Fields.Count-1
			Sheet1.Cells(1, fIndex+1).Value = rs.Fields
		Next fIndex
 
		Sheet1.Cells(2,1).CopyFromRecordset rs  '여기가 시트에 붙여 넣는 부분입니다. Range.CopyFromRecordset
 
		rs.Close	
	Call ConnectionClose
End Sub

 

4. 당연히 사용이 끝났으면 Connection개체를 닫아 주어야 열린 DB도 같이 닫히게 됩니다. 안 그러면 나중에 에러가 생길 수도 있습니다.

Function ConnectionClose() As Boolean
	On Error Goto ErrCloseCon
 
	If CBool(ConXl.State and adStateOpen) = True Then CoonXl.Close
 
	ConnectionClose= True
	Exit Function
ErrCloseCon:
	ConnectionClose= False
End Function

 

5. INSERT 구분은 Recordset 개체의 AddNew 메서들 이용하는 방법도 있고, SQL로 하는 방법도 있습니다. 다음은 AddNew 메서드를 사용하는 예제 코드입니다.  물론  "Connection.Execute SQL" 로 해도 됩니다. Recordset 개체는 확실히 공부해두는 것이 좋습니다. 배열과 Dictionary를 통해 수 많은 데이터를 한번에 INSERT하는 기능이 있습니다.

Sub AddNew()
	Dim rs As new ADODB.RecordSet
 
	Call ConnectionOpen(srcPath)
		rs.Open "[시트이름$]", conXl, adOpenDynamic, adLockPessimistic, adCmdTable
 
		rs.AddNew
			rs!필드명1 = "값1"	'= rs.Fields("필드명1") = "값1"   이렇게 써도 됩니다.
			rs!필드명2 = "값2"
			rs!필드명3 = "값3"
		rs.Update	
 
	Call ConnectionClose
End Sub

 

뭐 그렇게 대단한 코드는 아니지만 제가 처음 공부할 때는 이런 코드들에 많이 목말라 했었습니다. 사실 Connection개체가 무엇인지도 모르고 따라하다 보니 이해가 잘 안되서, 안되겠다 싶더군요. 그래서 공부하고 정리하다 보니 여기까지 왔네요. 외국 유튜버 강좌에는 널려 있는데 한국 유튜버들은 잘 안합니다. 아마 클릭 횟수가 떨어져서 그럴 듯 ...

사족1) Connection, Command, Properties, Recordset, Fields, Field, ListObjects, ListObject, QueryTable, WorksheetConnection 등 상당히 많은 개체들이 연결되어 있어 전체적인 맥락으로 보아야 좀 이해가 됩니다.

사족2) Connection개체의 ConnectionString으로 "OLEDB;Provider=Microsoft.Mashup.oledDb.1;" 이 부분이 PowerQuery와 연결되는 부분입니다. 위의 참조 싸이트에도 안 나옵니다. 매크로 기록을 해보면 등장합니다. 아직도 공부중이라 이 부분은 나중에.

이 부분들을 Class에 때려 담고 한방에 처리하는 것이 편합니다.

회원등급 : 열매4단계
포인트 : 1923 EP
전체 8

  • 2021-07-06 13:40

    sybase에서 배워온것이죠


  • 2021-07-05 00:52

    ADODB!!! 참 유용한 라이브러리이지만.. 막상 현업에 적용하려면 제한이 많이 생기죠 ㅜ

    그래도 엑셀 db를 지속적으로 받아올 수 있는 써드파티앱 있다면 정말 무궁무진하게 활용할 수 있는 것 같습니다 ㅎㅎ

    사실 한 페이지에 다 담기 어려운 내용이지만.. 중요 팁이 여러개 있는 듯 합니다 ㅎㅎ 정말 좋은 내용!! 추천박고갑니다


    • 2021-07-05 09:09

      요사이 파워쿼리 자동화 관련 공부를 하다 보니, 엑셀을 DB처럼 쓰면 매우 편하다는 생각을 갖게 되었습니다.

      서버에서 조회해서 원하는 결과를 도출해 내는데는 SQL 만큼 빠른 것도 없으니.

      마소가 파워쿼리에 공을 들인 이유를 어렴풋이나마 이해하고 있는 중입니다.


  • 2021-07-02 12:31

    헐... 대박. 빨리 시간내서 열심히 공부해야겠네요. 감사합니다.


  • 2021-07-26 02:51

    저한테는 좀 이해하기 어려운 내용이네요. ㅎㅎ;;


  • 2021-07-15 14:18

    감사합니다!!


  • 2021-07-28 15:21

    감사합니다!!

     


  • 2021-07-28 16:13

    감사합니다


전체 27
번호 제목 작성자 작성일 추천 조회
25
[엑셀 VBA] 개체 Reference - WORKBOOKS, WORKBOOK Object 첨부파일 (3)
dra**** | 2021.07.25 | 추천 2 | 조회 56
dra**** 2021.07.25 2 56
24
[엑셀 VBA] 개체 Reference - WORKSHEETS, WORKSHEET Object 첨부파일 (4)
dra**** | 2021.07.11 | 추천 0 | 조회 104
dra**** 2021.07.11 0 104
23
[엑셀 VBA] 개체 Reference - RANGE Object 첨부파일 (2)
dra**** | 2021.07.06 | 추천 2 | 조회 102
dra**** 2021.07.06 2 102
22
[엑셀 VBA] 자동화 - 엑셀 데이터베이스의 기본 작동 원리 및 구현 방법 (8)
dra**** | 2021.06.28 | 추천 3 | 조회 195
dra**** 2021.06.28 3 195
21
지배하는 자 Vs. 지배당하는 자 (11)
dra**** | 2021.06.27 | 추천 10 | 조회 198
dra**** 2021.06.27 10 198
20
[엑셀 VBA] 모든(?) 색상 값과 VBA로 적용하는 방법 첨부파일 (3)
dra**** | 2021.06.23 | 추천 3 | 조회 119
dra**** 2021.06.23 3 119
19
[엑셀 VBA] Naming Convention - 변수나 개체 이름 붙이는 관행 (4)
dra**** | 2021.06.14 | 추천 3 | 조회 95
dra**** 2021.06.14 3 95
18
[엑셀 VBA] 자동화 - FORMS를 이용한 직원 근무시간과 급여 계산하기
dra**** | 2021.06.13 | 추천 3 | 조회 176
dra**** 2021.06.13 3 176
17
[엑셀 VBA] 자동화 - 데이터 관리의 기본과 고급필터의 유용성
dra**** | 2021.06.10 | 추천 3 | 조회 128
dra**** 2021.06.10 3 128
16
[APP] 심플한 날짜 계산기 첨부파일 (4)
dra**** | 2021.06.09 | 추천 1 | 조회 135
dra**** 2021.06.09 1 135
15
[액셀 VBA] 자동화 - 직책별로 뽑은 직원들을 폼에 연동하여 수정 조회 하기
dra**** | 2021.06.03 | 추천 1 | 조회 145
dra**** 2021.06.03 1 145
14
[엑셀 VBA] 자동화 - ONEDRIVE를 서버로 활용하기 - 화상영어 회사 자동화 사례 (3)
dra**** | 2021.05.30 | 추천 2 | 조회 156
dra**** 2021.05.30 2 156
13
[엑셀 함수] WEEKNUM()으로 구한 몇 주차로 다시 날짜 구하기
dra**** | 2021.05.29 | 추천 0 | 조회 96
dra**** 2021.05.29 0 96
12
[엑셀 VBA] 자동화 - 1. 데이터 입력, 2. 양식에 자동으로 대입 3. 이메일 자동 발송 (2)
dra**** | 2021.05.28 | 추천 1 | 조회 180
dra**** 2021.05.28 1 180
11
[엑셀 함수] 엑셀에 쓰이는 모든 함수들입니다. 첨부파일 (10)
dra**** | 2021.05.26 | 추천 22 | 조회 255
dra**** 2021.05.26 22 255
10
[엑셀 VBA] 자동화의 딜레마 (11)
dra**** | 2021.05.25 | 추천 9 | 조회 222
dra**** 2021.05.25 9 222
9
[엑셀 VBA] 데이터베이스 다루기 (3)
dra**** | 2021.05.24 | 추천 3 | 조회 214
dra**** 2021.05.24 3 214
8
[엑셀 VBA]-[팁] 콤보박스에 시트에 있는 값을 자동으로 집어 넣기 (1)
dra**** | 2021.05.22 | 추천 0 | 조회 123
dra**** 2021.05.22 0 123
7
[엑셀 VBA]-[팁] TextBox, ComboBox 등에 숫자만 입력 받기 (1)
dra**** | 2021.05.21 | 추천 1 | 조회 169
dra**** 2021.05.21 1 169
6
디지털 시계 첨부파일 (10)
눈사람 | 2021.05.17 | 추천 10 | 조회 241
눈사람 2021.05.17 10 241