엑셀 파일을 열지 않고 데이터 가져오기 - Heeyoung-Ahn/Excel_VBA GitHub Wiki

참조: https://blog.naver.com/atmyhome/90151824949

Private Sub GetClosedData(rngTarget As Range, varFile As Variant, ByVal strStAddr As Variant)
    'rngTarget 붙여넣고자 하는 시작셀, varFile 불러올 통합문서들
    'strStAddr 불러오는 시트 및 영역
    Dim varFileName As String, strArg As String
    Dim strTarget As Variant
    
    varFileName = Dir(varFile)
    strTarget = Split(strStAddr, "!")
    
    If Left(strTarget(0), 1) = "'" Then
        strTarget(0) = Mid(strTarget(0), 2)
    Else
        strTarget(0) = strTarget(0) & "'"
    End If
    
    strArg = "'" & Left(varFile, Len(varFile) - Len(varFileName)) & "[" & varFileName & "]" & strTarget(0) & "!" & Range(strTarget(1))(1).Address(0, 0)
    
    With rngTarget.Resize(Range(strTarget(1)).Rows.Count, Range(strTarget(1)).Columns.Count)
        .Formula = "=IF(" & strArg & "="""",""""," & strArg & ")"
        .Value = .Value
    End With
End Sub



Sub ClosedFile_Read()
    Dim varGetFile As Variant
    Dim strStRng As String
    Dim lngFiles As Long, lngI As Long
    
    varGetFile = Application.GetOpenFilename("ExcelFiles *.xls*,*.xls*", Title:="취합할 엑셀파일들 선택", MultiSelect:=True)
    If TypeName(varGetFile) = "Boolean" Then Exit Sub
    strStRng = InputBox("불러들일 시트명!영역을 입력하세요.", Default:="Sheet1!A1:C10")
    If strStRng = "" Then Exit Sub
        lngFiles = UBound(varGetFile)
    For lngI = 1 To lngFiles
        GetClosedData ActiveSheet.UsedRange.SpecialCells(xlLastCell)(2).EntireRow(1), varGetFile(lngI), strStRng
    Next lngI
End Sub