파일을 열지않고 값 읽어오기

Step 1: 닫혀있는 파일의 값 읽어오기

강좌 제목이 아주 흥미롭군요(자기가 지어 놓고 흥미롭다니… 웬 나르시시즘?!@#). 오늘은 또 Exceller가 어떤 신통한(?) 것을 보여 드리려고 하나… 궁금하시지요? 하긴 어떤 분은 Exceller가 독심술을 배웠느냐고 질문하시는 분도 있었답니다.

"파일을 열지않은 상태에서 값을 읽어오는 방법이 없을까요?"라고 질문을 하시는 분이 가끔 계십니다. 이번 강좌에서 사용되는 파일은 지금 보시는 이 강좌 파일과 "지점별실적.xls" 파일입니다. 이 두 개의 파일을 반드시 같은 폴더에 압축해제를 하신 다음 아래 버튼을 눌러 보시기 바랍니다.

이 강좌에서 읽어올 지점별실적.xls 파일의 Sheet1은 아래와 같은 형태입니다.

Step 2: XLM 매크로란?

VBA로는 닫혀있는 파일의 값을 읽어오는 방법이 없습니다만 XLM 매크로를 사용하면 이것이 가능합니다(실제로는 "파일연결하기" 기능을 사용한 것입니다). XLM 매크로에 대해 간략하게 설명을 드리자면, 초기의 Excel(Excel 95이전까지)에서 매크로는 XLM이라는 확장자명을 갖는 별도의 파일에 코드를 따로 저장했었습니다. 이것을 XLM 매크로 또는 Excel4Macro라고도 불렀습니다.

XLM 매크로는 배우기가 엄청나게 어렵고 함수의 종류도 많았다고 합니다. (Exceller도 직접 사용해 보지는 않았고 오늘 강좌처럼 필요한 부분만 그때그때 참고를 하곤 합니다)

Excel 5.0 버전이 되면서부터 VBA 엔진을 장착하게 되었는데 VBA는 XLM보다 훨씬 배우기가 쉽고 막강한 성능을 자랑합니다. Excel에 VBA 엔진을 탑재할 것이냐 말것이냐를 두고 Microsoft社 내부에서도 의견이 분분했다고 전해집니다만 VBA를 Excel에 장착하면서부터 Excel은 그야말로 날개를 달게되었지요(그래서 본 강좌의 제목도 "Excel에 날개달기"랍니다 ^^).

하여튼 XLM 매크로는 앞으로 VBA가 버전업 됨에 따라 점점 자취를 감출 것으로 예상됩니다. 코드를 보도록 하지요.

Step 3: 값을 불러오는 프로시저 작성하기

Sub CallReadValue()
    Dim strPath As String
    Dim strFile As String
    Dim strSheet As String
    Dim strAddress As String
    Dim sht As Worksheet
    Dim r As Long
    Dim c As Integer
    '//언제나처럼 필요한 변수를 먼저 선언해 줍니다.

    Application.ScreenUpdating = True
    Set sht = Worksheets.Add
    ActiveWindow.DisplayGridlines = False
    strPath = ThisWorkbook.Path
    strFile = "지점별실적.xls"
    strSheet = "Sheet1"
    '//ScreenUpdating은 작업중 화면 갱신여부를 지정하는 것입니다.
    '//strPath 변수에는 현재 파일이 위치한 경로를, strFile은 불러올 파일 이름을, strSheet는
    '//불러올 파일의 대상 시트 이름을 각각 담아 둡니다.

    For r = 1 To 11
        For c = 1 To 5
            strAddress = Cells(r, c).Address
            If ReadValue(strPath, strFile, strSheet, strAddress) = 0 Then Exit For
            Cells(r, c) = ReadValue(strPath, strFile, strSheet, strAddress)
            '//순환문을 돌면서 불러올 자료가 있는 셀의 위치를 저장합니다. 여기서 ReadValue가
            '//바로 XLM 매크로를 사용한 사용자 정의 함수입니다.
            '//이 함수는 경로명, 파일이름, 시트이름, 셀주소 이렇게 4개의 요소를 넘겨 받습니다.
        Next c
    Next r

    Selection.AutoFormat Format:=xlRangeAutoFormatList1, Number:=True, Font:= _
        True, Alignment:=True, Border:=True, Pattern:=True, Width:=True
    Rows("1:2").Insert shift:=xlDown
    '//이 부분은 "자동 서식"을 설정하는 것입니다. 그러면 Exceller 당신은 이걸 다 외워서
    '//적었느냐? 절대 그렇지 않겠지요? 자동 서식 설정을 하는 과정을 매크로 기록기를 통해
    '//기록한 다음, 수정/복사해서 붙여넣기를 하면 됩니다.

    With ActiveCell
        ActiveSheet.Buttons.Add(.Left, .Top, .Width * 2, .Height).Select
        With Selection
            .Caption = "<<돌아가기"
            .OnAction = "GoBack"
        End With
    End With
    '//자료 불러오기가 끝난 다음에 원래 위치로 돌아가기 위한 과정으로, 버튼을 하나 만들고
    '//여기에 GoBack이라는 프로시저를 지정합니다.

    Range("a1").Select
    MsgBox "자료를 모두 읽어들였습니다", vbInformation, "작업 종료//Exceller"
End Sub

버튼에 연결된 GoBack 프로시저는 원래의 강좌 시트로 돌아가는 역할을 합니다.

Sub GoBack()
    Sheets("Preface").Select
End Sub

Step 4: XLM 매크로를 이용한 사용자 정의 함수

이제 XLM 매크로를 사용해서 닫힌 파일의 내용을 불러오는 사용자 정의 함수를 작성합니다. 이 부분이 오늘의 핵심입니다.

Function ReadValue(Path, File, sht, Rng) As Variant
'//이 함수는 경로명, 파일명, 시트명, 범위명(셀주소) 이렇게 4가지 요소를 받습니다.

    Dim Msg As String
    Dim strTemp As String

    If Trim(Right(Path, 1)) <> "\" Then Path = Path & "\"
    '//지정한 경로명 마지막 부분에 \ 표시가 없으면 \표시를 추가합니다.

    If Dir(Path & File) = "" Then
        ReadValue = "해당 파일이 없습니다"
        Exit Function
        '//지정한 경로명내에 파일이 없으면 "해당 파일이 없습니다"라는 문자열을 결과값으로
        '//돌려주고 함수를 종료합니다.

    End If
    Msg = "'" & Path & "[" & File & "]" & sht & "'!" & Range(Rng).Range("a1") _
         .Address(, , xlR1C1)
    ReadValue = ExecuteExcel4Macro(Msg)
    '//다른 파일이나 워크시트의 내용을 연결할 때, 파일의 경우에는 '를, 워크시트의 경우에는
    '//!표시가 붙습니다. 이것에 착안하여 Msg라는 문자열 변수에 경로명 & 파일명 & 셀주소 등의
    '//정보를 저장합니다.

    '//그런 다음에 Excel4Macro(XLM의 다른 이름!)를 실행시키고 결과값을 넘겨 받습니다.

End Function

마무리

오늘은 여기까지…

정리 — 닫힌 파일 값 읽기 핵심

자주 묻는 질문 (FAQ)

마치며

XLM 매크로를 활용하면 파일을 열지 않고도 다른 통합 문서의 값을 읽어올 수 있습니다.