Microsoft Excel에서 VBA를 사용하여 폴더에있는 각 통합 문서에서 열 또는 열을 복사
-
매크로는 C : \ Data 폴더에있는 모든 파일의 첫 번째 워크 시트의 일부를 통합 문서의 첫 번째 워크 시트로 복사합니다.
-
첫 번째 매크로는 일반 복사를 수행하고 두 번째 매크로는 값을 복사합니다.
Excel에는 256 개의 열만 있습니다
Sub CopyColumn ()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim cnum As Integer
Dim i As Long
Dim a As Integer
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "C:\Data"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
Set basebook = ThisWorkbook
cnum = 1
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles(i))
Set sourceRange = mybook.Worksheets(1).Columns("A:B")
a = sourceRange.Columns.Count
Set destrange = basebook.Worksheets(1).Cells(1, cnum)
sourceRange.Copy destrange
mybook.Close
cnum = i * a + 1
Next i
End If
End With
Application.ScreenUpdating = True
End Sub
Sub CopyColumnValues()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim cnum As Integer
Dim i As Long
Dim a As Integer
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "C:\Data"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
Set basebook = ThisWorkbook
cnum = 1
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles(i))
Set sourceRange = mybook.Worksheets(1).Columns("A:B")
a = sourceRange.Columns.Count
With sourceRange
Set destrange = basebook.Worksheets(1).Columns(cnum). _
Resize(, .Columns.Count)
End With
destrange.Value = sourceRange.Value
mybook.Close
cnum = i * a + 1
Next i
End If
End With
Application.ScreenUpdating = True
End Sub