Excel VBA - 외부 데이터 연동

Excel

 
 

엑셀 함수 시리즈 #42: VBA 외부 데이터 연동 - 데이터 분석의 확장

VBA(Visual Basic for Applications)를 사용하면 엑셀에서 외부 데이터 소스(데이터베이스, 텍스트 파일, 웹 등)와 연동하여 데이터를 가져오고 처리할 수 있습니다. 외부 데이터 연동은 데이터 분석의 범위를 확장하고 자동화된 데이터 처리 시스템을 구축하는 데 필수적인 기능입니다. 이번 글에서는 VBA 외부 데이터 연동의 기본 개념과 주요 연동 방법, 실제 예시를 알아보겠습니다.

1. 외부 데이터 연동의 필요성

• 데이터 분석 범위 확장: 엑셀에 없는 데이터를 가져와 분석할 수 있습니다.
• 데이터 자동화 처리: 외부 데이터 소스의 변경 사항을 자동으로 엑셀에 반영합니다.
• 데이터 통합: 여러 데이터 소스의 데이터를 통합하여 분석합니다.

2. 주요 연동 방법

1. ADO (ActiveX Data Objects): 데이터베이스(Access, SQL Server 등)와 연동하여 데이터를 가져오고 처리합니다.
2. FileSystemObject: 텍스트 파일, CSV 파일 등 파일 시스템의 데이터를 읽고 씁니다.
3. XMLHTTP: 웹 페이지의 데이터를 가져오거나 웹 API를 호출합니다.

3. ADO를 이용한 데이터베이스 연동 예시

VBA

 

Sub DatabaseConnectionExample()
    Dim conn As Object
    Dim rs As Object
    Dim sql As String

    Set conn = CreateObject("ADODB.Connection")
    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Database\Data.accdb;" ' Access 데이터베이스 연결
    Set rs = CreateObject("ADODB.Recordset")
    sql = "SELECT * FROM Table1"
    rs.Open sql, conn

    Sheets("Sheet1").Range("A1").CopyFromRecordset rs ' 워크시트에 데이터 복사

    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub

4. FileSystemObject를 이용한 텍스트 파일 연동 예시

VBA

 

Sub TextFileReadExample()
    Dim fso As Object
    Dim ts As Object
    Dim filePath As String
    Dim fileContent As String

    filePath = "C:\Data\Text.txt"
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.OpenTextFile(filePath, 1) ' 1: 읽기 모드

    fileContent = ts.ReadAll
    Sheets("Sheet1").Range("A1").Value = fileContent

    ts.Close
    Set ts = Nothing
    Set fso = Nothing
End Sub

5. XMLHTTP를 이용한 웹 데이터 연동 예시

VBA

 

Sub WebDataExample()
    Dim xmlhttp As Object
    Dim url As String

    url = "https://api.example.com/data"
    Set xmlhttp = CreateObject("MSXML2.XMLHTTP")
    xmlhttp.Open "GET", url, False
    xmlhttp.send

    Sheets("Sheet1").Range("A1").Value = xmlhttp.responseText

    Set xmlhttp = Nothing
End Sub

6. 마무리

VBA 외부 데이터 연동은 엑셀 데이터 분석의 범위를 확장하고 자동화된 데이터 처리 시스템을 구축하는 데 필수적인 기능입니다. 다양한 연동 방법을 익히고 실제 작업에 적용하여 데이터 분석 능력을 향상시키세요.