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

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""

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

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

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

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

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