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

 

Option Explicit

Function ReadValue(Path, File, sht, Rng) As Variant
    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)
End Function

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"
   
    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)
        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
   
    With ActiveCell
        ActiveSheet.Buttons.Add(.Left, .Top, .Width * 2, .Height).Select
        With Selection
            .Caption = "<<돌아가기"
            .OnAction = "GoBack"
        End With
    End With
    Range("a1").Select
    MsgBox "자료를 모두 읽어들였습니다", vbInformation, "작업 종료//Exceller"
End Sub

Sub GoBack()
    Application.Goto Sheets("Preface").Range("a1"), True
End Sub