VBA 폴더내 모든 엑셀파일 의 특정 셀 가져오기

 

폴더내 모든 엑셀파일 의 특정 셀 가져오기

​-- 폴더내 모든 엑셀파일의 D4에 있는 셀값을 가져오는 매크로입니다.
​

Sub CopyValuesFromMultipleFiles()

    Dim fd As FileDialog

    Dim fileName As String

​

    Set fd = Application.FileDialog(msoFileDialogFolderPicker)

    If fd.Show = -1 Then

        fileName = Dir(fd.SelectedItems(1) & "\*.xls*")

        Do While fileName <> ""

            Workbooks.Open (fd.SelectedItems(1) & "\" & fileName)

            If Not Workbooks(fileName).Sheets("A").Range("D4") Is Nothing Then

                /*Workbooks(fileName).Sheets("A").Range("D4").Copy Destination:=ThisWorkbook.Sheets("A1") -- 오류 발생*/
                Workbooks(fileName).Sheets("A").Range("D4").Copy Destination:=ThisWorkbook.Sheets("A1").Range("원하는 셀주소")

            Else

                MsgBox fileName & ": D4 cell not found."

            End If

            Workbooks(fileName).Close

            fileName = Dir()

        Loop

    End If

End Sub