사이트에서 일하다보면 엑셀로 무수히 많은 엑셀자료를 만들기는 하는데 이걸 참조해서 개발하는 개발자는 너무많은 자료에 치여사는경우가 많다 그래서 생각한것이 하나의 만들어놓은 엑셀에서 관련되어 있는 엑셀을 query를 실행하여 조회하는 메크로를 작성해보았다
전에 Database를 사용하기 위해서는 ODBC드라이버가 있어야된다고 했는데 ODBC드라이버를 확인해보자
1) ODBC설치 확인(기본으로 OFFICE를 설치하면 설치된는것을 알수 있다)
2) 참조에 Microsoft ActiveX DataObject 2.8 Library를 추가 : ADODB 객체를 사용하려구
3) 일단 엑셀을 sql로 조회하기 위한 엑셀을 하나 준비한다
4) 엑셀을 DataBase처럼 Connection을 실행해서 잘 연결되는지 확인해보자
특이하게 Provider를 사용하여 쓰고 뒤에 Properties가 더 붙는다
Public dbcon As ADODB.Connection
Public dbcon As ADODB.Connection
Public Sub ExcelCon() '=>실행시 문제가 없는지 확인
Dim strCon As String '연결String
Dim driver, dsrc
dsrc = "D:\엑셀DB테스트.xlsx" '파일명
'엑셀파일을 DataBase처럼 연결
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dsrc & ";Extended Properties=Excel 12.0;"
Set dbcon = New ADODB.Connection
dbcon.Open strCon '연결
End Sub
5) 연결이 잘되었으면 RecordSet으로 받아 엑셀에 출력을 해보자
Public Sub getExcelEtcSel()
Dim rs, sql, colCnt, wRow, i
ExcelCon
sql = "select * from [Sheet2$] " '->조건없이 전부 조회
Debug.Print sql
'Set rs = CreateObject("ADODB.RecordSet")
Set rs = New ADODB.Recordset
rs.Open sql, dbcon
wRow = ActiveSheet.Range("B4") '작성시작 row
colCnt = rs.Fields.Count
'컬럼에 head를 시작에 달아줌
For i = 0 To colCnt - 1
ActiveSheet.Cells(wRow, i + 1) = rs.Fields(i).Name
Next
setHeaderStyle wRow 'header 스타일 적용
wRow = wRow + 1
If rs.EOF = False Then
Do While rs.EOF = False
For i = 0 To colCnt - 1
ActiveSheet.Cells(wRow, i + 1) = rs(rs.Fields(i).Name) '레코드셋에 이름으로 매핑하여 표시
Next
wRow = wRow + 1
rs.MoveNext
Loop
End If
End Sub
6) 엑셀에 특정영역중에 query를 사용할경우와 컬럼명을 기술할때 일반 query와 상이함
sql = "select [컬럼1],[컬럼2] from [Sheet2$A2:G5] "
- 시트전체조회시 [시트명$]
- 시트에일부영역시 [Sheet2$A2:G5] -> sheet2에 A2:G5 영역중 조회
- 컬럼은 위처럼 중괄호를 해야되고 중복된 컬럼명이 있으면 임의로 넘버링을 붙임
단독적으로 쓸일은 많지는 않았다 그보다 현재 많은 정보들이 있다보니 각종 소스들에 대해서 목록으로 처리해야될일이 많아졌다 생각 되어진다.
프로젝트에서 말하자면 현황파악
제일먼저 특정경로에 소스를 가져왔을때 해당 소스하위까지 포함하여 폴더에 목록을 엑셀로 작성해보자
1) 엑셀시트 화면 구성
B1에는 검색하려는 기본경로 기입
B2에는 기존 목록을 삭제하고 다시 작성할건지 여부
B3에는 전체작성된 건수를 엑셀함수를 써서 기입=COUNT(A9:A1048576)
2) 소스작성
먼저 파일을 사용하기 위해 참조를 하자
직접 CreationObject를 사용하여도 되지만 ctrl+space로 자동완성기능을 사용하기 위하여
참조를 통하여 객체를 넣는 작업을 한다
다음은 엑셀 코딩을 하자
Option Explicit '명시적으로 항상 어떤변수를 사용했는지 확인하는게 신상에좋다
Dim wRow '파일을 엑셀시트에 저장하기 위해 선언==> 말하자면 전역변수선언
Public Sub getFileList() '==>실행하는 함수
Dim rc
wRow = 9 '기본은 9부터
If ActiveSheet.Range("B2") = "Y" Then '삭제여부를 보고 삭제작업
rc = MsgBox("삭제해도 될까요?", vbYesNo, "삭제경고")
If rc = vbYes Then
Rows(wRow & ":" & wRow).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Else
Exit Sub
End If
'타이틀을 작성한다
ActiveSheet.Cells(wRow, 1) = "NO"
ActiveSheet.Cells(wRow, 2) = "파일명"
ActiveSheet.Cells(wRow, 3) = "경로"
ActiveSheet.Cells(wRow, 4) = "타입"
ActiveSheet.Cells(wRow, 5) = "Size"
ActiveSheet.Cells(wRow, 6) = "비고"
'이건별도 나만에 함수로 생략 setHeaderStyle wRow 'header 스타일 적용
wRow = wRow + 1
Else
wRow = ActiveSheet.Range("B3") + 10 '삭제를 안하고 추가하는경우 헤더포함하여 다음써야될행
End If
'b1에 입력된 기본 경로하위 폴더 및 파일목록을 작성시작
FolderFile ActiveSheet.Range("B1")
End Sub
Function FolderFile(fn As String)
Dim fs As FileSystemObject
Set fs = New FileSystemObject
Dim foldeer As Folder
Dim fileinfo
Dim Filnavn() As String
Set foldeer = fs.GetFolder(fn)
'해당폴더에 파일이 있는경우
For Each fileinfo In foldeer.Files
ActiveSheet.Cells(wRow, 1) = wRow - 9 '기본자릿수
ActiveSheet.Cells(wRow, 2) = fileinfo.Name '파일명
ActiveSheet.Cells(wRow, 3) = Mid(fileinfo.path, 1, Len(fileinfo.path) - Len(fileinfo.Name)) 'path에 파일명까지 같이 들어오므로 파일명 길이만큼 잘라path만입력
ActiveSheet.Cells(wRow, 4) = fileinfo.Type
ActiveSheet.Cells(wRow, 5) = fileinfo.Size
wRow = wRow + 1 '작성후 다음위치 + 1
Debug.Print "F " & fileinfo
Next
For Each Folder In foldeer.SubFolders 'sub폴더가 있는경우
Debug.Print "D " & Folder
FolderFile (Folder) '재귀함수로 자신을 다시 부름
Next
End Function
파일처리 목록완성 예시
3) 하이퍼링크를 활용하여 클릭시 자동열리게 하자
요즘은 하도 참조하는 문서가 많아 헷갈리는경우가 많다 저같은경우 목록을 작성하고
뒤에 비고란에 확인한 내용 및 주요 참고내용등을 적어놓고 다시 열때는 클리하여 자동열기를 주로 사용하고 있다
엑셀만 그렇지는 않고 보통 개발하다 보면 라이브러리가 필요로 할때가 많습니다. 그리고 많은 라이브러리들을 추가해주고 설정해야됩니다. 이런과정이 VBA에도 있습니다.
데이터 연결방법 두가지
윈도우즈 각 드라이버를 설치하고,
첫번째는 윈도우드ODBC에서 연결을 하고 연결된 ODB를 통하여 데이터 연결하는 방법
두번째는 윈도우에 각 드라이버 직접접속 하여 데이터 연결하는 방법
두번째는 CreationObject를 통하여 각 DataBase 종류별로 Connection 스트링을 맞춰 직접 연결
첫번째는 OLBC에서 미리 접속연결하고 동일한 Connection 스트링으로 연결하면 됩니다.
일단 라이브러리 추가 -> 데이터 베이스 연결을 해보겠습니다.
▶ 라이브러리 추가
VBA에서 DataBase를 연결하기위해 또는 일반 파일을 엑세스 하기위해 라이브러리를 추가해야됩니다.
먼저 DataBase를 연결하기 위한 라이브러리를 추가합니다.
메뉴에서 도구->참조
라이브러리 추가 메뉴
Microsoft ActiveX Data Object Library를 선택 버전은 여러개 있는데 2.0이후부터인데 보통 2.7, 2.8 6.1 선택하시면 됩니다.
Microsoft ActiveX Data Object Library 추가
▶ DataBase 연결정보 확인
연결전 드라이버를 확인
검색->ODBC 입력후-> 64bit선택
내 PC에 저장되어 있는 드라이버
▶ DataBase 직접연결 방법
Option Explicit '명시적 선언
Public dbconn As ADODB.Connection '추가한 라이브러리 선언
Public Sub DBTest()
'DataBase 연결 사용하기 위한 변수 설정
Dim strCon As String '연결String ※요게 DataBase마다 다름
Dim rs As ADODB.Recordset 'Variant 형으로 선언해도 됨
Dim sql
'1-1) Mysql기준으로 Connection String 작성
strCon = "Driver={MySQL ODBC 8.0 Unicode Driver};SERVER=127.0.0.1;PORT=3306;UID=root;PWD=해당패스워드;DATABASE=mysql;"
'1-2) 커넥션 객체 생성 (객체 연결방법 두가지)
'Set dbconn = CreateObject("ADODB.Connection") '라이브러리를 추가하지 않고 직접생성방법
Set dbconn = New ADODB.Connection '라이브러리를 참조로 추가했기 때문에 사용가능
dbconn.Open strCon '1-3)연결
'2-1) DataBase 접속한후 작업
'Set rs = CreateObject("ADODB.RecordSet") 'RecordSet이라고 조회후 내용을 담아둘 객체 수기생성
Set rs = New ADODB.Recordset '참조된 라이브러리로 생성
'2-2) 쿼리 작성
sql = "select * from db "
'2-3) 쿼리 수행(여기서는 조회쿼리로 RecordSet에 담아서 결과출력)
rs.Open sql, dbconn 'Connecton 객체로 sql 수행
While rs.EOF = False
Debug.Print rs.Fields(1) '조회결과를 index로 출력
'Debug.Print rs("컬럼명") '컬럼명을 가지고 출력
rs.MoveNext '다음Record로 전환
Wend
rs.close 'RecordSet 객체 닫기 : 자원반납
dbconn.close 'Connection 객체 닫기: 자원반납
End Sub
2) Oracle 다운로드 및 설치 (odbc 패키지에 odbc_install.exe를 실행)
▶ 설치
* 아래의 홈페이지에서 아래의 파일을 다운로드한다.
다운로드 사이트 : http://www.oracle.com/technetwork/topics/winx64soft-089540.html
다운로드 파일명
instantclient-basic-windows.x64-11.2.0.3.0.zip
instantclient-odbc-windows.x64-11.2.0.3.0.zip
* 다운로드 받은 파일의 압축을 해제한다.
- 압축 해제하면 instantclient_11_2 폴더에 모든 파일이 존재한다.
* instantclient_11_2 를 C:\Oracle 폴더로 복사한다.
- C:\Oracle 이 없으면 생성한다.
* 관리자 권한으로 DOS 창을 실행한다.
- C:\Oracle\instantclient_11_2 폴더로 이동한 후, 아래와 같이 명령을 실행한다.
- ODBC 설치가 정상적으로 되었으면 아래와 같이 성공 메시지가 보여진다.
*설치 실행
C:\Oracle\instantclient_11_2>odbc_install.exe
Oracle ODBC Driver is installed successfully.
▶ 드라이버 확인
Oracle ODBC 드라이버 설치후 드라이버 확인
▶ 환경변수 등록:검색에서 ->고급 시스템 설정-> 환경변수 등록
ORACLE_HOME=C:\Oracle\instantclient_19_8
TNS_ADMIN=c:\Oracle\instantclient_19_8 ( tnsnames.ora 파일이 위치할 폴더 )
NLS_LANG=KOREAN_KOREA.KO16MSWIN949 ( 한글관련 설정 )
KOREAN_KOREA.AL32UTF8 (이것도 있는듯)
PATH=c:\Oracle\instantclient_19_8 ( 기존 패스에 추가 )
▶tnsnames.ora파일 생성 : ORACLE HOME 지정위치에 C:\Oracle\instantclient_19_8
# Generated by Oracle configuration tools.
# ORCL : 서비스 이름 또는 tns name
# DESCRIPTION : 접속하고자 하는 대상 데이터베이스 정보
# ADDRESS : 접속하고자 하는 데이터베이스 서버의 리스너를 호출하기 위한 주소 정보(프로토콜, 호스트, 리스너 포트)
# CONNECT_DATA : SERVICE_NAME 옵션을 이용하여 접속할 리스너 프로세스가 사용하는 서비스 이름을 지정하거나
# 또는 SID옵션을 이용하여 데이터베이스의 SID명을 지정
# SERVER = DEDICATED : 오라클 서버와 연결시 생성된 프로세스를 전용연결
Dedicated Server(전용 서버) 방식 혹은 Shared Server(공유 서버) 방식으로 운영함
# SERVICE_NAME = MYNAME : 오라클 서버의 리스너(listener.ora)에 기술된 서비스명
/ 서버측에 사용자 (DB관리자)가 임의로 작성하여 넣은 이름
ORCL=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=아이피)(PORT=포트))
)
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=xe)
)
)
엑셀 매크로는 VB의 문법을 사용하고 있고 추가로 엑셀의 함수를 지원하여 VBA라고 불리워 집니다.
그러므로 VB문법에 대해 소개 하고자 합니다.
▶ 변수선언 방법 두가지 : 명시적/묵시적
Java 나 C를 해보신분은 변수를 선언하지 않으면 컴파일이 안됩니다 이것을 명시적이라 합니다..
VB에서는 기본적으로 변수 선언없이(묵시적) 으로 사용가능하지만, 코딩이 길어질때 오히려 명시적으로 하는것이 좋습니다.
명시적 변수처리 하겠다 선언
그래서 변수 형이 지정되어 있지 않은 Variant 형이 VB에서만 존재 합니다.
Variant는 변수가 문자가 처음 저장되면 String형, 숫자가 저장되면 Integer로 알아서 내부적으로 적용됩니다.
▶변수선언 및 타입 확인
dim 변수명 as 변수타입 : 변수 선언방법
※ Integer, String,Variant : 변수타입 종류
※ as 부터 뒤에가 없으면 Variant(형이 없는)으로 선언되면 변수값이 저장될때 형이 지정됩니다.
▶ 조건문
if 조건문 then
elseif 조건문 ==> 생략가능
else ==>생략가능
end if
ex)
If a > 10 Then
MsgBox("10이상")
ElseIf b > 5 Then
MsgBox("5이상")
Else
MsgBox("기타")
End If
자바와 기본적으로 다른 문법을 사용합니다.
Java
VB
내용
!=
<>
조건문 다름
&&
and
조건문 그리고
||
or
조건문 또는
==
=
비교문 같음
▶반복문 (For, Loop)
java
VB
내용
for(int ii=0;ii<10;ii++){ break//for문을 빠져나옴 }
for i=0 to 9 step 1 exit for'for문을 빠져나옴 next i
step 는 1씩 증가인데 없으면 자동으로 1씩 증가
next 뒤에 i는 증가할변수를 등록 없으면 알아서 함
Do While i<9
exit loop'loop문을 빠져나옴 I = I + 1 Loop
i가 9이하일때까지 처리 exit loop는 강제로 루프 빠져 나옴
While i < 9 i = i + 1 Wend
위와 동일한 문법임
▶ 활용예시
자 그럼 활용예로 엑셀시트에 1행부터 10행까지 x 10을 해서 쓰는거 , 쓴것을 읽는것을 한번 해봅시다
1) 쓰기 예제
Public Sub 사용자처리()
For i = 1 To 10
Sheets("sheet1").Cells(i, 1) = i * 10
Next
End Sub
2)읽기 예제
For i = 1 To 10
'vba 편집에서 보기->직접실행을 선택하면 하단에 debug.print한 값이 나옴
'->자바에서는 붙여쓰기를 + 로 하는데 vb는& 로 함 +인경우는 연산자로 인식
Debug.Print "시트내용:" & Sheets("sheet1").Cells(i, 1)
Next
Public Sub 사용자처리()
Sheet("sheet1").Cells(3, 3) = "하하하하"
End Sub
※ 참고
Sheet("시트명") : 특정 시트
Workbooks("파일명").Sheet("시트명") : 해당파일명에 해당 시트명
ActiveWorkbook : 현재 활성화 되어 있는 파일 [("파일명")은 없음]
ActiveSheet : 현재 활성화 되어 있는 시트 [("시트명")은 없음]
▶ 엑셀 시트의 위치를 선택하는 두가지 방법
Range("C2") 라 직접 쓰는 방식이 있지만 우리가 반복작업을 하기위해 A->B->C를 FOR문이나 lOOP에서 돌리기 어렵기 때문에 Cells(세로줄 라인위치, 가로줄 라인 위치)를 사용합니다.
하단은 ' 로 주석을 하였고 동일한것으로 Cells명령어를 사용하여 작성해보았습니다.
End Sub
'주석 엑셀 시트에 가져오는 방법
MsgBox Cells(3, 3) 'Cells(행[y축] 위치, 열[x축] 위치) 표시
MsgBox Range("C3") 'Range는 열은 영문으로 행은 위치로 표시
End Sub
※ 주석은 ' 작은따옴표 하나이며 작은따옴표 뒤에는 전부 주석됨
※ 위 두가지 방법은 동일한 결과를 갖게 됨
▶ 엑셀 시트쓰기, 엑셀시트에서 값을 사용하기
등호(=)을 사용하고 오른쪽에 시트에 저장할값을 쓰면됩니다.
Public Sub 사용자처리()
Cells(3, 3) = "하하하하" '=>엑셀시트에 하하하하를 저장
Range ("C3")= "하하하하"
MsgBox Range ("C3") ' 엑셀시트값을 사용하기
End Sub
처리결과)
C3 또는 3,3에 값을 저장
Sheets는 일반 Visual Basic(일명 VB)에는 없는 함수로 엑셀에 내장되어 있는 VB로 사람들은 Visual Basic Application(일명 VBA) 라고 합니다.
※ 요약정보
파일함수.시트함수.셀함수
파일함수 : ActiveWorkbook,Workbooks("파일명")
시트함수 : ActiveSheet. WorkSheet("시트명")
셀함수 : ActiveCell, Cell(행 위치, 열 위치), Range( 열 영문, 행위치)
Range예) Range("D1") 하고 Cells(1,4) 는 동일함
※ Cells를 많이 쓰는이유는 For, Loop문으로 코딩하기 위해 많이 사용함
